sql,三表连接查询,跪求
三个表
t1商品表
id size num date
1 l 12 2007-9-1
2 m 12 2007-9-1
t2 商品出货明晰表
id shangpin num date
1 1 12 2007-9-19
2 1 12 2007-9-20
3 1 2 2007-9-20
t3 商品入库明晰表
id shangpin num date
1 1 20 2007-9-20
2 1 20 2007-9-20
如何通过sql语句查询到
2007-9-20这一天1商品出货,和入库多少
也就是得出以下结果
id 商品 出货 入库 时间
1 1 14(12+2) 40(20+20) 2007-9-20
2 2 0 0 2007-9-20
谢谢,跪求sql语句
[解决办法]
create table t1(id int, size varchar(10), num int, date datetime)
insert into t1 values(1, 'l ', 12, '2007-9-1 ')
insert into t1 values(2, 'm ', 12, '2007-9-1 ')
create table t2(id int, shangpin int, num int, date datetime)
insert into t2 values(1, 1, 12, '2007-9-19 ')
insert into t2 values(2, 1, 12, '2007-9-20 ')
insert into t2 values(3, 1, 2, '2007-9-20 ')
create table t3(id int, shangpin int, num int, date datetime)
insert into t3 values(1, 1, 20, '2007-9-20 ')
insert into t3 values(2, 1, 20, '2007-9-20 ')
go
select t1.id,isnull(m.num,0) 出货,isnull(n.num,0) 入库 , 时间 = '2007-09-20 ' from t1
left join (select shangpin,sum(num) num from t2 where date = '2007-09-20 ' group by shangpin) m on t1.id = m.shangpin
left join (select shangpin,sum(num) num from t3 where date = '2007-09-20 ' group by shangpin) n on t1.id = n.shangpin
drop table t1,t2,t3
/*
id 出货 入库 时间
----------- ----------- ----------- ----------
1 14 40 2007-09-20
2 0 0 2007-09-20
（所影响的行数为 2 行）
*/
[解决办法]
--借用楼上数据
create table t1(id int, size varchar(10), num int, date datetime)
insert into t1 values(1, 'l ', 12, '2007-9-1 ')
insert into t1 values(2, 'm ', 12, '2007-9-1 ')
create table t2(id int, shangpin int, num int, date datetime)
insert into t2 values(1, 1, 12, '2007-9-19 ')
insert into t2 values(2, 1, 12, '2007-9-20 ')
insert into t2 values(3, 1, 2, '2007-9-20 ')
create table t3(id int, shangpin int, num int, date datetime)
insert into t3 values(1, 1, 20, '2007-9-20 ')
insert into t3 values(2, 1, 20, '2007-9-20 ')
go
declare @dt datetime
set @dt= '2007-09-20 '
select
t1.id as 商品,
isnull(sum(t.出货),0) as 出货,
isnull(sum(t.入库),0) as 入库,
@dt as 时间
from
t1 left join
(
select id, shangpin,num as 出货, null as 入库, [date] as 时间 from t2 where [date]=@dt
union all
select id, shangpin,null as 出货, num as 入库, [date] as 时间 from t3 where [date]=@dt
) as t on t1.id=t.shangpin
group by t1.id
order by t1.id
drop table T1,t2,t3
[解决办法]
--商品这个字段是乎没有必要?
create table t1(id int, size varchar(10), num int, date datetime)
insert into t1 values(1, 'l ', 12, '2007-9-1 ')
insert into t1 values(2, 'm ', 12, '2007-9-1 ')
create table t2(id int, shangpin int, num int, date datetime)
insert into t2 values(1, 1, 12, '2007-9-19 ')
insert into t2 values(2, 1, 12, '2007-9-20 ')
insert into t2 values(3, 1, 2, '2007-9-20 ')
create table t3(id int, shangpin int, num int, date datetime)
insert into t3 values(1, 1, 20, '2007-9-20 ')
insert into t3 values(2, 1, 20, '2007-9-20 ')
go
select t1.id, 商品 = t1.id,isnull(m.num,0) 出货,isnull(n.num,0) 入库 , 时间 = '2007-09-20 ' from t1
left join (select shangpin,sum(num) num from t2 where date = '2007-09-20 ' group by shangpin) m on t1.id = m.shangpin
left join (select shangpin,sum(num) num from t3 where date = '2007-09-20 ' group by shangpin) n on t1.id = n.shangpin
drop table t1,t2,t3
/*
id 商品 出货 入库 时间
----------- ----------- ----------- ----------- ----------
1 1 14 40 2007-09-20
2 2 0 0 2007-09-20
（所影响的行数为 2 行）
*/
[解决办法]
--商品表：@t1
declare @t1 table(id int,size varchar(1),num int,date datetime)
insert @t1
select 1, 'l ',12, '2007-9-1 ' union all
select 2, 'm ',12, '2007-9-1 '
--商品出货明晰表：@t2
declare @t2 table(id int,shangpin int,num int,date datetime)
insert @t2
select 1,1,12, '2007-9-19 ' union all
select 2,1,12, '2007-9-20 ' union all
select 3,1,2, '2007-9-20 '
--商品入库明晰表：@t3
declare @t3 table(id int,shangpin int,num int,date datetime)
insert @t3
select 1,1,20, '2007-9-20 ' union all
select 2,1,20, '2007-9-20 '
declare @Date datetime
set @Date = '2007-9-20 '
select
商品=a.id,
出货=isnull(b.num,0),
入库=isnull(c.num,0),
时间=@Date
from @t1 a
left join (select shangpin,num=sum(num) from @t2 where date=@Date group by shangpin) b
on a.id=b.shangpin
left join (select shangpin,num=sum(num) from @t3 where date=@Date group by shangpin) c
on b.shangpin=c.shangpin
/*
商品出货入库时间
114402007-09-20 00:00:00.000
2002007-09-20 00:00:00.000
*/
[解决办法]
--商品表：@t1
declare @t1 table(id int,size varchar(1),num int,date datetime)
insert @t1
select 1, 'l ',12, '2007-9-1 ' union all
select 2, 'm ',12, '2007-9-1 '
--商品出货明晰表：@t2
declare @t2 table(id int,shangpin int,num int,date datetime)
insert @t2
select 1,1,12, '2007-9-19 ' union all
select 2,1,12, '2007-9-20 ' union all
select 3,1,2, '2007-9-20 '
--商品入库明晰表：@t3
declare @t3 table(id int,shangpin int,num int,date datetime)
insert @t3
select 1,1,20, '2007-9-20 ' union all
select 2,1,20, '2007-9-20 '
--这个是按日统计全部的
select
商品=a.id,
出货=isnull(b.num,0),
入库=isnull(c.num,0),
时间=isnull(b.date,c.date)
from @t1 a
full join (select shangpin,num=sum(num),date=convert(varchar(10),date,120) from @t2 group by shangpin,convert(varchar(10),date,120)) b
on a.id=b.shangpin
full join (select shangpin,num=sum(num),date=convert(varchar(10),date,120) from @t3 group by shangpin,convert(varchar(10),date,120)) c
on b.shangpin=c.shangpin and b.date=c.date
/*
商品出货入库时间
11202007-09-19
114402007-09-20
200NULL
*/
[解决办法]
declare @a table(id int identity(1,1),size varchar(20),num int,date datetime)
insert @a
select 'l ',12, '2007-9-1 '
union all
select 'm ',12, '2007-9-1 '
declare @b table(id int identity(1,1),shangpin int,num int,date datetime)
insert @b
select 1 , 12 , '2007-9-19 '
union all
select 1 , 12 , '2007-9-20 '
union all
select 1 , 2 , '2007-9-20 '
declare @c table(id int identity(1,1),shangpin int,num int,date datetime)
insert @c
select 1 , 20 , '2007-9-20 '
union all
select 1 , 20 , '2007-9-20 '
select * from @a a left join (
select sum(num) as num1,shangpin from @b where date= '2007-9-20 ' group by shangpin) b on a.id=b.shangpin
left join
(select sum(num) as num2,shangpin from @c where date= '2007-9-20 ' group by shangpin) c on a.id=c.shangpin
/*
（所影响的行数为 2 行）
（所影响的行数为 3 行）
（所影响的行数为 2 行）
id size num date num1 shangpin num2 shangpin
----------- -------------------- ----------- ------------------------------------------------------ ----------- ----------- ----------- -----------
1 l 12 2007-09-01 00:00:00.000 14 1 40 1
2 m 12 2007-09-01 00:00:00.000 NULL NULL NULL NULL
（所影响的行数为 2 行）
*/
[解决办法]
create table t1(id int, size varchar(10), num int, date datetime)
insert into t1 values(1, 'l ', 12, '2007-9-1 ')
insert into t1 values(2, 'm ', 12, '2007-9-1 ')
create table t2(id int, shangpin int, num int, date datetime)
insert into t2 values(1, 1, 12, '2007-9-19 ')
insert into t2 values(2, 1, 12, '2007-9-20 ')
insert into t2 values(3, 1, 2, '2007-9-20 ')
create table t3(id int, shangpin int, num int, date datetime)
insert into t3 values(1, 1, 20, '2007-9-20 ')
insert into t3 values(2, 1, 20, '2007-9-20 ')
go
select * from T1
select a.id, a.id as 商品, isnull(出货,0) as 出货,isnull(入货,0) as 入货, isnull(b.date, '2007-9-20 ') date from t1 a
left join (
select shangpin,sum(num) as 出货,date from T2
where date= '2007-9-20 '
group by shangpin ,date
) b on a.id=b.shangpin
left join
(
select shangpin,sum(num) as 入货 from T3
where date= '2007-9-20 '
group by shangpin
) c on a.id=c.shangpin