表1:
编号 类别 数量
001 A 102
001 C 114
002 A 12
002 B 39
002 D 20
表2:
ID 类别
1 A
2 B
3 C
4 D
5 E我想根据表1表2 得到下列结果(即若表2中的“类别”在表1中没有则“数量”为0): 编号 类别 数量
001 A 102
001 B 0
001 C 114
001 D 0
001 E 0
002 A 12
002 B 39
002 C 0
002 D 20
002 E 0
请问该如何实现?谢谢!
编号 类别 数量
001 A 102
001 C 114
002 A 12
002 B 39
002 D 20
表2:
ID 类别
1 A
2 B
3 C
4 D
5 E我想根据表1表2 得到下列结果(即若表2中的“类别”在表1中没有则“数量”为0): 编号 类别 数量
001 A 102
001 B 0
001 C 114
001 D 0
001 E 0
002 A 12
002 B 39
002 C 0
002 D 20
002 E 0
请问该如何实现?谢谢!
from B left join A on B.类别=A.类别
FROM (
SELECT 类别 FROM tb2 CROSS JOIN SELECT DISTINCT 编号 FROM tb2
) AS A
LEFT JOIN tb1 AS B
ON A.编号=B.编号 AND A.类别=B.类别
from B left join A on B.类别=A.类别 and B.编号=A.编号
FROM TB1 A FULL JOIN TB2 B ON A.类别=B.类别??
t1.编号,t2.类别,isnull(t3.数量,0) as 数量
from (
select distinct 编号 from 表1) as t1
cross join 表2 t2
left join 表1 t3
on t1.编号=t3.编号 and t3.类别=t2.类别
from a,b
where
a.类别=b.类别
-- Author : liangCK 小梁
-- Comment: 小梁 爱 兰儿
-- Date : 2009-07-17 14:46:50
-------------------------------------------
--> 生成测试数据: @tb1
DECLARE @tb1 TABLE (编号 VARCHAR(3),类别 VARCHAR(1),数量 INT)
INSERT INTO @tb1
SELECT '001','A',102 UNION ALL
SELECT '001','C',114 UNION ALL
SELECT '002','A',12 UNION ALL
SELECT '002','B',39 UNION ALL
SELECT '002','D',20
--> 生成测试数据: @tb2
DECLARE @tb2 TABLE (ID INT,类别 VARCHAR(1))
INSERT INTO @tb2
SELECT 1,'A' UNION ALL
SELECT 2,'B' UNION ALL
SELECT 3,'C' UNION ALL
SELECT 4,'D' UNION ALL
SELECT 5,'E'--SQL查询如下:SELECT A.编号,A.类别,ISNULL(B.数量,0) AS 数量
FROM (
SELECT * FROM @tb2
CROSS JOIN (SELECT DISTINCT 编号 FROM @tb1) AS B
) AS A
LEFT JOIN @tb1 AS B
ON A.编号=B.编号 AND A.类别=B.类别/*
编号 类别 数量
---- ---- -----------
001 A 102
002 A 12
001 B 0
002 B 39
001 C 114
002 C 0
001 D 0
002 D 20
001 E 0
002 E 0(10 row(s) affected)
*/
from a,b
where
a.类别=b.类别 and a.编号=b.编号
INSERT @TA
SELECT '001', 'A', 102 UNION ALL
SELECT '001', 'C', 114 UNION ALL
SELECT '002', 'A', 12 UNION ALL
SELECT '002', 'B', 39 UNION ALL
SELECT '002', 'D', 20DECLARE @TB TABLE([ID] INT, [类别] VARCHAR(1))
INSERT @TB
SELECT 1, 'A' UNION ALL
SELECT 2, 'B' UNION ALL
SELECT 3, 'C' UNION ALL
SELECT 4, 'D' UNION ALL
SELECT 5, 'E'SELECT A.[编号],B.[类别],ISNULL(C.[数量],0) AS [数量]
FROM (SELECT DISTINCT [编号] FROM @TA) AS A CROSS JOIN @TB AS B
LEFT JOIN @TA AS C
ON A.[编号]=C.[编号] AND B.[类别]=C.[类别]
/*
编号 类别 数量
---- ---- -----------
001 A 102
001 B 0
001 C 114
001 D 0
001 E 0
002 A 12
002 B 39
002 C 0
002 D 20
002 E 0(10 行受影响)
*/
from (select * from b
cross join (select distinct 编号 from a)b
) a
where
a.类别=b.类别 and a.编号=b.编号
INSERT TB1
SELECT '001' , 'A' , 102 UNION
SELECT '001' , 'C' , 114 UNION
SELECT '002' , 'A' , 12 UNION
SELECT '002' , 'B' , 39 UNION
SELECT '002' , 'D' , 20 CREATE TABLE TB2(ID INT, 类别 VARCHAR(10))
INSERT TB2
SELECT 1 , 'A' UNION
SELECT 2 , 'B' UNION
SELECT 3 , 'C' UNION
SELECT 4 , 'D' UNION
SELECT 5 , 'E'--DROP TABLE TB1,TB2SELECT B.编号,
ISNULL(A.类别,B.类别)AS 类别,
ISNULL(A.数量 ,0)数量
FROM TB1 A
RIGHT JOIN (SELECT DISTINCT 编号,TB2.类别 FROM TB1 CROSS JOIN TB2)AS B ON A.类别=B.类别 AND A.编号=B.编号编号 类别 数量
---------- ---------- -----------
001 A 102
001 B 0
001 C 114
001 D 0
001 E 0
002 A 12
002 B 39
002 C 0
002 D 20
002 E 0(所影响的行数为 10 行)