为何多条件SQL查询返回额外行？求最优SQL查询方案

Hey there! Let's break down why your query is spitting out extra rows when using multiple filters, and get you the optimal solution sorted out.

The Root of the Problem #

Your current approach uses UNION to stitch together results from separate SELECT statements. Here's the catch: UNION acts like a logical OR—it returns every row that matches any single condition, not rows that match all your selected filters. That's why when you try to filter for both STATUS=SCHEDULED and CUSTOMER ID=87, you're getting all rows with that status plus all rows with that customer ID, instead of only the rows that meet both criteria.

The Optimal SQL Solution #

Instead of chaining queries with UNION, use a single SELECT statement with combined conditions. The key is to use AND for filters that must all be true, and add checks to ignore empty filter parameters so they don't break your results.

Example: All Filters Must Be Satisfied

This query will only return rows that match all non-empty filter criteria:

SELECT * 
FROM workforce_customerorder 
WHERE 
  -- Ignore ORDER_ID filter if the parameter is empty
  (ORDER_ID LIKE '$sOrder' OR '$sOrder' = '')
  -- Require CUSTOMER_ID match only if the parameter is set
  AND (CUSTOMER_ID LIKE '%$sCustomerID%' OR '$sCustomerID' = '')
  -- Require AGENT_NUMBER match only if the parameter is set
  AND (AGENT_NUMBER LIKE '%$sAgentNumber%' OR '$sAgentNumber' = '')
  -- Require STATUS match only if the parameter is set
  AND (STATUS LIKE '$sStatus' OR '$sStatus' = '')
  -- Require GST_NUMBER match only if the parameter is set
  AND (GST_NUMBER LIKE '$sGST' OR '$sGST' = '')
  -- Require ORDER_DATE range only if both dates are provided
  AND (DATE(ORDER_DATE) BETWEEN '$sOrderDateFrom' AND '$sOrderDateTo' OR '$sOrderDateFrom' = '' OR '$sOrderDateTo' = '')

Example: Any Filter Can Be Satisfied

If you want rows that match at least one filter (instead of all), replace the ANDs with ORs—just wrap each condition group in parentheses to avoid logic mix-ups:

SELECT * 
FROM workforce_customerorder 
WHERE 
  (ORDER_ID LIKE '$sOrder' AND '$sOrder' != '')
  OR (CUSTOMER_ID LIKE '%$sCustomerID%' AND '$sCustomerID' != '')
  OR (AGENT_NUMBER LIKE '%$sAgentNumber%' AND '$sAgentNumber' != '')
  OR (STATUS LIKE '$sStatus' AND '$sStatus' != '')
  OR (GST_NUMBER LIKE '$sGST' AND '$sGST' != '')
  OR (DATE(ORDER_DATE) BETWEEN '$sOrderDateFrom' AND '$sOrderDateTo' AND '$sOrderDateFrom' != '' AND '$sOrderDateTo' != '')

Critical Security Reminder #

Your current code directly plugs variables into the SQL string, which is a massive security risk (SQL injection attacks). Always use prepared statements (parameterized queries) instead. For example, in PHP with PDO:

$stmt = $pdo->prepare("
  SELECT * 
  FROM workforce_customerorder 
  WHERE 
    (ORDER_ID LIKE ? OR ? = '')
    AND (CUSTOMER_ID LIKE ? OR ? = '')
    AND (AGENT_NUMBER LIKE ? OR ? = '')
    AND (STATUS LIKE ? OR ? = '')
    AND (GST_NUMBER LIKE ? OR ? = '')
    AND (DATE(ORDER_DATE) BETWEEN ? AND ? OR ? = '' OR ? = '')
");
// Bind your filter values to the prepared statement
$stmt->execute([
  $sOrder, $sOrder,
  "%$sCustomerID%", $sCustomerID,
  "%$sAgentNumber%", $sAgentNumber,
  $sStatus, $sStatus,
  $sGST, $sGST,
  $sOrderDateFrom, $sOrderDateTo, $sOrderDateFrom, $sOrderDateTo
]);
$results = $stmt->fetchAll();

Why Your Earlier "AND" Attempt Failed #

Chances are, when you tried using AND before, one of your filter variables was empty. For example, if $sOrder was blank, ORDER_ID LIKE '' would match no rows, making the entire query return nothing. The conditional checks for empty parameters fix this gap.

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