将同一id与一个表中字符串中的2个值进行比较

Question

我有一个这样的表：

AB[/tr>DCA

id	状态	等级
123	总体
123	当前	B
234	总体
234	当前
345	总体
345	当前

由于等级是连续的，您可以执行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

您可以使用非常简单的条件聚合，不需要窗口函数。

• Pass是当Overall的行具有小于或等于Current的grade时；小于"；为A-Z顺序
• 然后在整个表上再次聚合，qty_pass_the_condition就是Pass中非null的数量。CCD_ 8与此相反

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<gt；小提琴