今天在群里讨论一个需求:(无聊 就写了一下)
需求:请教一条sql,我id不唯一,我想取name的值有0也有非0的id,比如下面我要取的是1和4
id name
1 0
1 1
1 2
2 0
3 0
4 0
4 2
5 1
5 2
6 0
6 0
解答:
-- 1.写法1:子查询
SELECT ID,NAME
FROM TEST
WHERE ID IN (SELECT ID FROM TEST GROUP BY ID HAVING COUNT(DISTINCT NAME) > 1)
AND NAME = '0';
-- 2.写法2:分析函数
SELECT *
FROM (SELECT ID, NAME, COUNT(DISTINCT NAME) OVER(PARTITION BY ID) AS RN FROM TEST) T
WHERE RN > 1
AND NAME = '0'