处理某分组列值有重复时，只显示分组的首行列值

 

IF NOT OBJECT_ID('[tb]') IS NULL

    DROP TABLE [tb]

GO

CREATE TABLE [tb]([chuanming] NVARCHAR(10),[huoming] NVARCHAR(10))

INSERT [tb]

SELECT N'东方',N'乙烯' UNION ALL

SELECT N'东方',N'笨' UNION ALL

SELECT N'东方',N'甲烷' UNION ALL

SELECT N'东方',N'碳' UNION ALL

SELECT N'东方',N'石化' UNION ALL

SELECT N'海欣',N'乙烯' UNION ALL

SELECT N'海欣',N'笨' UNION ALL

SELECT N'海欣',N'甲烷' UNION ALL

SELECT N'海欣',N'碳' UNION ALL

SELECT N'海欣',N'石化'

GO

--SELECT * FROM [tb]

 

-->SQL查询如下:

--1.2000/2005通用

SELECT CASE WHEN [huoming]=(SELECT TOP 1 [huoming] FROM tb WHERE [chuanming]=t.[chuanming])

               THEN [chuanming] ELSE ''

        END [chuanming],

    [huoming]

FROM tb t

--2.2005以上用

SELECT CASE ROW_NUMBER()OVER(PARTITION BY [chuanming] ORDER BY GETDATE()) WHEN 1

           THEN [chuanming] ELSE ''

       END [chuanming],

    [huoming]

FROM tb t

/*

chuanming  huoming

---------- ----------

东方       乙烯

           笨

           甲烷

           碳

           石化

海欣       乙烯

           笨

           甲烷

           碳

           石化

 

(10 行受影响)

 

*/

 

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/htl258/archive/2010/04/26/5528553.aspx