我有一張這樣的桌子：
| ID | 地位 | 年級 |
|---|---|---|
| 123 | 全面的 | 一個 |
| 123 | 當前的 | 乙 |
| 234 | 全面的 | 乙 |
| 234 | 當前的 | D |
| 345 | 全面的 | C |
| 345 | 當前的 | 一個 |
請問如何顯示符合條件
的 id 數量： 等級排序如下 A > B > C > D > F，
并且總等級必須大于或等于當前等級
是否需要先使用 CASE() 將等級切換為數字？
例如 A = 4, B = 3, C = 2, D = 1, F = 0
在表中，應該有 345 個不匹配的條件。如何顯示下表：
| qty_pass_the_condition | qty_fail_the_condition | total_ids |
|---|---|---|
| 2 | 1 | 3 |
和\
| 失敗ID |
|---|
| 345 |
謝謝。
uj5u.com熱心網友回復：
由于等級是連續的，你可以做order by desc這個數字。對于第一個結果，您可以執行以下操作
select
sum(case when GradeRankO >= GradeRankC then 1 else 0 end) AS
qty_pass_the_condition,
sum(case when GradeRankO < GradeRankC then 1 else 0 end) AS
qty_fail_the_condition,
count(*) AS total_ids
from
(
select * from (
select Id,Status,
Rank() over (partition by Id order by grade desc) GradeRankO
from YourTbale
) as a where Status='Overall'
) as b
inner join
(
select * from (
select Id,Status,
Rank() over (partition by Id order by grade desc) GradeRankC
from YourTbale
) as a where Status='Current'
) as c on b.Id=c.Id
對于第二個，你可以在下面做
select
b.Id fail_id
from
(
select * from (
select Id,Status,
Rank() over (partition by Id order by grade desc) GradeRankO
from Grade
) as a where Status='Overall'
) as b
inner join
(
select * from (
select Id,Status,
Rank() over (partition by Id order by grade desc) GradeRankC
from Grade
) as a where Status='Current'
) as c on b.Id=c.Id
where GradeRankO < GradeRankC
uj5u.com熱心網友回復：
您可以為此使用非常簡單的條件聚合，不需要視窗函式。
- A
Pass是當Overall具有grade小于或等于的行時Current，“小于”按 AZ 順序排列。 - 然后再次聚合整個表，并且
qty_pass_the_condition只是Pass. 并且qty_fail_the_condition是相反的。
SELECT
qty_pass_the_condition = COUNT(t.Pass),
qty_fail_the_condition = COUNT(*) - COUNT(t.Pass),
total_ids = COUNT(*)
FROM (
SELECT
t.id,
Pass = CASE WHEN MIN(CASE WHEN t.status = 'Overall' THEN t.grade END) <=
MIN(CASE WHEN t.status = 'Current' THEN t.grade END)
THEN 1 END
FROM YourTable t
GROUP BY
t.id
) t;
要查詢實際失敗的 ID，只需使用一個HAVING子句：
SELECT
t.id
FROM YourTable t
GROUP BY
t.id
HAVING MIN(CASE WHEN t.status = 'Overall' THEN t.grade END) >
MIN(CASE WHEN t.status = 'Current' THEN t.grade END);
db<>小提琴
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/504867.html