这是我在fcuandy专家的空间看到的:
CREATE TABLE ta(ID INT,RID VARCHAR(10),FID VARCHAR(10))
INSERT Ta SELECT 1, 'R123', '123'
UNION ALL SELECT 2, 'R0' , '456'
UNION ALL SELECT 3, 'R11' , '789'
CREATE TABLE tb(ID INT,FID VARCHAR(10))
INSERT Tb SELECT 1,12
UNION ALL SELECT 2,123
UNION ALL SELECT 3,789
CREATE TABLE tc(ID INT,PID VARCHAR(100))
INSERT Tc SELECT 1, 'R11'
UNION ALL SELECT 2, '123,R0,33'
UNION ALL SELECT 3, 'R0,12'SELECT c.*,COUNT(*) CNT FROM tc c
INNER JOIN
(SELECT RID FROM Ta
UNION
SELECT FID FROM tb) x
ON CHARINDEX(','+x.RID+',',','+c.PID+',')>0
GROUP BY ID,PID
HAVING COUNT(*)<>LEN(REPLACE(PID,',',',,'))-LEN(PID)+1DROP TABLE ta,tb,tc
麻烦大家帮解释一下各语句用意及思路
CREATE TABLE ta(ID INT,RID VARCHAR(10),FID VARCHAR(10))
INSERT Ta SELECT 1, 'R123', '123'
UNION ALL SELECT 2, 'R0' , '456'
UNION ALL SELECT 3, 'R11' , '789'
CREATE TABLE tb(ID INT,FID VARCHAR(10))
INSERT Tb SELECT 1,12
UNION ALL SELECT 2,123
UNION ALL SELECT 3,789
CREATE TABLE tc(ID INT,PID VARCHAR(100))
INSERT Tc SELECT 1, 'R11'
UNION ALL SELECT 2, '123,R0,33'
UNION ALL SELECT 3, 'R0,12'SELECT c.*,COUNT(*) CNT FROM tc c
INNER JOIN
(SELECT RID FROM Ta
UNION
SELECT FID FROM tb) x
ON CHARINDEX(','+x.RID+',',','+c.PID+',')>0
GROUP BY ID,PID
HAVING COUNT(*)<>LEN(REPLACE(PID,',',',,'))-LEN(PID)+1DROP TABLE ta,tb,tc
麻烦大家帮解释一下各语句用意及思路
INNER JOIN
(SELECT RID FROM Ta
UNION
SELECT FID FROM tb) x --建立虚表x
ON CHARINDEX(','+x.RID+',',','+c.PID+',')>0
GROUP BY ID,PID
HAVING COUNT(*) <>LEN(REPLACE(PID,',',',,'))-LEN(PID)+1 --having用法...
--这个应该怎么解释,还是说说你那里不明白吧
[/code]
CHARINDEX
len
等函数的用法
CREATE TABLE ta(ID INT,RID VARCHAR(10),FID VARCHAR(10)) //建表
INSERT Ta SELECT 1, 'R123', '123'//插入数据
UNION ALL SELECT 2, 'R0' , '456'
UNION ALL SELECT 3, 'R11' , '789'
CREATE TABLE tb(ID INT,FID VARCHAR(10))
INSERT Tb SELECT 1,12
UNION ALL SELECT 2,123
UNION ALL SELECT 3,789
CREATE TABLE tc(ID INT,PID VARCHAR(100))
INSERT Tc SELECT 1, 'R11'
UNION ALL SELECT 2, '123,R0,33'
UNION ALL SELECT 3, 'R0,12'
--内联/联合查询
SELECT c.*,COUNT(*) CNT FROM tc c
INNER JOIN
(SELECT RID FROM Ta
UNION
SELECT FID FROM tb) x
ON CHARINDEX(','+x.RID+',',','+c.PID+',')>0
GROUP BY ID,PID
HAVING COUNT(*) <>LEN(REPLACE(PID,',',',,'))-LEN(PID)+1DROP TABLE ta,tb,tc