我有一张records商店ID，处理批次ID和开始时间的表，如下所示：
|store_id | batch_id | process_start_time |
| A | 1 | 10 |
| B | 1 | 40 |
| C | 1 | 30 |
| A | 2 | 400 |
| B | 2 | 800 |
| C | 2 | 600 |
| A | 3 | 10 |
| B | 3 | 80 |
| C | 3 | 90 |
在此，行需要由分组batch_id和time_taken是差的process_start_time的商店A和商店Ç。
因此，预期结果将是：
batch_id | time_taken
1 | 20
2 | 200
3 | 80
我试图做类似的事情：
select batch_id, ((select process_start_time from records where store_id = 'C') - (select process_start_time from records where store_id = 'A')) as time_taken
from records group by batch_id;
但是无法找出在该特定组中选择特定的行。
感谢您的调查！
更新：商店C的process_start_time列不一定最大
您似乎想要条件聚合和算术运算：
select batch_id,
(max(case when store_id = 'C' then process_start_time end) -
min(case when store_id = 'A' then process_start_time end)
) as diff
from records
group by batch_id;
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句