SELECT PutSubmer=isnull(PutSubmer,0), xyku=isnull(xyku,0), sykc=isnull(sykc,0), bykc=isnull(bykc,0)
from
(SELECT PutSubmer = SUM(CASE WHEN 0 = 0 AND Stock.spmc like '21108043152' THEN PutStock.PutSubmer ELSE 0 END),
xyku = SUM(CASE WHEN 0 = 0 AND year(PutStock.adddate)>= 2011 AND month(PutStock.adddate)>= 11 and Stock.spmc like '21108043152' THEN PutStock.PutSubmer ELSE 0 END),
sykc = SUM(CASE WHEN 0 = 0 AND year(PutStock.adddate)<= 2011 AND month(PutStock.adddate)<= 10 and Stock.spmc like '21108043152' THEN PutStock.PutSubmer ELSE 0 END),
bykc = SUM(CASE WHEN 0 = 0 AND year(PutStock.adddate) = 2011 AND month(PutStock.adddate)= 11 and Stock.spmc like '21108043152' THEN PutStock.PutSubmer ELSE 0 END)
FROM Stock, PutStock
WHERE Stock.ID = PutStock.F_ID) t1我在网上看到例子,照搬了一下,其实我对这个语句不是很了解。我照着写了,但是得不到想要的数据
哪位高手给我大致讲讲这种写法
PutSubmer = ISNULL(SUM(CASE WHEN Stock.spmc like '21108043152' THEN PutStock.PutSubmer ELSE 0 END),0),
xyku = ISNULL(SUM(CASE WHEN year(PutStock.adddate)>= 2011 AND month(PutStock.adddate)>= 11 and Stock.spmc like '21108043152' THEN PutStock.PutSubmer ELSE 0 END),0),
sykc = ISNULL(SUM(CASE WHEN year(PutStock.adddate)<= 2011 AND month(PutStock.adddate)<= 10 and Stock.spmc like '21108043152' THEN PutStock.PutSubmer ELSE 0 END),0),
bykc = ISNULL(SUM(CASE WHEN year(PutStock.adddate) = 2011 AND month(PutStock.adddate)= 11 and Stock.spmc like '21108043152' THEN PutStock.PutSubmer ELSE 0 END),0)
FROM
Stock LEFT JOIN PutStock
ON
Stock.ID = PutStock.F_ID
PutSubmer = ISNULL(SUM(PutStock.PutSubmer),0),
xyku = ISNULL(SUM(CASE WHEN year(PutStock.adddate)>= 2011 AND month(PutStock.adddate)>= 11 THEN PutStock.PutSubmer ELSE 0 END),0),
sykc = ISNULL(SUM(CASE WHEN year(PutStock.adddate)<= 2011 AND month(PutStock.adddate)<= 10 THEN PutStock.PutSubmer ELSE 0 END),0),
bykc = ISNULL(SUM(CASE WHEN year(PutStock.adddate) = 2011 AND month(PutStock.adddate)= 11 THEN PutStock.PutSubmer ELSE 0 END),0)
FROM
Stock LEFT JOIN PutStock
ON
Stock.ID = PutStock.F_ID
WHERE
Stock.spmc like '21108043152'
ID 商品名称 商品编号 商品类型
1 手镯 20111211 玉器
2 金佛 20111212 金器
3 玉佛 20111213 玉器
4 金钗 20111213 金器表B
ID 库存数 日期 P_ID
1 5 2011-11-1 1
2 6 2011-12-1 2
3 5 2011-11-2 3
4 6 2011-12-2 4
5 4 2011-11-1 1
6 5 2011-12-2 1表C 销售表
ID 商品名称 销售数量 商品类别 F_ID 销售时间
1 手镯 1 玉器 1 2011-12-1
2 手镯 1 玉器 5 2011-12-12
3 金佛 1 金器 2 2011-12-2
结果 查询 2011年12月份库存数和销售数据商品名称 商品编号 商品类别 库存总数 12月份之前的库存数 12月份进货数 12月份销售数
手镯 20111211 玉器 14 9 5 2
金佛 20111212 金器 6 6 0 0
玉佛 20111213 玉器 5 5 0 0
金钗 20111213 金器 6 6 0 0
其中表B列P_ID是表A列ID的父键
表C F_ID 是表B的父ID