C#中如何拆分多条件查询并定位失效查询条件

Hey there! Let's walk through how to solve this problem—identifying which condition in your WHERE clause is returning no results, especially when dealing with mixed AND/OR logic and subqueries like your example:

select * from ABD where a=0 and b= 42 and c=(Select c from azs) or f=89 or x=10

First, remember that SQL evaluates AND before OR, so your query is actually grouped like this:
(a=0 AND b=42 AND c=(Select c from azs)) OR (f=89) OR (x=10)
Each of these parentheses groups is a "logical branch"—if any branch returns results, the whole query does. So first, we need to isolate these branches, then dig into the ones that fail.

First, we need to pull the WHERE clause out of the input SQL, then split it into logical branches (split on OR, but ignore OR inside parentheses like subqueries).

Extracting the WHERE Clause #

Here's a simple C# method to grab the WHERE content (handles case insensitivity and trailing clauses like ORDER BY):

public static string ExtractWhereClause(string sql)
{
    var lowerSql = sql.ToLower();
    var whereIndex = lowerSql.IndexOf(" where ");
    if (whereIndex == -1) return string.Empty;
    
    var start = whereIndex + 6; // Length of " where "
    var orderByIndex = lowerSql.IndexOf(" order by ", start);
    var end = orderByIndex == -1 ? sql.Length : orderByIndex;
    
    return sql.Substring(start, end - start).Trim();
}

Splitting into Logical Branches (OR-separated groups) #

To split on OR without breaking subqueries, we can use a stack to track parentheses:

public static List<string> SplitLogicalBranches(string whereClause)
{
    var branches = new List<string>();
    var current = new StringBuilder();
    var parenthesisDepth = 0;
    
    foreach (var c in whereClause)
    {
        if (c == '(') parenthesisDepth++;
        else if (c == ')') parenthesisDepth--;
        
        if (parenthesisDepth == 0 && c.ToString().Equals("or", StringComparison.OrdinalIgnoreCase))
        {
            branches.Add(current.ToString().Trim());
            current.Clear();
        }
        else
        {
            current.Append(c);
        }
    }
    
    // Add the last branch
    if (current.Length > 0)
        branches.Add(current.ToString().Trim());
    
    return branches;
}

This will split your example into:

• a=0 and b= 42 and c=(Select c from azs)
• f=89
• x=10

Splitting AND Conditions within a Branch #

For branches with multiple AND conditions, we can do a similar split (again, ignoring AND inside parentheses):

public static List<string> SplitAndConditions(string branch)
{
    var conditions = new List<string>();
    var current = new StringBuilder();
    var parenthesisDepth = 0;
    
    foreach (var c in branch)
    {
        if (c == '(') parenthesisDepth++;
        else if (c == ')') parenthesisDepth--;
        
        if (parenthesisDepth == 0 && c.ToString().Equals("and", StringComparison.OrdinalIgnoreCase))
        {
            conditions.Add(current.ToString().Trim());
            current.Clear();
        }
        else
        {
            current.Append(c);
        }
    }
    
    if (current.Length > 0)
        conditions.Add(current.ToString().Trim());
    
    return conditions;
}

For the first branch, this gives:

• a=0
• b= 42
• c=(Select c from azs)

Now that we have our conditions split, we can test them one by one to see which returns no rows.

Strategy for Testing #

1. Test each OR branch first: For each branch, run a query like SELECT COUNT(*) FROM ABD WHERE [branch]. If a branch returns 0, that's a candidate to investigate further.
2. Dig into AND conditions in failed branches: For a branch that returns 0, test each individual AND condition (e.g., SELECT COUNT(*) FROM ABD WHERE a=0), then test combinations (e.g., a=0 AND b=42) to see which combination breaks the results.

Example C# Code for Testing #

Assuming you have a method to execute a SQL query and return the row count:

public static int GetRowCount(string connectionString, string tableName, string condition)
{
    var sql = string.IsNullOrEmpty(condition) 
        ? $"SELECT COUNT(*) FROM {tableName}" 
        : $"SELECT COUNT(*) FROM {tableName} WHERE {condition}";
    
    using (var conn = new SqlConnection(connectionString))
    {
        conn.Open();
        using (var cmd = new SqlCommand(sql, conn))
        {
            return (int)cmd.ExecuteScalar();
        }
    }
}

// Usage
var originalSql = "select * from ABD where a=0 and b= 42 and c=(Select c from azs) or f=89 or x=10";
var whereClause = ExtractWhereClause(originalSql);
var branches = SplitLogicalBranches(whereClause);

foreach (var branch in branches)
{
    var count = GetRowCount("your_connection_string", "ABD", branch);
    if (count == 0)
    {
        Console.WriteLine($"Branch returns no results: {branch}");
        var andConditions = SplitAndConditions(branch);
        
        // Test individual AND conditions
        foreach (var cond in andConditions)
        {
            var condCount = GetRowCount("your_connection_string", "ABD", cond);
            Console.WriteLine($"  Condition '{cond}' returns {condCount} rows");
        }
        
        // Optional: Test combinations to find the exact failing pair/group
        // (You could implement logic to test all subsets here)
    }
    else
    {
        Console.WriteLine($"Branch returns {count} results: {branch}");
    }
}

• Subqueries: The code above treats subqueries (like c=(Select c from azs)) as a single condition, which is correct—you need to test the entire subquery condition, not split it apart.
• Case Sensitivity: The code uses case-insensitive checks for AND/OR, which works for most SQL dialects.
• Complex Conditions: If you have conditions with IN, LIKE, BETWEEN, etc., the split logic still works because it only splits on top-level AND/OR outside parentheses.
• Performance: If your table is large, consider limiting the count to 1 instead of counting all rows (e.g., SELECT TOP 1 1 FROM ...)—it's faster since you just need to know if there's any data.

内容的提问来源于stack exchange，提问作者Lakhan Singla