本文以SQL Server 2000为例：
SQL code/*=============
===fcuandy=====
===2008.1.10===
=============*/
CREATE TABLE ta(id INT IDENTITY(1,1),cid INT,name VARCHAR(10))
GO
INSERT ta SELECT 1,'a'
UNION ALL SELECT 1,'b'
UNION ALL SELECT 1,'c'
UNION ALL SELECT 2,'d'
UNION ALL SELECT 3,'e'
UNION ALL SELECT 3,'f'
GO
/*
示例1
同一分类中取1条或n条。
单表及多表的写法
*/
-----------------------------------------------------------
--以ta为例，cid为分类id,每个id取一条,我以取最小id为约束条件
SELECT a.* FROM ta a
WHERE NOT EXISTS(SELECT 1 FROM ta WHERE cid=a.cid AND id<a.id)
SELECT a.* FROM ta a
WHERE 1>(SELECT COUNT(*) FROM ta WHERE cid=a.cid AND id<a.id)
SELECT a.* FROM ta a
WHERE id IN (SELECT TOP 1 ID FROM ta WHERE cid=a.cid ORDER BY id)
SELECT a.* FROM ta a
WHERE id = (SELECT TOP 1 ID FROM ta WHERE cid=a.cid ORDER BY id)
SELECT a.* FROM ta a
WHERE id IN (SELECT MIN(ID) FROM ta WHERE cid=a.cid)
SELECT a.* FROM ta a
WHERE id = (SELECT MIN(ID) FROM ta WHERE cid=a.cid)
SELECT a.*
FROM ta a
INNER JOIN
(SELECT MIN(id) mi FROM ta GROUP BY cid) b
>
以上是针对ta单表。 如果多表，下面以二表为例
CREATE TABLE tb(cid INT,className VARCHAR(10))
GO
INSERT tb SELECT 1,'A'
UNION ALL SELECT 2,'B'
UNION ALL SELECT 3,'C'
GO
--ta,tb以cid关联，取每个cid中id最小的一条记录,
需要 tb.className,tb.cid,ta.id,ta.name列。
SELECT b.*,a.*
FROM tb b
INNER JOIN ta a
>
示例2
所谓的多行同组合并
*/
--以ta为例，以cid分组合并，产生如下的数据结果
/*
cid nameS
1 a,b,c
2 d
3 e,f
*/
--函数实现
CREATE FUNCTION myJoinSTR
(
@cid INT
)
RETURNS VARCHAR(1000)
AS
BEGIN
DECLARE @s VARCHAR(1000)
SELECT @s=ISNULL(@s+',','') + name FROM ta WHERE cid = @cid
RETURN @s
END
GO
SELECT DISTINCT cid,dbo.myJoinSTR(cid) nameS FROM ta
GO
/*
示例3
列拆分为行.
以上例生成的数据格式为示例表,将a,b,c以,分融成行,即上个示例的反操作
*/
SELECT DISTINCT cid,dbo.myJoinSTR(cid) nameS INTO tx FROM ta
GO
SELECT * FROM tx
GO
--以系统表构建identity列,并以连表方式来将列拆成行
SELECT IDENTITY(INT,1,1) id INTO # FROM syscolumns,sysobjects
SELECT id,cid,RIGHT(STUFF(nameS+',',id,LEN(names),''),
CHARINDEX(',',REVERSE(STUFF(','+nameS+',',id,LEN(names),'')))) name
FROM tx a
INNER JOIN # b
>
示例4
行转列
*/
--单表，以ta为例. 静态行转列,设cid所有出现的可能值已知
SELECT
cid_1=MAX(CASE WHEN cid=1 THEN name ELSE NULL END),
cid_2=MAX(CASE WHEN cid=2 THEN name ELSE NULL END),
cid_3=MAX(CASE WHEN cid=3 THEN name ELSE NULL END)
FROM ta
--单表,以ta为例，动态行转列,设cid所有出现的可能值未知
DECLARE @s VARCHAR(8000)
SET @s=''
SELECT @s=@s + ',cid_' + RTRIM(cid) + '= MAX(CASE WHEN
cid=' + RTRIM(cid) + ' THEN name ELSE null END) '
FROM ta GROUP BY cid
SELECT @s='SELECT ' + STUFF(@s,1,1,'') + ' FROM ta'
--你可以在这里PRINT @s 看看，就知道跟上面的静态行转列一样的了。
会写静态行转列，就没理由写不出动态的。多表的同理，把多表的静
态行转列写出来，那么动态的也就出来了
EXEC(@s)
GO
DROP TABLE tx,#
GO
DROP TABLE ta,tb
DROP FUNCTION myJoinSTR
GO
