[转]行转列,列分行,行合并列,etc

实现的方法还有很多,时间有限,没有一一列出来,新手可以参考一下,不要再发重复贴子了,每点一个"高难度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