So I have a table like this :
所以我有一个这样的表:
---------
id, keyid
---------
1, 3
1, 5
1, 6
2, 1
2, 1
2, 3
4, 1
I want the output of the query to be
1,3
2,1
4,1
If i use select distinct(id,keyid) from table it applies the distinct on the pair of id,keyid and not just id alone.
如果我在表中使用select distinct(id,keyid),它会将id应用于id,keyid而不仅仅是id。
2 个解决方案
#1
13
select id, min(keyid) from tbl group by id
#2
1
If you want the min value of KeyId for each Id, then user194076's answer will definitely work.
如果你想要每个Id的KeyId的最小值,那么user194076的答案肯定会有效。
If you want the first occurring value of KeyId for each Id, you could use:
如果您希望每个Id的KeyId的第一个出现值,您可以使用:
WITH CTE AS (
SELECT Id, KeyId, ROW_NUMBER() OVER (PARTITION BY Id ORDER BY Id) AS RN
FROM tbl
)
SELECT Id, KeyId FROM CTE WHERE RN = 1
I've tested both using STATISTICS IO and STATISTICS TIME and they appear to be the same in terms of performance, so really depends on what your exact needs are.
我已经使用STATISTICS IO和STATISTICS TIME进行了测试,它们在性能方面似乎相同,所以真正取决于您的确切需求。
#1
13
select id, min(keyid) from tbl group by id
#2
1
If you want the min value of KeyId for each Id, then user194076's answer will definitely work.
如果你想要每个Id的KeyId的最小值,那么user194076的答案肯定会有效。
If you want the first occurring value of KeyId for each Id, you could use:
如果您希望每个Id的KeyId的第一个出现值,您可以使用:
WITH CTE AS (
SELECT Id, KeyId, ROW_NUMBER() OVER (PARTITION BY Id ORDER BY Id) AS RN
FROM tbl
)
SELECT Id, KeyId FROM CTE WHERE RN = 1
I've tested both using STATISTICS IO and STATISTICS TIME and they appear to be the same in terms of performance, so really depends on what your exact needs are.
我已经使用STATISTICS IO和STATISTICS TIME进行了测试,它们在性能方面似乎相同,所以真正取决于您的确切需求。