27,579
社区成员
发帖
与我相关
我的任务
分享
SELECT * FROM (
SELECT *,
ROW_NUMBER()OVER(PARTITION BY name,cardno,type ORDER BY remark) rn
FROM 表名
)t
WHERE rn=1
USE tempdb
GO
IF OBJECT_ID('t') IS NOT NULL DROP TABLE t
GO
CREATE TABLE t(
a INT,
b INT,
c INT,
d INT,
e INT
)
GO
INSERT INTO t VALUES(1,2,3,4,5)
INSERT INTO t VALUES(1,1,2,1,5)
INSERT INTO t VALUES(1,1,2,4,5)
INSERT INTO t VALUES(1,1,4,4,5)
;WITH cte AS (
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS rid,* FROM t
)
SELECT * FROM cte AS X WHERE NOT EXISTS(
SELECT 1 FROM cte AS Y WHERE x.rid!=y.rid AND x.a=y.a AND x.b=y.b AND x.c=y.c
)
SELECT
*
FROM
t tt
WHERE
EXISTS
(
SELECT
*
FROM
dbo.t
WHERE
t.a <> tt.a
OR t.b <> tt.b
OR t.c <> tt.c
);