如何忽略重复的行 [英] How to ignore duplicate rows
本文介绍了如何忽略重复的行的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
Id状态编号
1242 P 5 。 25
3 P 7 。 5
11 P 9
19 P 9
36 P 9
3 F 0
4 F 0
9 F. 3 。 75
19 F 3 。 75
状态为P的数字和F的数字之和,其状态为P。
我的意思是1242(P),3(P),11(P),19(P),36(P),4(F),9(F)的总和......忽略这些2行3(F)和19(F)。
谢谢!
解决方案
有这个问题。
问题是你申请的任何分组都不会影响总和。你需要一个 Common Table Expressions(CTE) [ ^ ]。
阅读它因为它们非常强大
因此,您的查询可能如下所示:
WITH myCTE1 as (
SELECT ID,状态,编号
来自 table
其中 Status = ' P'),
myCTE2 as (
选择 t.Id,t.Status,t.Number
From tabl e t
其中 t.Status = ' F'
AND NOT EXISTS ( SELECT * FROM myCTE1 WHERE Id = t.Id)
UNION SELECT ID,状态,Number
FROM myCTE1)
SELECT sum(Number) ,状态 FROM myCTE2 GROUP BY 状态
Id Status Number
1242 P 5.25
3 P 7.5
11 P 9
19 P 9
36 P 9
3 F 0
4 F 0
9 F 3.75
19 F 3.75
Sum of number having status 'P' and those 'F' have id which is not in status 'P'.
I mean sum of 1242(P),3(P),11(P),19(P),36(P),4(F),9(F) ... Ignore these 2 rows 3(F) and 19(F).
Thank You!
解决方案
There is a trick to this.
The issue is that any groupings you apply will not effect the sum. You need a Common Table Expressions(CTE)[^].
Read up on it because they are so powerful
So, your query might look like this:
WITH myCTE1 as ( SELECT Id, Status, Number From table where Status = 'P'), myCTE2 as ( Select t.Id, t.Status, t.Number From table t where t.Status = 'F' AND NOT EXISTS (SELECT * FROM myCTE1 WHERE Id = t.Id) UNION SELECT Id, Status, Number FROM myCTE1) SELECT sum(Number), Status FROM myCTE2 GROUP BY Status
这篇关于如何忽略重复的行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文