解决行转列,列分行,行合并列的疑难问题

SQL code/*=============
===fcuandy=====
===2008.1.10===
=============*/

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条或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.其它的组合写法省略 
数据挖掘交友
 

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默认转换为内连),
采用哪种方式依具你的业务需求。



--------------------------------------------
/* 
数据挖掘实验室