看到SQL版问的最多的问题,每天都是重复的,今天无事,整一下发上.行转列,列分行,行合并列,etc.

/*=============

===fcuandy=====

===2008.1.23===

=============*/



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

	ON id = mi

--etc.其它的组合写法再不累赘



--以cid为分类，每个cid取id最小的2条记录，2可以适当修改

SELECT a.* FROM ta a

	WHERE 2>(SELECT COUNT(*) FROM ta WHERE cid=a.cid AND id<a.id)

SELECt a.* FROM ta a

	WHERE id IN (SELECT TOP 2 ID FROM ta WHERE cid=a.cid ORDER BY ID)

SELECT a.*

	FROM ta a

INNER JOIN

	(SELECT ID,CNT=(SELECT COUNT(*) FROM ta WHERE cid=x.cid AND id<x.id) FROM ta x) b

	ON a.id = b.id AND cnt<2

--etc.其它的组合写法再不累赘









--上面是针对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

	ON a.cid = b.cid

	WHERE NOT EXISTS(SELECT 1 FROM ta WHERE cid=a.cid AND id<a.id)

GO

--多表与单表取数思路一对致，多一次连表操作，可以照上面单表的把其它写法改出来.

--同样，取每个cid中id最小的前n条记录，一样的方法

--需要注意的是连表时，可以用内连，左连，或是老式的多表写法(from ta ,tb where ta.cid=tb.cid默认转换为内连),采用哪种方式依具你的业务需求。







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

/*

示例 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

	ON SUBSTRING(names+',',id,1)=','

ORDER BY cid

--以动态语句或循环，或函数的方式略去





/*示例 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