SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE XX (
num  int,
name varchar(10),
type varchar(10));

INSERT INTO XX Values
(1,'abc','er'),
(1,'abc','er'),
(2,'qwer','er'),
(2,'qwer','ty'),
(4,'der','ui'),
(5,'nerr','io');

SELECT *
FROM XX;


num
name
type


1
abc
er

1
abc
er

2
qwer
er

2
qwer
ty

4
der
ui

5
nerr
io


-- 解法一
SELECT *
FROM XX
WHERE type IN (
SELECT type
FROM XX
GROUP BY type
HAVING COUNT(type) > 1)


num
name
type


1
abc
er

1
abc
er

2
qwer
er


-- 解法二
SELECT XX.*
FROM XX
INNER JOIN (
SELECT type
FROM XX
GROUP BY type
HAVING COUNT(type) > 1) AS YY ON YY.type = XX.type


num
name
type


1
abc
er

1
abc
er

2
qwer
er


-- 解法三
SELECT *
FROM XX
WHERE EXISTS (
SELECT YY.type
FROM XX AS YY
WHERE YY.type = XX.type
GROUP BY YY.type
HAVING COUNT(YY.type) > 1)


num
name
type


1
abc
er

1
abc
er

2
qwer
er