mysqli查询结果不符合预期，SQL筛选逻辑及日期处理求助

It sounds like your current query isn’t properly filtering out bow entries that already exist in the comeandtradaysparticipant table for your target date. Let’s break down how to fix this issue.

The Root Problem #

When you filter for date = '2018-01-24', row C still appears because your existing logic doesn’t explicitly check if that specific BowID has an entry in comeandtradaysparticipant for that exact date. You need to correlate the BowID and date in your exclusion check to get accurate results.

Corrected Query Using NOT EXISTS #

This approach is efficient and straightforward—it directly checks for the absence of a matching entry in the participant table for the same BowID and target date:

SELECT b.BowID, b.bowCode
FROM bows b -- Replace with your actual main table name
WHERE NOT EXISTS (
    SELECT 1
    FROM comeandtradaysparticipant c
    WHERE c.BowID = b.BowID
      AND c.date = '2018-01-24'
)

Alternative: LEFT JOIN + IS NULL #

If you prefer using joins, this method achieves the same result by joining tables and filtering out rows where a match was found:

SELECT b.BowID, b.bowCode
FROM bows b
LEFT JOIN comeandtradaysparticipant c
  ON b.BowID = c.BowID
  AND c.date = '2018-01-24'
WHERE c.BowID IS NULL

Integrating PHP’s date() Function #

To use the current date dynamically, always use prepared statements to avoid SQL injection risks. Here’s how to implement this in PHP:

$currentDate = date("Y-m-d");

// Prepare the query (using PDO as an example)
$stmt = $pdo->prepare("
    SELECT b.BowID, b.bowCode
    FROM bows b
    WHERE NOT EXISTS (
        SELECT 1
        FROM comeandtradaysparticipant c
        WHERE c.BowID = b.BowID
          AND c.date = ?
    )
");

// Execute with the current date parameter
$stmt->execute([$currentDate]);

// Fetch the filtered results
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);

Why This Works #

Both methods ensure you only return BowIDs that have no corresponding entry in comeandtradaysparticipant for the specified date. The NOT EXISTS clause directly verifies the absence of a matching record, while the LEFT JOIN approach filters out rows where a match was found (indicated by c.BowID IS NULL).

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