1.有两个表
表A
列名MC(名称) ZL(商品总量)
A 200
B 100
表B
MC CKSL(出库数量)
A 10
A 20
A 30
B 20
B 40
求:用一句SQL语言显示出剩余商品名称和总量?(要求一句SQL语句)2.有一个表
表TEST
CS(城市) ZL(种类) SL(数量)
北京 苹果 100
北京 李子 200
上海 苹果 100
上海 李子 300
要求:用一句SQL使显示下列内容
CS 苹果 李子
北京 100 200
上海 100 3003.有表 A
MC(名称) ZT(出入库状态) SL(数量)
A IN 200
A OUT 20
B IN 100
要求:也是用一句SQL语句查询显示出这个表中AB的剩余数量小弟觉得自己SQL非常不好,只有简单的SQL语句能力,请各位大侠请教!小弟万分感谢!!!!
表A
列名MC(名称) ZL(商品总量)
A 200
B 100
表B
MC CKSL(出库数量)
A 10
A 20
A 30
B 20
B 40
求:用一句SQL语言显示出剩余商品名称和总量?(要求一句SQL语句)2.有一个表
表TEST
CS(城市) ZL(种类) SL(数量)
北京 苹果 100
北京 李子 200
上海 苹果 100
上海 李子 300
要求:用一句SQL使显示下列内容
CS 苹果 李子
北京 100 200
上海 100 3003.有表 A
MC(名称) ZT(出入库状态) SL(数量)
A IN 200
A OUT 20
B IN 100
要求:也是用一句SQL语句查询显示出这个表中AB的剩余数量小弟觉得自己SQL非常不好,只有简单的SQL语句能力,请各位大侠请教!小弟万分感谢!!!!
select a.MC,剩余数=a.ZL-c.CKSL from 表A a
left join (select MC,sum(CKSL) as CKSL from 表B b group by MC) c on a.MC=c.MC
from a join (select mc,sum(cksl) zl from b group by mc) b on a.mc = b.mc2、select cs,sum(case zl when '苹果' then sl else 0 end) [苹果],
sum(case zl when '李子' then sl else 0 end) [李子]
from test3、select mc,sum(case zt when 'in' then sl when 'out' then -sl end) sl
from a
group by mc
declare @表A table (MC varchar(1),ZL int)
insert into @表A
select 'A',200 union all
select 'B',100declare @表B table (MC varchar(1),CKSL int)
insert into @表B
select 'A',10 union all
select 'A',20 union all
select 'A',30 union all
select 'B',20 union all
select 'B',40select a.MC,剩余数=a.ZL-c.CKSL from @表A a
left join (select MC,sum(CKSL) as CKSL from @表B b group by MC) c on a.MC=c.MC
/*
MC 剩余数
---- -----------
A 140
B 40
*/declare @表TEST table (CS varchar(4),ZL varchar(4),SL int)
insert into @表TEST
select '北京','苹果',100 union all
select '北京','李子',200 union all
select '上海','苹果',100 union all
select '上海','李子',300select CS,
苹果=max(case ZL when '苹果' then SL else 0 end),
李子=max(case ZL when '李子' then SL else 0 end)
from @表TEST group by CS
/*
CS 苹果 李子
---- ----------- -----------
北京 100 200
上海 100 300
*/
--1、select a.mc,a.zl-b.zl as zl
from a join (select mc,sum(cksl) zl from b group by mc) b on a.mc = b.mc--2、select cs,sum(case zl when '苹果' then sl else 0 end) [苹果],
sum(case zl when '李子' then sl else 0 end) [李子]
from test--3、select mc,sum(case zt when 'in' then sl when 'out' then -sl end) sl
from a
group by mc
select MC,zl=zl-(select sum(CKSL) from B where MC=A.MC)
from A
--2
select CS,[苹果]=max(case when ZL='苹果' then SL end),
[李子]=max(case when ZL='李子' then SL end)
from TEST group by CS
--3
select MC,sum(SL*(case when ZT='IN' then 1 else -1 end))
from A group by MC
2、select cs,sum(case zl when '苹果' then sl else 0 end) [苹果],
sum(case zl when '李子' then sl else 0 end) [李子]
from test
group by cs修正下!
declare @表A table (MC varchar(1),ZT varchar(3),SL int)
insert into @表A
select 'A','IN',200 union all
select 'A','OUT',20 union all
select 'B','IN',100select MC,剩余数量=sum(case ZT when 'IN' then SL when 'OUT' then -SL end)
from @表A group by MC/*
MC 剩余数量
---- -----------
A 180
B 100
*/
第二问,行列转换。这应该是最简单的行列转换。lz可以搜一下行列转换精华帖,学习学习
第三问,分组查询时,对out的设置一下正负,然后根据分组相加减。
sql语句见楼上各位大仙的
select mc,zl=zl-(select sum(CKSL) from B where mc=a.mc) from aselect
cs,
max(case when ZL='苹果' then SL end)苹果,
max(case when ZL='李子' then SL end)李子
from
test
group by
csselect
MC,sum(SL*(case when ZT='IN' then 1 else -1 end))
from
A
group by
MC
declare @表TEST table (CS varchar(4),ZL varchar(4),SL int)
insert into @表TEST
select '北京','苹果',100 union all
select '北京','李子',200 union all
select '上海','苹果',100 union all
select '上海','李子',300select * from @表TEST pivot(sum(sl) for zl in([苹果],[李子]))as a
declare @表A table (MC varchar(1),ZL int)
insert into @表A
select 'A',200 union all
select 'B',100declare @表B table (MC varchar(1),CKSL int)
insert into @表B
select 'A',10 union all
select 'A',20 union all
select 'A',30 union all
select 'B',20 union all
select 'B',40
--1.
select a.MC,剩余数=max(a.ZL)-sum(b.CKSL)
from @表A a,@表B b
where a.MC=b.MC
group by a.MC
/*
MC 剩余数
---- -----------
A 140
B 40
*/
--2.
declare @表TEST table (CS varchar(4),ZL varchar(4),SL int)
insert into @表TEST
select '北京','苹果',100 union all
select '北京','李子',200 union all
select '上海','苹果',100 union all
select '上海','李子',300
select CS,[苹果],[李子] from @表TEST pivot(sum(SL) for ZL in([苹果],[李子])) as b
/*
CS 苹果 李子
---- ----------- -----------
北京 100 200
上海 100 300
*/--3.
declare @表C table (MC varchar(1),ZT varchar(3),SL int)
insert into @表C
select 'A','IN',200 union all
select 'A','OUT',20 union all
select 'B','IN',100select MC,
sum(case ZT when 'IN' then SL when 'OUT' then -SL end) as 剩余数量
from @表C group by MC
/*
MC 剩余数量
---- -----------
A 180
B 100
*/
第三个受教了。
让我写,我会用常规的思路,分别求出‘IN’ 和‘OUT’,这样比较复杂的。