数据:列A 列B 列C
1 11 5
1 12 3
1 13 9
1 15 5
…… …… ……能不能直接用SQL读出这样的效果:像上面这种每行第一列都是相同的,只让第一行显示1,下面只要是显示的行都显示为空,查询结果如下:列A 列B 列C
1 11 5
12 3
13 9
15 5
…… …… ……当然列A不光是1,还有很多其他的值。谢谢!
1 11 5
1 12 3
1 13 9
1 15 5
…… …… ……能不能直接用SQL读出这样的效果:像上面这种每行第一列都是相同的,只让第一行显示1,下面只要是显示的行都显示为空,查询结果如下:列A 列B 列C
1 11 5
12 3
13 9
15 5
…… …… ……当然列A不光是1,还有很多其他的值。谢谢!
(case when exists(select 1 from tName where 列A=t.列A and 列B<t.列B) then '' else rtrim(t.列A) end),
t.列B,t.列C
from
tName t
order by
t.列A,t.列B,t.列C
-- Author: liangCK 小梁
---------------------------------
--> 生成测试数据: @T
DECLARE @T TABLE (列A INT,列B INT,列C INT)
INSERT INTO @T
SELECT 1,11,5 UNION ALL
SELECT 1,12,3 UNION ALL
SELECT 1,13,9 UNION ALL
SELECT 2,15,5 UNION ALL
SELECT 2,11,5 UNION ALL
SELECT 2,12,3 UNION ALL
SELECT 3,13,9 UNION ALL
SELECT 3,15,5--SQL查询如下:;WITH Liang AS
(
SELECT
*,
RID=ROW_NUMBER() OVER(PARTITION BY 列A ORDER BY 列A),
flag=列A
FROM @T
)
SELECT
CASE WHEN RID=1 THEN RTRIM(列A) ELSE '' END AS 列A,
列B,
列C
FROM Liang
ORDER BY flag,RID/*
列A 列B 列C
------------ ----------- -----------
1 11 5
12 3
13 9
2 15 5
11 5
12 3
3 13 9
15 5(8 行受影响)
*/
INSERT @TB
SELECT 1, 11, 5 UNION ALL
SELECT 1, 12, 3 UNION ALL
SELECT 1, 13, 9 UNION ALL
SELECT 1, 15, 5 UNION ALL
SELECT 2, 23, 23 UNION ALL
SELECT 2, 23, 23SELECT *,ID2=IDENTITY(INT,1,1) INTO # FROM @TB ORDER BY IDSELECT CASE WHEN SEQ=1 THEN RTRIM(ID) ELSE '' END AS ID,COL,COL2
FROM (
SELECT *,SEQ=ID2-(SELECT COUNT(*) FROM # WHERE ID<A.ID) FROM # AS A
) TDROP TABLE #
/*
ID COL COL2
------------ ----------- -----------
1 11 5
12 3
13 9
15 5
2 23 23
23 23
*/
FROM TABLE1 GROUP BY A1) then 列A else '' end 列A,列B,列C from table1 order by id
INSERT INTO @T
SELECT 1,11,5 UNION ALL
SELECT 1,12,3 UNION ALL
SELECT 1,13,9 UNION ALL
SELECT 2,15,5 UNION ALL
SELECT 2,11,5 UNION ALL
SELECT 2,12,3 UNION ALL
SELECT 3,13,9 UNION ALL
SELECT 3,15,5
select (case rank1 when 1 then 列A else '' end) 列A,列B,列C from
(select *,ROW_NUMBER()over(partition by 列A order by 列B) rank1 from @T) D