SQL版问的最多的问题.行转列,列分行,行合并列,etc.

ehxz

实现的方法还有很多,时间有限,没有一一列出来,新手可以参考一下,不要再发重复贴子了,每点一个"高难度xxx"之类的贴子进去都是这几种.

bom树之类的没有写例子,都差不多.有时间的朋友可以发上来贴上去.

SQL code/*=============
===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



以上示例皆以sql2000为例.   2005下的方法杂多,没有贴了.
FROM:http://topic.csdn.net/u/20080123 ... 9-f2c3ca331f0c.html