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