CREATE TABLE #A (
id INT,
name varchar(4),
number INT
);
INSERT INTO #A
SELECT 1, 'a', 123 UNION ALL
SELECT 2, 'b', 152 UNION ALL
SELECT 3, 'c', 123 UNION ALL
SELECT 4, 'd', 520 UNION ALL
SELECT 5, 'e', 300;
GO
SELECT
*
FROM
#A main
WHERE
NOT EXISTS (
SELECT 1
FROM #A sub
WHERE main.id <> sub.id AND main.number = sub.number
);
GO
去掉重复数据方式:
select * from #A where id in(select max(id) from #A group by number)