同一类型取1-N条记录

/*================fcuandy========2008.1.23================*/CREATE TABLE ta(id INT IDENTITY(1,1),cid INT,name VARCHAR(10))GOINSERT 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 aINNER 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 aINNER 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))GOINSERT 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 bINNER 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 nameS1 a,b,c2 d3 e,f*/--函数实现CREATE FUNCTION myJoinSTR(@cid INT)RETURNS VARCHAR(1000)ASBEGIN DECLARE @s VARCHAR(1000) SELECT @s=ISNULL(@s+',','') + name FROM ta WHERE cid = @cid RETURN @sENDGOSELECT DISTINCT cid,dbo.myJoinSTR(cid) nameS FROM taGO/*示例 3列拆分为行.以上例生成的数据格式为示例表,将a,b,c以,分融成行,即上个示例的反操作*/SELECT DISTINCT cid,dbo.myJoinSTR(cid) nameS INTO tx FROM taGOSELECT * FROM txGO--以系统表构建identity列,并以连表方式来将列拆成行SELECT IDENTITY(INT,1,1) id INTO # FROM syscolumns,sysobjectsSELECT id,cid,RIGHT(STUFF(nameS+',',id,LEN(names),''),CHARINDEX(',',REVERSE(STUFF(','+nameS+',',id,LEN(names),'')))) name FROM tx aINNER 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 cidSELECT @s='SELECT ' + STUFF(@s,1,1,'') + ' FROM ta'--你可以在这里PRINT @s 看看，就知道跟上面的静态行转列一样的了。 会写静态行转列，就没理由写不出动态的。多表的同理，把多表的静态行转列写出来，那么动态的也就出来了EXEC(@s)GODROP TABLE tx,#GODROP TABLE ta,tbDROP FUNCTION myJoinSTRGO