---------------------------------
-- Author: liangCK 小梁
-- Date : 2008-12-03 15:43:05
---------------------------------
--> 生成测试数据: @tb1
DECLARE @tb1 TABLE (a_id INT,a_name VARCHAR(2),a_desc VARCHAR(8))
INSERT INTO @tb1
SELECT 1,'a1','a1的描述' UNION ALL
SELECT 2,'a2','a2的描述'
--> 生成测试数据: @tb2
DECLARE @tb2 TABLE (b_id INT,a_id INT,b_name VARCHAR(4),b_desc VARCHAR(10))
INSERT INTO @tb2
SELECT 1,1,'a1b1','a1b1的描述' UNION ALL
SELECT 2,1,'a1b2','a1b2的描述' UNION ALL
SELECT 3,1,'a1b3','a1b3的描述' UNION ALL
SELECT 4,2,'a2b1','a2b1的描述' UNION ALL
SELECT 5,2,'a2b2','a2b2的描述'
--> 生成测试数据: @tb3
DECLARE @tb3 TABLE (c_id INT,b_id INT,c_name VARCHAR(6),c_desc VARCHAR(12))
INSERT INTO @tb3
SELECT 1,1,'a1b1c1','a1b1c1的描述' UNION ALL
SELECT 2,1,'a1b1c2','a1b1c2的描述' UNION ALL
SELECT 3,1,'a1b1c3','a1b1c3的描述' UNION ALL
SELECT 4,2,'a1b2c1','a1b2c1的描述' UNION ALL
SELECT 5,2,'a1b2c2','a1b2c2的描述' UNION ALL
SELECT 6,3,'a1b3c1','a1b3c1的描述'
--> 生成测试数据: @tb4
DECLARE @tb4 TABLE (d_id INT,a_name VARCHAR(2),b_name VARCHAR(2),c_name VARCHAR(2),d_f1 VARCHAR(2),d_f2 VARCHAR(2))
INSERT INTO @tb4
SELECT 1,'a1','b1','c1','xx','xx' UNION ALL
SELECT 2,'a1','b1','c2','xx','xx' UNION ALL
SELECT 3,'a1','b2','c1','xx','xx' UNION ALL
SELECT 4,'a1','b2',null,'xx','xx'--SQL查询如下:SELECT
A.d_id,
B.a_desc,
C.b_desc,
D.c_desc,
A.d_f1,
A.d_f2
FROM @tb4 AS A
LEFT JOIN
@tb1 AS B
ON A.a_name=B.a_name
LEFT JOIN
@tb2 AS C
ON C.b_name LIKE A.a_name+'%'
AND C.b_name LIKE '%'+A.b_name
LEFT JOIN
@tb3 AS D
ON D.c_name LIKE A.a_name+'%'
AND D.c_name LIKE '%'+A.b_name+'%'
AND D.c_name LIKE '%'+A.c_name/*
d_id a_desc b_desc c_desc d_f1 d_f2
----------- -------- ---------- ------------ ---- ----
1 a1的描述 a1b1的描述 a1b1c1的描述 xx xx
2 a1的描述 a1b1的描述 a1b1c2的描述 xx xx
3 a1的描述 a1b2的描述 a1b2c1的描述 xx xx
4 a1的描述 a1b2的描述 NULL xx xx(4 行受影响)
*/
-- Author: liangCK 小梁
-- Date : 2008-12-03 15:43:05
---------------------------------
--> 生成测试数据: @tb1
DECLARE @tb1 TABLE (a_id INT,a_name VARCHAR(2),a_desc VARCHAR(8))
INSERT INTO @tb1
SELECT 1,'a1','a1的描述' UNION ALL
SELECT 2,'a2','a2的描述'
--> 生成测试数据: @tb2
DECLARE @tb2 TABLE (b_id INT,a_id INT,b_name VARCHAR(4),b_desc VARCHAR(10))
INSERT INTO @tb2
SELECT 1,1,'a1b1','a1b1的描述' UNION ALL
SELECT 2,1,'a1b2','a1b2的描述' UNION ALL
SELECT 3,1,'a1b3','a1b3的描述' UNION ALL
SELECT 4,2,'a2b1','a2b1的描述' UNION ALL
SELECT 5,2,'a2b2','a2b2的描述'
--> 生成测试数据: @tb3
DECLARE @tb3 TABLE (c_id INT,b_id INT,c_name VARCHAR(6),c_desc VARCHAR(12))
INSERT INTO @tb3
SELECT 1,1,'a1b1c1','a1b1c1的描述' UNION ALL
SELECT 2,1,'a1b1c2','a1b1c2的描述' UNION ALL
SELECT 3,1,'a1b1c3','a1b1c3的描述' UNION ALL
SELECT 4,2,'a1b2c1','a1b2c1的描述' UNION ALL
SELECT 5,2,'a1b2c2','a1b2c2的描述' UNION ALL
SELECT 6,3,'a1b3c1','a1b3c1的描述'
--> 生成测试数据: @tb4
DECLARE @tb4 TABLE (d_id INT,a_name VARCHAR(2),b_name VARCHAR(2),c_name VARCHAR(2),d_f1 VARCHAR(2),d_f2 VARCHAR(2))
INSERT INTO @tb4
SELECT 1,'a1','b1','c1','xx','xx' UNION ALL
SELECT 2,'a1','b1','c2','xx','xx' UNION ALL
SELECT 3,'a1','b2','c1','xx','xx' UNION ALL
SELECT 4,'a1','b2',null,'xx','xx'--SQL查询如下:SELECT
A.d_id,
B.a_desc,
C.b_desc,
D.c_desc,
A.d_f1,
A.d_f2
FROM @tb4 AS A
LEFT JOIN
@tb1 AS B
ON A.a_name=B.a_name
LEFT JOIN
@tb2 AS C
ON C.b_name LIKE A.a_name+'%'
AND C.b_name LIKE '%'+A.b_name
LEFT JOIN
@tb3 AS D
ON D.c_name LIKE A.a_name+'%'
AND D.c_name LIKE '%'+A.b_name+'%'
AND D.c_name LIKE '%'+A.c_name/*
d_id a_desc b_desc c_desc d_f1 d_f2
----------- -------- ---------- ------------ ---- ----
1 a1的描述 a1b1的描述 a1b1c1的描述 xx xx
2 a1的描述 a1b1的描述 a1b1c2的描述 xx xx
3 a1的描述 a1b2的描述 a1b2c1的描述 xx xx
4 a1的描述 a1b2的描述 NULL xx xx(4 行受影响)
*/
A.d_id,
B.a_desc,
C.b_desc,
D.c_desc,
A.d_f1,
A.d_f2
FROM @tb4 AS A
LEFT JOIN
@tb1 AS B
ON A.a_name=B.a_name
LEFT JOIN
@tb2 AS C
ON C.b_name LIKE A.a_name+'%'
AND C.b_name LIKE '%'+A.b_name
AND B.a_id=C.a_id
LEFT JOIN
@tb3 AS D
ON D.c_name LIKE A.a_name+'%'
AND D.c_name LIKE '%'+A.b_name+'%'
AND D.c_name LIKE '%'+A.c_name
AND C.b_id=D.b_id/*
d_id a_desc b_desc c_desc d_f1 d_f2
----------- -------- ---------- ------------ ---- ----
1 a1的描述 a1b1的描述 a1b1c1的描述 xx xx
2 a1的描述 a1b1的描述 a1b1c2的描述 xx xx
3 a1的描述 a1b2的描述 a1b2c1的描述 xx xx
4 a1的描述 a1b2的描述 NULL xx xx(4 行受影响)
*/