如何筛选Table2指定行并统计Table1对应时间区间的记录数与总价

Hey there, let's break down what's wrong with your current query and fix it step by step to meet your requirement.

Table Structures #

First, let's restate your table data clearly:

table1

table2

Your Goal #

You need to:

1. Filter rows from table2 where startTm >= x and endTm <= y (replace x/y with your actual values)
2. For each filtered row, calculate:
   • Total number of records in table1 where time is between the row's startTm and endTm, and col1/col2 match
   • Sum of price for those matching table1 records

What's Broken in Your Current Query #

Your use of user variables (@sTime, @eTime) won't work as expected. Here's why:
SQL executes subqueries before the main query assigns values to variables. When your left-joined subquery runs, @sTime and @eTime are still uninitialized (usually NULL), so the WHERE time BETWEEN @sTime AND @eTime condition matches nothing. That's why you're getting incorrect or empty totalNo/totalPrice values.

Correct Solutions #

Here are two reliable approaches to get the results you need:

Option 1: Correlated Subqueries (Simple & Readable)

This method runs a small subquery for each row in your filtered table2—great for small to medium datasets:

SELECT
    t2.id,
    t2.startTm,
    t2.endTm,
    t2.col1,
    t2.col2,
    -- Count matching records from table1
    (SELECT COUNT(*) 
     FROM table1 t1
     WHERE t1.col1 = t2.col1
       AND t1.col2 = t2.col2
       AND t1.time BETWEEN t2.startTm AND t2.endTm) AS totalNo,
    -- Sum prices of matching records
    (SELECT SUM(t1.price)
     FROM table1 t1
     WHERE t1.col1 = t2.col1
       AND t1.col2 = t2.col2
       AND t1.time BETWEEN t2.startTm AND t2.endTm) AS totalPrice
FROM table2 t2
WHERE t2.startTm >= [your_x_value]
  AND t2.endTm <= [your_y_value];

Option 2: JOIN + GROUP BY (More Efficient for Large Datasets)

If you're working with bigger tables, this approach is better because it avoids running duplicate subqueries. We use COALESCE to return 0 instead of NULL when there are no matching records in table1:

SELECT
    t2.id,
    t2.startTm,
    t2.endTm,
    t2.col1,
    t2.col2,
    COUNT(t1.price) AS totalNo,
    COALESCE(SUM(t1.price), 0) AS totalPrice
FROM table2 t2
LEFT JOIN table1 t1
    ON t1.col1 = t2.col1
    AND t1.col2 = t2.col2
    AND t1.time BETWEEN t2.startTm AND t2.endTm
WHERE t2.startTm >= [your_x_value]
  AND t2.endTm <= [your_y_value]
GROUP BY t2.id, t2.startTm, t2.endTm, t2.col1, t2.col2;

Example Output (Using x=11, y=26) #

If you filter table2 with startTm >=11 and endTm <=26, both queries will return this result:

Quick breakdown:

• Row 1 (12-20): Matches table1 rows with time 13 and 15 → count=2, sum=100+150=250
• Row 2 (15-26): Matches table1 row with time 15 → count=1, sum=150
• Row 3 (11-13): Matches table1 row with time13 → count=1, sum=100

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