单个查询中有多个选择语句

debugcn 发表于 Dev

苏尼基姆

总数应为ShipperID的总和。这是我到目前为止的代码，但是我看到的列全为1。如果您看一下，数字3、2、1列的数字加起来并不等于总数，只显示一串1。

SELECT 
OrderDate,
COUNT(OrderID) AS totalID, 
(SELECT COUNT(ShipperID) FROM Orders WHERE ShipperID='3' GROUP BY OrderDate) AS number3, 
(SELECT COUNT(ShipperID) FROM Orders WHERE ShipperID='2' GROUP BY OrderDate) AS number2, 
(SELECT COUNT(ShipperID) FROM Orders WHERE ShipperID='1' GROUP BY OrderDate) AS number1
FROM Orders 
WHERE OrderDate >= '1996-07-04' 
GROUP BY OrderDate 
ORDER BY OrderDate ASC;

https://www.w3schools.com/sql/trysql.asp?filename=trysql_select_count

例

专线小巴

无需这些多个子查询。您只能扫描该表一次，然后在聚合函数中使用条件表达式：

SELECT case_start_date_time AS eventdatetime, COUNT(*) AS total_riskid, 
    SUM(CASE WHEN transaction_details_analyst_action = 'PASS' THEN 1 ELSE 0 END) as pass_count,
    SUM(CASE WHEN transaction_details_analyst_action = 'FAIL' THEN 1 ELSE 0 END) as fail_count,
    SUM(CASE WHEN transaction_details_analyst_action = 'REINSTATE' THEN 1 ELSE 0 END) as reinstate_count
FROM lz.risk_rcm_ato_flattened
WHERE eventdatetime >= '2020-12-01'
GROUP BY case_start_date_time

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。