要将三个查询语句合并为一个,以统计不同版本下特定时间创建的记录数,可以使用SQL的CASE WHEN语句或条件聚合函数。
假设原始有三个类似这样的查询:
-- 查询1: 统计版本1的记录数
SELECT COUNT(*) FROM table_name
WHERE version = '1.0' AND create_time BETWEEN '2023-01-01' AND '2023-01-31';
-- 查询2: 统计版本2的记录数
SELECT COUNT(*) FROM table_name
WHERE version = '2.0' AND create_time BETWEEN '2023-01-01' AND '2023-01-31';
-- 查询3: 统计版本3的记录数
SELECT COUNT(*) FROM table_name
WHERE version = '3.0' AND create_time BETWEEN '2023-01-01' AND '2023-01-31';
SELECT
SUM(CASE WHEN version = '1.0' THEN 1 ELSE 0 END) AS version_1_count,
SUM(CASE WHEN version = '2.0' THEN 1 ELSE 0 END) AS version_2_count,
SUM(CASE WHEN version = '3.0' THEN 1 ELSE 0 END) AS version_3_count
FROM table_name
WHERE create_time BETWEEN '2023-01-01' AND '2023-01-31';
SELECT
version,
COUNT(*) AS record_count
FROM table_name
WHERE create_time BETWEEN '2023-01-01' AND '2023-01-31'
AND version IN ('1.0', '2.0', '3.0')
GROUP BY version;
SELECT *
FROM (
SELECT version FROM table_name
WHERE create_time BETWEEN '2023-01-01' AND '2023-01-31'
) AS SourceTable
PIVOT (
COUNT(version)
FOR version IN ([1.0], [2.0], [3.0])
) AS PivotTable;
方案1(条件聚合)是最通用的解决方案,适用于大多数数据库系统,并且结果以列的形式展示,便于阅读和后续处理。
您可以根据实际使用的数据库系统和具体需求选择最适合的方案。