--测试数据
create table OrderDetail(orderID varchar(20),ItemNo varchar(20),Quantity varchar(20))
insert OrderDetail select '1','2','100'
union all select '2','3','200'create table Shipping(ID int,OrderID varchar(20),ItemNo varchar(20),ShippingQuantity varchar(20))
insert Shipping select '1','1','2','20'
union all select '2','1','2','30'
union all select '3','1','2','10'
union all select '4','2','3','50'
union all select '5','2','3','20'
go--查询统计
select a.OrderID,a.ItemNO,a.Quantity
,剩余数量=cast(a.Quantity as int)-isnull(b.ShippingQuantity,0)
from OrderDetail a
left join(
select OrderID,ItemNO,ShippingQuantity=sum(cast(ShippingQuantity as int))
from Shipping
group by OrderID,ItemNO
)b on a.OrderID=b.OrderID and a.ItemNO=b.ItemNO
go--删除测试
drop table OrderDetail,Shipping/*--结果OrderID ItemNO Quantity 剩余数量
-------------------- -------------------- -------------------- -----------
1 2 100 40
2 3 200 130(所影响的行数为 2 行)
--*/
create table OrderDetail(orderID varchar(20),ItemNo varchar(20),Quantity varchar(20))
insert OrderDetail select '1','2','100'
union all select '2','3','200'create table Shipping(ID int,OrderID varchar(20),ItemNo varchar(20),ShippingQuantity varchar(20))
insert Shipping select '1','1','2','20'
union all select '2','1','2','30'
union all select '3','1','2','10'
union all select '4','2','3','50'
union all select '5','2','3','20'
go--查询统计
select a.OrderID,a.ItemNO,a.Quantity
,剩余数量=cast(a.Quantity as int)-isnull(b.ShippingQuantity,0)
from OrderDetail a
left join(
select OrderID,ItemNO,ShippingQuantity=sum(cast(ShippingQuantity as int))
from Shipping
group by OrderID,ItemNO
)b on a.OrderID=b.OrderID and a.ItemNO=b.ItemNO
go--删除测试
drop table OrderDetail,Shipping/*--结果OrderID ItemNO Quantity 剩余数量
-------------------- -------------------- -------------------- -----------
1 2 100 40
2 3 200 130(所影响的行数为 2 行)
--*/
解决方案 »
- SQL查询语句
- 问个简单的sql字符串转换问题,急!
- 100分求把一delphi简单函数转化为sqlserver语法的数据库自定义函数
- 各位帮帮忙,急,求一个sql语句~~~~~~~~~~~~!
- 在SQL Server里找不到"Microsoft Query"客户端应用程序?
- 请sql语句高手,优化一下。
- 存储过程的注释符是什么?
- 一个表与另一个表的联接是怎么回事??
- 急问!!!![dbnetlib][(connectionwrite(wrapperwrite()))]一般网络性错误.请检查网络文档
- 能否在数据库视图增加一个自增加列呢?从1开始!
- 为什么我在添加记录的时候,总是莫名其妙的添加两条相同的记录呢(只有id不同)??
- 多个表查询
isnull(b.ShippingQuantity,0)
中的b.ShippingQuantity最好也能转换成数字
当然不转换的话sql server会自动转
ShippingQuantity=sum(cast(ShippingQuantity as int))
ShippingQuantity已经转为Int了。
create table OrderDetail(orderID varchar(20),ItemNo varchar(20),Quantity varchar(20))
insert OrderDetail select '1','2','100'
union all select '2','3','200'create table Shipping(ID int,OrderID varchar(20),ItemNo varchar(20),ShippingQuantity varchar(20))
insert Shipping select '1','1','2','20'
union all select '2','1','2','30'
union all select '3','1','2','10'
union all select '4','2','3','50'
union all select '5','2','3','20'
go--查询统计
select orderID=max(orderID),ItemNo,Quantity=sum(case when Qry>0 then Qry else 0 end),剩余数量=sum(Qry)
from(
select orderID,ItemNo,Qry=cast(Quantity as integer) from OrderDetail
union
select null,ItemNo,-cast(ShippingQuantity as integer) from Shipping )a
group by ItemNo
go--删除测试
drop table OrderDetail,Shipping/*--结果OrderID ItemNO Quantity 剩余数量
-------------------- -------------------- -------------------- -----------
1 2 100 40
2 3 200 130(所影响的行数为 2 行)
--*/
--建立测试环境
create table OrderDetail(orderID varchar(20),ItemNo varchar(20),Quantity varchar(20))
insert OrderDetail select '1','2','100'
union all select '2','3','200'create table Shipping(ID int,OrderID varchar(20),ItemNo varchar(20),ShippingQuantity varchar(20))
insert Shipping select '1','1','2','20'
union all select '2','1','2','30'
union all select '3','1','2','10'
union all select '4','2','3','50'
union all select '5','2','3','20'
go
--测试
Select OrderID,ItemNo,Quantity,
剩余数量=(Quantity-(Select IsNull(SUM(Convert(Int,ShippingQuantity)),0) from Shipping Where OrderID=A.OrderID And ItemNo=A.ItemNo Group By OrderID,ItemNo))
from OrderDetail A
--删除测试环境
drop table OrderDetail,Shipping
--结果
/*
OrderID ItemNo Quantity 剩余数量
1 2 100 40
2 3 200 130
*/