SQL小需求

今天在群里讨论一个需求：(无聊 就写了一下)

需求：请教一条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'