数据结构如下
需求:
有两列字段,id,和name。现在一个id可能有多个name,怎么写sql把有2个不同name以上的id找出来,如上图的话按需求只找出1和4
先自己做再抄答案
下面是我写的:
SELECT
name_id, test_name
FROM
test_z
WHERE
name_id IN (
SELECT
name_id
FROM
test_z
GROUP BY
name_id
HAVING
count( test_name ) >1
)GROUP BY
test_name
HAVING
count( test_name ) =1
开发写的
SELECT
*
FROM
test_z
GROUP BY
test_name,
name_id
HAVING
count( test_name ) = 1
AND name_id IN (
SELECT
name_id
FROM
test_z
GROUP BY
name_id
HAVING
count( name_id ) > 1)
开发评价: