表sell
order_no date b_no
01965329 2010-12-06 212
01965330 2010-12-07 211
01965331 2010-12-07 211
01965332 2010-12-07 211
01965333 2010-12-07 211
01965334 2010-12-09 211
表sell_per
order_no code sl je
01965329 112259 20 200
01965330 112693 11 600
01965331 112693 15 700
01965332 112884 6 255
01965333 112884 7 400
01965334 112885 7 400
表comd name
112259 材料1
112563 材料2
112693 材料3
112884 材料4
112885 材料5
112886 材料6 如何得到以下结果 查询b_no=2010-12-07,comd为112693,112884,112886的2010-12-07销售,有的销售显示金额,没有的为0
comd name sl je date
112693 材料3 26 1300 2010-12-07
112884 材料4 13 655 2010-12-07
112886 材料6 0 0 2010-12-07刚刚请教过答案为,有大侠给出以下代码,可我sum(isnull(sl,0)),sum(isnull(je,0))的时候总是把其它的数据也累加了,不知道什么原因,望大侠赐教正解
order_no date b_no
01965329 2010-12-06 212
01965330 2010-12-07 211
01965331 2010-12-07 211
01965332 2010-12-07 211
01965333 2010-12-07 211
01965334 2010-12-09 211
表sell_per
order_no code sl je
01965329 112259 20 200
01965330 112693 11 600
01965331 112693 15 700
01965332 112884 6 255
01965333 112884 7 400
01965334 112885 7 400
表comd name
112259 材料1
112563 材料2
112693 材料3
112884 材料4
112885 材料5
112886 材料6 如何得到以下结果 查询b_no=2010-12-07,comd为112693,112884,112886的2010-12-07销售,有的销售显示金额,没有的为0
comd name sl je date
112693 材料3 26 1300 2010-12-07
112884 材料4 13 655 2010-12-07
112886 材料6 0 0 2010-12-07刚刚请教过答案为,有大侠给出以下代码,可我sum(isnull(sl,0)),sum(isnull(je,0))的时候总是把其它的数据也累加了,不知道什么原因,望大侠赐教正解
如何得到以下结果 查询再加一句b_no =211,b_no=2010-12-07,comd为112693,112884,112886的2010-12-07销售,有的销售显示金额,没有的为0
drop table ta
Go
Create table ta([order_no] nvarchar(8),[date] Datetime,[b_no] int)
Insert ta
select N'01965329','2010-12-06',212 union all
select N'01965330','2010-12-07',211 union all
select N'01965331','2010-12-07',211 union all
select N'01965332','2010-12-07',211 union all
select N'01965333','2010-12-07',211 union all
select N'01965334','2010-12-09',211
Go
if not object_id('tb') is null
drop table tb
Go
Create table tb([order_no] nvarchar(8),[code] int,[sl] int,[je] int)
Insert tb
select N'01965329',112259,20,200 union all
select N'01965330',112693,11,600 union all
select N'01965331',112693,15,700 union all
select N'01965332',112884,6,255 union all
select N'01965333',112884,7,400 union all
select N'01965334',112885,7,400
Go
if not object_id('tc') is null
drop table tc
Go
Create table tc([comd] int,[name] nvarchar(3))
Insert tc
select 112259,N'材料1' union all
select 112563,N'材料2' union all
select 112693,N'材料3' union all
select 112884,N'材料4' union all
select 112885,N'材料5' union all
select 112886,N'材料6'
Go
select c.*,
isnull(b.sl,0),
isnull(b.je,0),
b.[date]
from tc c left join (select [date],
code,
sum([sl])[sl],
sum([je])[je]
from tb,ta
where ta.[order_no]=tb.[order_no]
and [date] ='2010-12-07'
group by [date],code )b on c.[comd]=b.[code]
where c.[comd] in(112693,112884,112886)
/*
comd name date
----------- ---- ----------- ----------- -----------------------
112693 材料3 26 1300 2010-12-07 00:00:00.000
112884 材料4 13 655 2010-12-07 00:00:00.000
112886 材料6 0 0 NULL(3 row(s) affected)
*/
drop table ta
Go
Create table ta([order_no] nvarchar(8),[date] Datetime,[b_no] int)
Insert ta
select N'01965329','2010-12-06',212 union all
select N'01965330','2010-12-07',211 union all
select N'01965331','2010-12-07',211 union all
select N'01965332','2010-12-07',211 union all
select N'01965333','2010-12-07',211 union all
select N'01965334','2010-12-09',211
Go
if not object_id('tb') is null
drop table tb
Go
Create table tb([order_no] nvarchar(8),[code] int,[sl] int,[je] int)
Insert tb
select N'01965329',112259,20,200 union all
select N'01965330',112693,11,600 union all
select N'01965331',112693,15,700 union all
select N'01965332',112884,6,255 union all
select N'01965333',112884,7,400 union all
select N'01965334',112885,7,400
Go
if not object_id('tc') is null
drop table tc
Go
Create table tc([comd] int,[name] nvarchar(3))
Insert tc
select 112259,N'材料1' union all
select 112563,N'材料2' union all
select 112693,N'材料3' union all
select 112884,N'材料4' union all
select 112885,N'材料5' union all
select 112886,N'材料6'
Go
select c.*,
isnull(b.sl,0),
isnull(b.je,0),
b.[date]
from tc c left join (select [date],
code,
sum([sl])[sl],
sum([je])[je]
from tb,ta
where ta.[order_no]=tb.[order_no]
and [date] ='2010-12-07'
group by [date],code )b on c.[comd]=b.[code]
where c.[comd] in(112693,112884,112886)
/*
comd name date
----------- ---- ----------- ----------- -----------------------
112693 材料3 26 1300 2010-12-07 00:00:00.000
112884 材料4 13 655 2010-12-07 00:00:00.000
112886 材料6 0 0 NULL(3 row(s) affected)
*/
楼上的大哥,用left join可以得到以上结果吗??就是left join两次可以吗
'01965331','2010-12-07',211
'01965330',112693,11,600
'01965331',112693,15,700 Create table ta([order_no] nvarchar(8),[date] Datetime,[b_no] int)
Insert ta
select N'01965329','2010-12-06',212 union all
select N'01965330','2010-12-07',211 union all
select N'01965331','2010-12-07',211 union all
select N'01965332','2010-12-07',211 union all
select N'01965333','2010-12-07',211 union all
select N'01965334','2010-12-09',211
Create table tb([order_no] nvarchar(8),[code] int,[sl] int,[je] int)
Insert tb
select N'01965329',112259,20,200 union all
select N'01965330',112693,11,600 union all
select N'01965331',112693,15,700 union all
select N'01965332',112884,6,255 union all
select N'01965333',112884,7,400 union all
select N'01965334',112885,7,400
Create table tc([comd] int,[name] nvarchar(3))
Insert tc
select 112259,N'材料1' union all
select 112563,N'材料2' union all
select 112693,N'材料3' union all
select 112884,N'材料4' union all
select 112885,N'材料5' union all
select 112886,N'材料6'select c.comd ,c.name ,
sl = isnull((select sum(sl) from tb b , ta a where b.code = c.comd and b.order_no = a.order_no and a.date = '2010-12-07' and a.b_no =211),0),
je = isnull((select sum(je) from tb b , ta a where b.code = c.comd and b.order_no = a.order_no and a.date = '2010-12-07' and a.b_no =211),0)
from tc c
where c.comd in (112693,112884,112886)drop table ta , tb , tc/*
comd name sl je
----------- ---- ----------- -----------
112693 材料3 26 1300
112884 材料4 13 655
112886 材料6 0 0(所影响的行数为 3 行)
*/