数据如下
ID1 ID2 ID3 NAME PWD ORDERS
601 1533 638 jiyure-7946 NULL CH20100625000001
601 1534 639 jiyure-7946 NULL CH20100625000001
601 1535 640 jiyure-7946 NULL CH20100625000001
602 1533 638 [email protected] NULL CH20100629000001
602 1534 639 [email protected] NULL CH20100629000001
602 1535 640 [email protected] NULL CH20100629000001
604 1533 638 [email protected] NULL CH20100630000001
604 1534 639 [email protected] NULL CH20100630000001
604 1535 640 [email protected] NULL CH20100630000001查询结果如下
ID1 ID2 ID3
601 1533 638
602 1534 639
604 1535 640
。
ID1 ID2 ID3 NAME PWD ORDERS
601 1533 638 jiyure-7946 NULL CH20100625000001
601 1534 639 jiyure-7946 NULL CH20100625000001
601 1535 640 jiyure-7946 NULL CH20100625000001
602 1533 638 [email protected] NULL CH20100629000001
602 1534 639 [email protected] NULL CH20100629000001
602 1535 640 [email protected] NULL CH20100629000001
604 1533 638 [email protected] NULL CH20100630000001
604 1534 639 [email protected] NULL CH20100630000001
604 1535 640 [email protected] NULL CH20100630000001查询结果如下
ID1 ID2 ID3
601 1533 638
602 1534 639
604 1535 640
。
我假设去ID2的最大的CREATE TABLE #tb
(
id1 int,
id2 int,
id3 int
)
INSERT INTO #tb
SELECT 1,1,1 UNION ALL
SELECT 1,2,2 UNION ALL
SELECT 1,3,3 UNION ALL
SELECT 2,1,1 UNION ALL
SELECT 2,2,2 UNION ALL
SELECT 2,3,3 UNION ALL
SELECT 3,1,1 UNION ALL
SELECT 3,2,2 UNION ALL
SELECT 3,3,3 UNION ALL
SELECT 4,1,1 UNION ALL
SELECT 4,2,2 UNION ALL
SELECT 4,3,3DELETE a FROM
#tb a JOIN
(
SELECT
id1,max(id2) m
FROM #tb GROUP BY id1
) b ON a.id1 = b.id1 AND a.id2 < b.mSELECT * FROM #tb
id1 id2 id3
----------- ----------- -----------
1 3 3
2 3 3
3 3 3
4 3 3(4 行受影响)
ID1 ID2 ID3
601 1533 638
602 1534 639
604 1535 640要求ID1 ID2 ID3中没有任何重复
ID1和ID1 ID2和ID2 ID3和ID3 中不会出现2次一样的ID
group by id1
FROM (
SELECT ROW_NUMBER() OVER(ORDER BY ID1 ASC) ID,ID1 FROM TB
) T1
FULL JION (
SELECT ROW_NUMBER() OVER(ORDER BY ID2 ASC) ID,ID2 FROM TB
) T2 ON T1.ID=T2.ID
FULL JION (
SELECT ROW_NUMBER() OVER(ORDER BY ID3 ASC) ID,ID3 FROM TB
) T3 ON T1.ID=T3.ID
DECLARE @T TABLE
(
id1 int,
id2 int,
id3 int
)
INSERT INTO @T
SELECT 601, 1533, 638 UNION ALL
SELECT 601, 1534, 639 UNION ALL
SELECT 601, 1535, 640 UNION ALL
SELECT 602, 1533, 638 UNION ALL
SELECT 602, 1534, 639 UNION ALL
SELECT 602, 1535, 640 UNION ALL
SELECT 604, 1533, 638 UNION ALL
SELECT 604, 1534, 639 UNION ALL
SELECT 604, 1535, 640SELECT DISTINCT T1.ID1,T2.ID2,T3.ID3
FROM (
SELECT ROW_NUMBER() OVER(ORDER BY ID1 ASC) ID,ID1 FROM @T GROUP BY ID1
) T1
FULL JOIN (
SELECT ROW_NUMBER() OVER(ORDER BY ID2 ASC) ID,ID2 FROM @T GROUP BY ID2
) T2 ON T1.ID=T2.ID
FULL JOIN (
SELECT ROW_NUMBER() OVER(ORDER BY ID3 ASC) ID,ID3 FROM @T GROUP BY ID3
) T3 ON T1.ID=T3.ID
/*
ID1 ID2 ID3
----------- ----------- -----------
601 1533 638
602 1534 639
604 1535 640
*/
FROM TB T1
INNER JOIN (
.... --上面那一串
) T2 ON T1.ID1=T2.ID1 AND T1.ID2=T2.ID2 AND T1.ID3=T2.ID3
select a.* from T a INNER JOIN
(select id1,MIN(id3)as id3 from T GROUP BY id1) B
ON B.id1=a.id1 and B.id3=a.id3