sql-select语句

t40tm48m 于 2021-07-26 发布在 Java

关注(0)|答案(2)|浏览(309)

让我们以下表为例

ID Name Status
1  Jon  pass
2  Jon  fail
3  Jon  fail
4  Snow pass
5  Snow fail
6  Snow fail

我需要写一个查询，以下面的格式显示结果

Name Total Pass Fail
Jon  3     1    2
Snow 3     1    2

我正在用select中的子查询尝试以下查询，但我知道它不正确。请告知。

SELECT 
    Name, 
    count(ID) as Total,
    (SELECT count(ID) FROM results WHERE status = 'pass') as Pass
    (SELECT count(ID) FROM results WHERE status = 'fail') as Fail
FROM results
HAVING count(ID)>2 
GROUP BY Name 
ORDER BY count(ID) desc;

sql subquery oracle10g

来源：https://stackoverflow.com/questions/62484187/sql-select-statement-with-sub-queries

2条答案

按热度按时间

ffscu2ro1#

您可以使用条件聚合：

SELECT 
    Name, 
    COUNT(ID) as Total,
    COUNT(CASE WHEN status = 'pass' THEN 1 END) Pass,
    COUNT(CASE WHEN status = 'fail' THEN 1 END) Fail
FROM results
GROUP BY Name 
HAVING COUNT(ID) > 2
ORDER BY COUNT(ID) desc;

我保留了 HAVING 条款（必须放在 GROUP BY )因为你在你的代码中使用它。
请看演示。
结果：

> NAME | TOTAL | PASS | FAIL
> :--- | ----: | ---: | ---:
> Jon  |     3 |    1 |    2
> Snow |     3 |    1 |    2

赞(0）回复(0）举报 2021-07-26

6qfn3psc2#

尝试下面的脚本-

SELECT 
Name, 
count(ID) as Total,
SUM(CASE WHEN status = 'pass' THEN 1 ELSE 0 END)  as Pass,
SUM(CASE WHEN status = 'fail' THEN 1 ELSE 0 END)  as fail
FROM results
GROUP BY Name 
HAVING count(ID)>2 
ORDER BY count(ID) desc;

赞(0）回复(0）举报 2021-07-26

我来回答

sql-select语句

2条答案

相关问题

热门标签

最新问答