第一次问问题，给点勇气

[sunnyyin]

已知：
Table1:
TypeID,ProductID,Total
R11 D0002 0
R11 D0004 100
R11 D0035 100
R12 D0002 100
R12 D0004 50
R12 D0005 10

Table2:
TypeID
R11
R12
R13

求Table3
TypeID,ProductID,Total
R11 D0002 0
R12 D0002 100
R13 D0002 0
R11 D0004 100
R12 D0004 50
R13 D0004 0
R11 D0035 100
R12 D0035 0
R13 D0035 0
R11 D0005 10
R12 D0005 0
R13 D0005 0

[zjcxc]

解释一下,没看懂

[victorycyz]

select a.typeid,b.productid,(case when b.total is null then 0 else b.total) as total
from table2 a left join table1 b on a.typeid=b.typeid

[txlicenhe]

--测试：
create table t1(TypeID char(3),ProductID char(5),Total int)
insert t1 select 'R11','D0002',0
union all select 'R11','D0004',100
union all select 'R11','D0035',100
union all select 'R12','D0002',100
union all select 'R12','D0004',50
union all select 'R12','D0005',10

create table t2(TypeID char(3))
insert t2 values('R11')
insert t2 values('R12')
insert t2 values('R13')

select aa.TypeID,aa.ProductID,IsNull(b.Total,0) as Total
from
(
select * from
(select distinct(productID) as productid from t1) a,t2
) aa
left join t1 b on aa.typeid = b.typeid and aa.productid = b.productid
order by aa.productid,aa.typeid

TypeID ProductID Total
------ --------- -----------
R11 D0002 0
R12 D0002 100
R13 D0002 0
R11 D0004 100
R12 D0004 50
R13 D0004 0
R11 D0005 0
R12 D0005 10
R13 D0005 0
R11 D0035 100
R12 D0035 0
R13 D0035 0

（所影响的行数为 12 行）