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