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

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

--  Author : htl258(Tony)

--  Date   : 2010-04-26 09:03:25

--  Version:Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)

--          Jul  9 2008 14:43:34

--          Copyright (c) 1988-2008 Microsoft Corporation

--          Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)

--  Blog   : http://blog.youkuaiyun.com/htl258

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

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

--> 生成测试数据表:tb

 

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 行受影响)

 

*/