根据你上面的要求,可以这样---测试数据--- if object_id('[a]') is not null drop table [a] go create table [a]([编号] int,[序号] int,[商品码] int,[数量] int,[金额] numeric(4,2)) insert [a] select 39474,1,1151,2,1.9 union all select 39474,1,1151,1,1.9 union all select 39474,2,1905,10,30.8 union all select 39474,2,1485,1,18.4 union all select 39474,3,1438,1,31.05 union all select 39474,3,30,4,0.23 union all select 39474,4,60,1,57.5 union all select 39474,4,26,1,0.22 union all select 39474,5,1521,2,23.46 if object_id('[b]') is not null drop table [b] go create table [b]([编号] varchar(6),[总费用] numeric(5,2),[时间] datetime,[操作员] varchar(4)) insert [b] select '039474',400.35,'2008-02-11 10:22:02.950','1426' union all select '039474',68.36,'2008-12-12 11:22:33.250','0140'---查询--- select 商品码, sum(数量) as 数量 from a,b where a.编号=b.编号 and a.商品码='1905' and b.操作员='0140' and b.时间>'2008-12-1' group by 商品码---结果--- 商品码 数量 ----------- ----------- 1905 10(所影响的行数为 1 行)
根据你上面的要求,可以这样---测试数据---
if object_id('[a]') is not null drop table [a]
go
create table [a]([编号] int,[序号] int,[商品码] int,[数量] int,[金额] numeric(4,2))
insert [a]
select 39474,1,1151,2,1.9 union all
select 39474,1,1151,1,1.9 union all
select 39474,2,1905,10,30.8 union all
select 39474,2,1485,1,18.4 union all
select 39474,3,1438,1,31.05 union all
select 39474,3,30,4,0.23 union all
select 39474,4,60,1,57.5 union all
select 39474,4,26,1,0.22 union all
select 39474,5,1521,2,23.46
if object_id('[b]') is not null drop table [b]
go
create table [b]([编号] varchar(6),[总费用] numeric(5,2),[时间] datetime,[操作员] varchar(4))
insert [b]
select '039474',400.35,'2008-02-11 10:22:02.950','1426' union all
select '039474',68.36,'2008-12-12 11:22:33.250','0140'---查询---
select
商品码,
sum(数量) as 数量
from a,b
where a.编号=b.编号
and a.商品码='1905'
and b.操作员='0140' and b.时间>'2008-12-1'
group by 商品码---结果---
商品码 数量
----------- -----------
1905 10(所影响的行数为 1 行)