选择具有相同id的行数（SQL） [英] Select count of rows with same id (SQL)
问题描述
您好，
我的问题是我有一个包含许多列的表格，但为了这个问题，我有两个重要的列，
1- ID_NO
2- CurrentStepName
CurrentStepName的值如< b> IV 和 EV ，在ID_NO栏中有一些ID重复用于IV和EV。
例如：
ID_NO ---------- CurrentStepName
1 ---------------- IV
1-- -------------- EV
2 ---------------- IV
3 ---------------- EV
4 ---------------- IV
4 ---------------- EV
如果您在上面的示例中看到ID为1和4，则重复这两个IV和EV。
现在我想要的是从IV中删除一个与EV重复的ID。
我想要的结果如下所示。
ID_NO ---------- CurrentStepName
1 ---------------- EV
2 ---------------- IV
3 -------- -------- EV
4 ---------------- EV
在上面的结果集中，IV的1和4 ID被删除了我想要这样。然后我想计算CurrentStepName的总数。
我希望我已经清楚了。
一如既往地期待您的帮助。
我尝试过的方法：
尝试了很多但却无法得到结果
Hello,
My question is i have a table with many columns in it, but for the sake of this question i have two important columns in it,
1- ID_NO
2- CurrentStepName
The CurrentStepName has values like IV and EV and in ID_NO column there are some IDs that are repeating for IV and EV.
For example:
ID_NO----------CurrentStepName
1----------------IV
1----------------EV
2----------------IV
3----------------EV
4----------------IV
4----------------EV
If you see in above example IDs 1 and 4 are repeating for both IV and EV.
Now what i want is to eliminate one ID from IV where it is duplicated with EV.
The result that i want is like as follow.
ID_NO----------CurrentStepName
1----------------EV
2----------------IV
3----------------EV
4----------------EV
In above result set the 1 and 4 ID of IV is removed i want it this way. And then i want to calculate the total count of CurrentStepName.
I hope i have made myself clear.
Looking forward to your help as always.
What I have tried:
Tried many things but still unable to get the result
推荐答案
为了能够检测'重复'，你可以使用< a href =https://msdn.microsoft.com/en-us/library/ms186734.aspx> ROW_NUMBER（） [ ^ ]内联函数：
To be able to detect 'duplicates', you can use ROW_NUMBER()[^] inline function:
SELECT *, ROW_NUMBER() OVER(PARTITION BY ID_NO ORDER BY CurrentStepName) AS RowNo
FROM YourTable
以上代码返回：
Above code returns:
ID_NO CurrentStepName RowNo
1 EV 1
1 IV 2
2 IV 1
3 EV 1
4 EV 1
4 IV 2
你需要做的就是用 RowNo = 2 写删除行的查询。
试试！
All you need to do is to write query to delete rows with RowNo=2.
Try!
谷歌搜索是你的朋友......以下是一个快速搜索： SQL WHERE子句 [ ^ ]。所以：
Google Search is your friend... Here is what a quick search turned up: SQL WHERE Clause[^]. So:
SELECT .... WHERE ID='?'
你能放这个吗（子查询）
Can you put this (subquery)
select count(ID_NO) as TotalCount from table1
where currentstepname in ('EV','IV')
group by ID_NO having count(ID_NO)>1
进入临时表？ - 不确定确切的语法，因为它取决于您使用的是哪个数据库，但是类似于
into a temp table ? - not sure of the exact syntax because it depends on which DB you are using, but something like
create temp table multi_IDNOs as select ID_NO, count(ID_NO) as TotalCount from table1
where currentstepname in ('EV','IV')
group by ID_NO having count(ID_NO)>1
然后您的查询变为（我认为）
then your query becomes (I think)
select ID_NO, currentstepname from table
where ID_NO in (select ID_NO from multi_IDNOs) and currentstepname = "EV"
union
select ID_NO, currentstepname from table
where ID_NO Not in (Select ID_No from multi_IDNOs)
我用子查询思考这个问题的原始方式很可能是表现不佳，如果没有它就可以做到这一点可能更好
警告 - 我的SQ L是生锈的，所以，用它作为指导/想法
The original way I thought about this with the subquery, is likely bad performance-wise, if you can do it without it may be nicer
caveat - my SQL is as rusty as, so, use it for guidance/ideas
这篇关于选择具有相同id的行数（SQL）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
