SELECT TestName,
SUM(TestFee) AS TestFee ,
COUNT(*) "No of Tests"
FROM ALLPATIENTINFO
WHERE ReqDate BETWEEN '2016-11-04' AND '2016-11-05'
GROUP BY TestName
这里只显示日期对应的Testname,总数和计数。但是我想显示保存在数据表中的所有testName。如果测试名称过期,则仅显示testname,testfee&count no将为零。
最佳答案
这里的一种选择是将WHERE
子句的逻辑移到SELECT
子句并使用条件聚合:
SELECT TestName,
SUM(CASE WHEN ReqDate BETWEEN '2016-11-04' AND '2016-11-05'
THEN TestFee ELSE 0 END) AS TestFee,
SUM(CASE WHEN ReqDate BETWEEN '2016-11-04' AND '2016-11-05'
THEN 1 ELSE 0 END) AS "No of Tests in Range",
COUNT(*) AS "Total No of Test"
FROM ALLPATIENTINFO
GROUP BY TestName
这将确保原始数据集中出现的每个测试名称也将出现在结果集中。
关于sql - 我如何根据以下条件编写此sql,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/40439724/