原始表
N1 N02 N03
1 1 5
1 2 5
1 3 5
2 4 5
2 5 5
2 6 5
3 7 5
3 8 5
3 9 5 表结果显示
N1 N02 N03
1 1 5
2 5
3 5
2 4 5
5 5
6 5
3 7 5
8 5
9 5
N1 N02 N03
1 1 5
1 2 5
1 3 5
2 4 5
2 5 5
2 6 5
3 7 5
3 8 5
3 9 5 表结果显示
N1 N02 N03
1 1 5
2 5
3 5
2 4 5
5 5
6 5
3 7 5
8 5
9 5
--> 生成测试数据: #T
IF OBJECT_ID('tempdb.dbo.#T') IS NOT NULL DROP TABLE #T
CREATE TABLE #T (N1 INT,N02 INT,N03 INT)
INSERT INTO #T
SELECT '1','1','5' UNION ALL
SELECT '1','2','5' UNION ALL
SELECT '1','3','5' UNION ALL
SELECT '2','4','5' UNION ALL
SELECT '2','5','5' UNION ALL
SELECT '2','6','5' UNION ALL
SELECT '3','7','5' UNION ALL
SELECT '3','8','5' UNION ALL
SELECT '3','9','5'--SQL查询如下:SELECT
N1,
N02,
CASE WHEN N02 =(SELECT MIN(N02)
FROM #T
WHERE N1=t.N1
)
THEN RTRIM(N03)
ELSE ''
END AS NO3
FROM #T AS t/*
N1 N02 NO3
----------- ----------- ------------
1 1 5
1 2
1 3
2 4 5
2 5
2 6
3 7 5
3 8
3 9 (9 行受影响)
*/
原始表
N1 N02 N03
1 1 5
1 2 5
1 3 5
2 4 5
2 5 5
2 6 5
3 7 5
3 8 5
3 9 5要求是第一列N1的显示第一个,第二个1就不显示出来了,直到显示2
T.N1,T.NO2,TB.NO3
from(
select N1,MIN(NO2)
from TB
group by N1)T
left join TB
on T.N1 = TB.N1
and T.NO2 = TB.NO2
N1 N02 N03
1 1 5
null 2 5
null 3 5
2 4 5
null 5 5
null 6 5
3 7 5
null 8 5
null 9 5
N1 N02 N03
1 1 5
1 2 5
1 3 5
2 4 5
2 5 5
2 6 5
3 7 5
3 8 5
3 9 5 表结果显示
N1 N02 N03
1 1 5
null 2 5
null 3 5
2 4 5
null 5 5
null 6 5
3 7 5
null 8 5
null 9 5
CREATE TABLE #Temps (N01 INT,N02 INT,N03 INT)
INSERT INTO #Temps
SELECT '1','1','5' UNION ALL
SELECT '1','2','5' UNION ALL
SELECT '1','3','5' UNION ALL
SELECT '1','3','5' UNION ALL
SELECT '2','4','5' UNION ALL
SELECT '2','5','5' UNION ALL
SELECT '2','6','5' UNION ALL
SELECT '3','7','5' UNION ALL
SELECT '3','8','5' UNION ALL
SELECT '3','9','5'SELECT * FROM #Temps
DROP TABLE #TempsSELECT
CASE WHEN N02 =(SELECT MIN(N02)
FROM #Temps
WHERE N01=t.N01
)
THEN RTRIM(N01)
ELSE ''
END AS NO1,
N02,
N03
FROM #Temps AS t