有两个表
表一 t1
种类mc 库存总量shu
A 997
B 1234
表二 t2
种类mc 出库数量chushu
A 105
A 213
B 116
B 211
B 303
用一条SQL语句求出A,B各剩下多少?
答案
select t1.mc, shu-sumnum as sy from t1,(select mc, sum(t2.chushu) as sumnum from t2 group by mc) as t5 where t1.mc=t5.mc
理解:
select * from t1
select mc,sum(t2.chushu) as sumnum from t2 group by mc
把上面两个组合一下,就得到答案,我用sql server 2008 运行, 答案中的 as 可以省略,
第二道题目:
有两张表,一张员工表username, 一张销售表tab_data1, 如下图: 求 counts 为 0 或者 null 的员工信息
答案:
select name,age,laojia from
(select id from tab_data1 where data_time like '____05__' and counts=0 or counts is NULL) as tb4,username where tb4.id=username.id