我正试图找到一种方法,该方法可以创建一个新表或一个选择查询,该查询可以统计多个预定义范围内的行数。我当前的限制是,我不能用引用具有类别的表的新列来更新任何表。
我正在尝试创建下表,其中包含属于一个类别的行数:
Id | 类别_Id | 类别名称 | 计数
---|---|---|
1 | 1 | 慢速3 |
2 | 2 | 中等 | 1
3 | 3 | 快速<2>[/tr>
一种方法是横向连接:
select *
from table1 t1 left join lateral
(select count(*) as count
from table2 t2
where t2.teset_time between t1.start_range and t1.end_range
) t2
on 1=1;
您也可以只使用join
和group by
:
select t1.Id, t1.category_id, t1.category_name,
count(t2.id) as count
from table1 t1 left join
table2 t2
on t2.teset_time between t1.start_range and t1.end_range
group by t1.Id, t1.category_id, t1.category_name
请永远不要说"查询不起作用";而不告诉我们什么不起作用。事实上,你应该得到以下语法错误消息
ERROR: column ttc.start_date does not exist
LINE 4: WHERE tt.test_time > ttc.start_date AND
^
HINT: Perhaps you meant to reference the column "ttc.start_range".
这确实说明了很多,不是吗?按照提示,更正查询中的列名,一切都会正常运行:
SELECT *, (
SELECT count(*)
FROM temp_times as tt
WHERE tt.test_time > ttc.start_range AND
tt.test_time <= ttc.end_range
) FROM temp_test_times_classification as ttc
;
演示:https://dbfiddle.uk/?rdbms=postgres_13&fiddle=cfcde9e81d9ee5f8783d13bdf784d25b