如何简化含AND/OR运算符的SQL关联求和查询？

Hey there! Let's break down how to streamline your existing SQL query while keeping the same logic and expected results (books=15, pens=30).

First, let's recap your original query for context:

SELECT SUM(books) AS books, SUM(pens) AS pens FROM plan JOIN item ON plan.id = item.plan_id AND item.comp_id = '1' AND (item.expiry IS NULL OR item.expiry > NOW()) ;

Simplification Option 1: Clean Up Structure with Table Aliases & Separated Conditions #

The biggest win here is making the query more readable by using table aliases and separating join logic from filtering logic. Here's the revised version:

SELECT SUM(p.books) AS books, SUM(p.pens) AS pens
FROM plan p
JOIN item i ON p.id = i.plan_id
WHERE i.comp_id = '1'
  AND (i.expiry IS NULL OR i.expiry > NOW());

• Table aliases (p for plan, i for item) cut down on repetitive typing and make the query more compact.
• Moving non-join filters (comp_id check, expiry condition) to the WHERE clause keeps the ON clause focused solely on how the two tables relate, which follows SQL best practices and makes the query easier to debug later.

Simplification Option 2: Condense the Expiry Check #

If you want to make the expiry condition even tighter, you can use COALESCE to handle the NULL case in one line. This works because we're replacing NULL expiry values with a date that's definitely in the future (so it will always pass the > NOW() check):

SELECT SUM(p.books) AS books, SUM(p.pens) AS pens
FROM plan p
JOIN item i ON p.id = i.plan_id
WHERE i.comp_id = '1'
  AND COALESCE(i.expiry, NOW() + INTERVAL 1 DAY) > NOW();

This is functionally identical to your original condition but reduces the number of logical checks in the query.

Both of these versions will return the exact same result as your original query, but they're cleaner and easier to maintain.

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