现在表table1编码 销售合计数量
1001 0
1002 0
1003 0
1004 0
1005 0表table2
编码 销售数量 销售时间
1001 5 2009-8-12
1002 3 2009-8-12
1001 7 2009-8-13
1003 1 2009-8-13
1004 2 2009-8-13
1001 1 2009-8-13
1002 3 2009-8-14要求在表table1 销售合计数量 即为如下
1001 13
1002 6
1003 1
1004 2
1005 0请高手们给一SQL语句
1001 0
1002 0
1003 0
1004 0
1005 0表table2
编码 销售数量 销售时间
1001 5 2009-8-12
1002 3 2009-8-12
1001 7 2009-8-13
1003 1 2009-8-13
1004 2 2009-8-13
1001 1 2009-8-13
1002 3 2009-8-14要求在表table1 销售合计数量 即为如下
1001 13
1002 6
1003 1
1004 2
1005 0请高手们给一SQL语句
from 表table2 group by 编码
编码,
sum(销售数量) as 销售合计数量
from
table2
group by
编码
Drop table [table1]
Go
Create table [table1]([id] int,[saletotal] int)
Insert table1select 1001, 0 union all
select 1002, 0 union all
select 1003, 0 union all
select 1004, 0 union all
select 1005, 0 If not object_id('[table2]') is null
Drop table [table2]
Go
Create table [table2]([id] int,[salenum] int,[saledate] datetime)
Insert table2select 1001, 5, '2009-8-10' union all
select 1002, 4, '2009-8-10' union all
select 1001, 3, '2009-8-10' union all
select 1003, 4, '2009-8-10' union all
select 1001, 1, '2009-8-11' union all
select 1002, 2, '2009-8-11' union all
select 1004, 6, '2009-8-11' union all
select 1001, 2, '2009-8-11'update table1
set saletotal=(select sum(salenum) from table2 where table2.id=table1.id)
select * from table1
Drop table [table1]
Go
Create table [table1]([id] int,[saletotal] int)
Insert table1select 1001, 0 union all
select 1002, 0 union all
select 1003, 0 union all
select 1004, 0 union all
select 1005, 0 If not object_id('[table2]') is null
Drop table [table2]
Go
Create table [table2]([id] int,[salenum] int,[saledate] datetime)
Insert table2select 1001, 5, '2009-8-10' union all
select 1002, 4, '2009-8-10' union all
select 1001, 3, '2009-8-10' union all
select 1003, 4, '2009-8-10' union all
select 1001, 1, '2009-8-11' union all
select 1002, 2, '2009-8-11' union all
select 1004, 6, '2009-8-11' union all
select 1001, 2, '2009-8-11'update table1
set saletotal=(select sum(salenum) from table2 where table2.id=table1.id)
select * from table1
Drop table [table1]
Go
Create table [table1]([id] int,[saletotal] int)
Insert table1select 1001, 0 union all
select 1002, 0 union all
select 1003, 0 union all
select 1004, 0 union all
select 1005, 0 If not object_id('[table2]') is null
Drop table [table2]
Go
Create table [table2]([id] int,[salenum] int,[saledate] datetime)
Insert table2select 1001, 5, '2009-8-10' union all
select 1002, 4, '2009-8-10' union all
select 1001, 3, '2009-8-10' union all
select 1003, 4, '2009-8-10' union all
select 1001, 1, '2009-8-11' union all
select 1002, 2, '2009-8-11' union all
select 1004, 6, '2009-8-11' union all
select 1001, 2, '2009-8-11'update table1
set saletotal=(select sum(salenum) from table2 where table2.id=table1.id)
select * from table1