问题是这样的:
有两张表A,B;
表A有两个字段type(主键)表示商品品种, num字段表示商品总数量。
表B也有两个字段type表示商品品种,num已经售出商品数量。
表A的数据如下:
type num
A 1000
B 2000
C 2500表B的数据如下:
type num
A 100
B 200
A 300
A 100
B 200现在要查询A表中剩余商品的数量,即查询结果为:
type num
A 500
B 1600
C 2500不知道这样的sql语句怎么写,请大家帮帮忙,谢谢!
有两张表A,B;
表A有两个字段type(主键)表示商品品种, num字段表示商品总数量。
表B也有两个字段type表示商品品种,num已经售出商品数量。
表A的数据如下:
type num
A 1000
B 2000
C 2500表B的数据如下:
type num
A 100
B 200
A 300
A 100
B 200现在要查询A表中剩余商品的数量,即查询结果为:
type num
A 500
B 1600
C 2500不知道这样的sql语句怎么写,请大家帮帮忙,谢谢!
解决方案 »
- 加与删
- SQL Server 安装程序无法连接到数据库服务进行服务器配置。 错误为: [Microsoft][SQL Native Client]客户端不支持加密
- 查询表1的数据 要求:不能包含表2中数据 求各位帮我看一下我的代码(sql 2000)
- 怎样将连接多个SQL语句自动变为一个字符串,省去人工用许多加号“+”和分号“"”连接各个SQL句子。见例子:
- 求一条很简单的SQL语句,高手帮帮忙急
- 求一字符串匹配以及修改的sql语句或者存储过程(急,进者有分)
- SQLserver小数点问题!!!!!!!!!!!!!!!!!!
- 给位高手:小弟求解一个sql解决方案
- 请问,同一实例中,如何把一个数据库中的一张表拷贝到别一个数据库中(如果同名表则覆盖)?谢谢!
- 一个字段的多个值怎么求和
- 用sql语句创建数据库的问题???
- 请大侠们帮我看下我的存储过程
from ta a
left join (select type,sum(num) as num from tb group by type) b
on a.type = b.type
(
select type sum(num) num1 from B group by type
) B on A.type=B.type group by A.type
a.[type],
a.num-isnull(b.num,0)
from
a
left join
(select [type],sum(num) as num from b group by [type]) b
on
a.[type]=b.[type]
drop table a
go
create table a([type] varchar(10),[num] int)
insert a select 'A',1000
insert a select 'B',2000
insert a select 'C',2500
go
if object_id('b') is not null
drop table b
go
create table b([type] varchar(10),[num] int)
insert b select 'A','100'
insert b select 'B','200'
insert b select 'A','300'
insert b select 'A','100'
insert b select 'B','200'
goselect type,num-isnull((select sum(num) from b where type=t.type),0) num from a t/*
type num
---------- -----------
A 500
B 1600
C 2500(3 行受影响)
*/
from A ta
left join (select type,sum(num) as num from B group by type) tb
on ta.type = tb.type
declare @ta table(type varchar(2),num smallint)
insert into @ta
select 'A',1000
union all
select 'B',2000
union all
select 'C',2500--定义临时表tb,并插入数据
declare @tb table(type varchar(2),num smallint)
insert into @tb
select 'A',100
union all
select 'B',200
union all
select 'A',300
union all
select 'A',100
union all
select 'B',200--查询出每种商品的剩余数量
select t1.type,case when t2.soldqty is null then num else num-t2.soldqty end as leftQty from @ta t1 left join
(
select type,sum(num) as soldQty from @tb group by type
) t2 on t1.type=t2.type
查询结果:
type leftQty
---- -----------
A 500
B 1600
C 2500(所影响的行数为 3 行)
SELECT T1.TYPE,T1.NUM
FROM
(
SELECT T2.TYPE,(T2.NUM - T3.NUM) NUM FROM
A T2,
(SELECT SUM(NUM) FROM B GROUP BY TYPE) T3 WHERE T2.TYPE = T3.TYPE
) T1
FROM
(
SELECT A.TYPE TYPE,(A.NUM - T3.NUM) NUM FROM
A,
(SELECT SUM(B.NUM) NUM,B.TYPE TYPE FROM B GROUP BY TYPE) T3 WHERE A.TYPE = T3.TYPE
) T1
UNION
SELECT TYPE,NUM FROM A
WHERE TYPE NOT IN
(
SELECT TYPE FROM
(
SELECT T1.TYPE,T1.NUM
FROM
(
SELECT A.TYPE TYPE,(A.NUM - T3.NUM) NUM FROM
A,
(SELECT SUM(B.NUM) NUM,B.TYPE TYPE FROM B GROUP BY TYPE) T3 WHERE A.TYPE = T3.TYPE
) T1
)T5
)
declare @ta table(type varchar(2),num smallint)
insert into @ta
select 'A',1000
union all
select 'B',2000
union all
select 'C',2500--定义临时表tb,并插入数据
declare @tb table(type varchar(2),num smallint)
insert into @tb
select 'A',100
union all
select 'B',200
union all
select 'A',300
union all
select 'A',100
union all
select 'B',200--查询出每种商品的剩余数量
select t1.type,case when t2.soldqty is null then num else num-t2.soldqty end as leftQty from @ta t1 left join
(
select type,sum(num) as soldQty from @tb group by type
) t2 on t1.type=t2.type
select A.type as type1,(case when C.summ is null then A.num else (A.num-C.summ) end) as csum from #ta A left join(select type,sum(num)as summ from #tb group by type) C on A.type=C.type 温州it程序员群32677495,温州的IT人++,大家多多交流
SELECT A.[type]
,m=(A.[num]-isnull(N.s,0))
FROM A left join
(
SELECT [type]
,sum([num]) as s
FROM [B] group by [type]
) N
on A.[type]=N.[type]