*
=============
===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
===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