从表中有:(单号)R2002052201,(物品代码)A001,(领用数量)20(存储仓库)A R2002052501, B001, 10 B R2002072101, A001, 10 A R2002072201, B002, 50 B
下面的DATES为你主表里的日期,SL 是你的从表的数量SELECT DISTINCT (x.单号), x.物品代码, x.仓库, SUM(CASE WHEN (DATEPART(mm, y.dates)) = 1 THEN sl END) '1月份', SUM(CASE WHEN (DATEPART(mm, y.dates)) = 2 THEN sl END) '2月份', SUM(CASE WHEN (DATEPART(mm, y.dates)) = 3 THEN sl END) '3月份', SUM(CASE WHEN (DATEPART(mm, y.dates)) = 4 THEN sl END) '4月份', SUM(CASE WHEN (DATEPART(mm, y.dates)) = 5 THEN sl END) '5月份', SUM(CASE WHEN (DATEPART(mm, y.dates)) = 6 THEN sl END) '6月份', SUM(CASE WHEN (DATEPART(mm, y.dates)) = 7 THEN sl END) '7月份', SUM(CASE WHEN (DATEPART(mm, y.dates)) = 8 THEN sl END) '8月份', SUM(CASE WHEN (DATEPART(mm, y.dates)) = 9 THEN sl END) '9月份', SUM(CASE WHEN (DATEPART(mm, y.dates)) = 10 THEN sl END) '10月份', SUM(CASE WHEN (DATEPART(mm, y.dates)) = 11 THEN sl END) '11月份', SUM(CASE WHEN (DATEPART(mm, y.dates)) = 12 THEN sl END) '12月份' FROM 主表 x,从表 y WHERE y.单号 = x.单号 AND (y.dates >= '01/01/2002' AND y.dates <= '12/31/2002') GROUP BY x.单号, x.物品代码, x.仓库, y.dates // y.dates >= '01/01/2002' AND y.dates <= '12/31/2002' //这句话表示你可以算出今年的。去年的什么的,你都可以自己改
FROM 主表 x,从表 y这句写错了 改为 FROM 从表 x,主表 y你试试是不是可以了,不行的话跟我说好了
谢谢你的热心帮忙小鱼儿,只是这个查询出来的结果是全年的所有值,我现在要对输入的一段时间进行查询.再有就是查询出来结果不是按“物品代码”归类的,而是以“领料单号”归类,我试着按了一下Order by 中的顺序也不行.
我希望得到的结果样式如: 物品代码 存储仓库 五月数量 六月数量 七月数量 A001 A 20 10 B001 B 10 B002 B 50
select a.单号,a.日期,b.物品代码,sum(b.领用数量) from 主表 a inner join 从表 b on a.单号=b.单号 where a.日期='2002-07-20' group by a.单号,a.日期,b.物品代码 order by a.单号 2002-07-2是你输入的日期.
在Where里还要加一个b.仓库='A' 仓库和日期应该是你要输入的条件吧?
回頭看看以前提出的問題﹐覺得這個還是在SQL語句中有一定的技巧﹐所以在學到東西的情況下也沒忘記與大家分享﹐下面是經過整理的 suny_2001 兄的SQL﹐貼出來大家看看﹕ TSQL:='SELECT DISTINCT RD.GoodsID "物品代碼",G.GoodsName "物品名稱",G.Standard "規格"'; For I:=StartMonth To EndMonth do //在某段時間內循環找出各月 begin Case I of 1: TSQL:=TSQL+', SUM(CASE WHEN (DATEPART(mm, R.Receivedate))= 1 THEN RD.ReceiveNumber end) "1月份"'; 2: TSQL:=TSQL+', SUM(CASE WHEN (DATEPART(mm, R.Receivedate))= 2 THEN RD.ReceiveNumber end) "2月份"'; 3: TSQL:=TSQL+', SUM(CASE WHEN (DATEPART(mm, R.Receivedate))= 3 THEN RD.ReceiveNumber end) "3月份"'; 4: TSQL:=TSQL+', SUM(CASE WHEN (DATEPART(mm, R.Receivedate))= 4 THEN RD.ReceiveNumber end) "4月份"'; 5: TSQL:=TSQL+', SUM(CASE WHEN (DATEPART(mm, R.Receivedate))= 5 THEN RD.ReceiveNumber end) "5月份"'; 6: TSQL:=TSQL+', SUM(CASE WHEN (DATEPART(mm, R.Receivedate))= 6 THEN RD.ReceiveNumber end) "6月份"'; 7: TSQL:=TSQL+', SUM(CASE WHEN (DATEPART(mm, R.Receivedate))= 7 THEN RD.ReceiveNumber end) "7月份"'; 8: TSQL:=TSQL+', SUM(CASE WHEN (DATEPART(mm, R.Receivedate))= 8 THEN RD.ReceiveNumber end) "8月份"'; 9: TSQL:=TSQL+', SUM(CASE WHEN (DATEPART(mm, R.Receivedate))= 9 THEN RD.ReceiveNumber end) "9月份"'; 10: TSQL:=TSQL+', SUM(CASE WHEN (DATEPART(mm, R.Receivedate))= 10 THEN RD.ReceiveNumber end) "10月份"'; 11: TSQL:=TSQL+', SUM(CASE WHEN (DATEPART(mm, R.Receivedate))= 11 THEN RD.ReceiveNumber end) "11月份"'; 12: TSQL:=TSQL+', SUM(CASE WHEN (DATEPART(mm, R.Receivedate))= 12 THEN RD.ReceiveNumber end) "12月份"'; end; end; DM.ADSReceive.Active:=false; DM.ADSReceive.CommandText:=TSQL+' FROM ReceiveDetail RD,Receive R,Goods G ' +'WHERE RD.ReceiveID = R.REceiveID AND (R.Receivedate >= '+''''+DateToStr(StartTime)+''''+'AND R.Receivedate <= '+''''+DateToStr(EndTime)+''''+') And R.StoreID='+''''+DM.ADSStoreID.Fields[0].AsString+''''+'And RD.GoodsID=G.GoodsID' +' GROUP BY RD.GoodsID,G.GoodsName,G.Standard';
R2002052501, 2002/5/25
R2002072101, 2002/7/21
R2002072201, 2002/7/22
从表中有:(单号)R2002052201,(物品代码)A001,(领用数量)20(存储仓库)A
R2002052501, B001, 10 B
R2002072101, A001, 10 A
R2002072201, B002, 50 B
(x.单号), x.物品代码, x.仓库, SUM(CASE WHEN (DATEPART(mm, y.dates))
= 1 THEN sl END) '1月份', SUM(CASE WHEN (DATEPART(mm, y.dates))
= 2 THEN sl END) '2月份', SUM(CASE WHEN (DATEPART(mm, y.dates))
= 3 THEN sl END) '3月份', SUM(CASE WHEN (DATEPART(mm, y.dates))
= 4 THEN sl END) '4月份', SUM(CASE WHEN (DATEPART(mm, y.dates))
= 5 THEN sl END) '5月份', SUM(CASE WHEN (DATEPART(mm, y.dates))
= 6 THEN sl END) '6月份', SUM(CASE WHEN (DATEPART(mm, y.dates))
= 7 THEN sl END) '7月份', SUM(CASE WHEN (DATEPART(mm, y.dates))
= 8 THEN sl END) '8月份', SUM(CASE WHEN (DATEPART(mm, y.dates))
= 9 THEN sl END) '9月份', SUM(CASE WHEN (DATEPART(mm, y.dates))
= 10 THEN sl END) '10月份', SUM(CASE WHEN (DATEPART(mm, y.dates))
= 11 THEN sl END) '11月份', SUM(CASE WHEN (DATEPART(mm, y.dates))
= 12 THEN sl END) '12月份'
FROM 主表 x,从表 y
WHERE y.单号 = x.单号 AND (y.dates >= '01/01/2002' AND y.dates <= '12/31/2002')
GROUP BY x.单号, x.物品代码, x.仓库, y.dates
// y.dates >= '01/01/2002' AND y.dates <= '12/31/2002'
//这句话表示你可以算出今年的。去年的什么的,你都可以自己改
你的结果想怎么样,你写出个例子出来,就是具体数据了。
你说后面加Where 条件我也试了试,没有改变.
物品代码 存储仓库 五月数量 六月数量 七月数量
A001 A 20 10
B001 B 10
B002 B 50
from 主表 a inner join 从表 b on a.单号=b.单号
where a.日期='2002-07-20'
group by a.单号,a.日期,b.物品代码
order by a.单号
2002-07-2是你输入的日期.
仓库和日期应该是你要输入的条件吧?
TSQL:='SELECT DISTINCT RD.GoodsID "物品代碼",G.GoodsName "物品名稱",G.Standard "規格"';
For I:=StartMonth To EndMonth do //在某段時間內循環找出各月
begin
Case I of
1:
TSQL:=TSQL+', SUM(CASE WHEN (DATEPART(mm, R.Receivedate))= 1 THEN RD.ReceiveNumber end) "1月份"';
2:
TSQL:=TSQL+', SUM(CASE WHEN (DATEPART(mm, R.Receivedate))= 2 THEN RD.ReceiveNumber end) "2月份"';
3:
TSQL:=TSQL+', SUM(CASE WHEN (DATEPART(mm, R.Receivedate))= 3 THEN RD.ReceiveNumber end) "3月份"';
4:
TSQL:=TSQL+', SUM(CASE WHEN (DATEPART(mm, R.Receivedate))= 4 THEN RD.ReceiveNumber end) "4月份"';
5:
TSQL:=TSQL+', SUM(CASE WHEN (DATEPART(mm, R.Receivedate))= 5 THEN RD.ReceiveNumber end) "5月份"';
6:
TSQL:=TSQL+', SUM(CASE WHEN (DATEPART(mm, R.Receivedate))= 6 THEN RD.ReceiveNumber end) "6月份"';
7:
TSQL:=TSQL+', SUM(CASE WHEN (DATEPART(mm, R.Receivedate))= 7 THEN RD.ReceiveNumber end) "7月份"';
8:
TSQL:=TSQL+', SUM(CASE WHEN (DATEPART(mm, R.Receivedate))= 8 THEN RD.ReceiveNumber end) "8月份"';
9:
TSQL:=TSQL+', SUM(CASE WHEN (DATEPART(mm, R.Receivedate))= 9 THEN RD.ReceiveNumber end) "9月份"';
10:
TSQL:=TSQL+', SUM(CASE WHEN (DATEPART(mm, R.Receivedate))= 10 THEN RD.ReceiveNumber end) "10月份"';
11:
TSQL:=TSQL+', SUM(CASE WHEN (DATEPART(mm, R.Receivedate))= 11 THEN RD.ReceiveNumber end) "11月份"';
12:
TSQL:=TSQL+', SUM(CASE WHEN (DATEPART(mm, R.Receivedate))= 12 THEN RD.ReceiveNumber end) "12月份"';
end;
end;
DM.ADSReceive.Active:=false;
DM.ADSReceive.CommandText:=TSQL+' FROM ReceiveDetail RD,Receive R,Goods G '
+'WHERE RD.ReceiveID = R.REceiveID AND (R.Receivedate >= '+''''+DateToStr(StartTime)+''''+'AND R.Receivedate <= '+''''+DateToStr(EndTime)+''''+') And R.StoreID='+''''+DM.ADSStoreID.Fields[0].AsString+''''+'And RD.GoodsID=G.GoodsID'
+' GROUP BY RD.GoodsID,G.GoodsName,G.Standard';