sorry,錯了,是值裡有這個字符串:)。不過報表的標題是a & b.看下面的sql語句. select site,sum(decode(stage,'DVT & A',1,0)) DVT,sum(decode(stage,'EOL',1,0)) EOL,sum(decode(stage,'MP',1,0)) MP,sum(decode(stage,'PVT',1,0)) PVT, sum(decode(stage,'DVT',1,0))+sum(decode(stage,'EOL',1,0))+sum(decode(stage,'MP',1,0))+sum(decode(stage,'PVT',1,0)) 總計 from action_memo group by siteSQL> select site,category,count(*) num from action_memo group by site,category;SITE CATEGORY NUM ---------- -------------------- ---------- MKL MB and D/D 37 MKL Small BD 20 MPTK Plastic 27 MPTS Cable&Wire 5 MPTS Iron 8 MPTS Plastic 13 MSL MB and D/D 23 MSL Small BD 4 TSD MB and D/D 21
樓上的,我也沒辦法,老板要求這樣,我有什麼辦法. SQL> select site,category,count(*) num from action_memo group by site,category;SITE CATEGORY NUM ---------- -------------------- ---------- MKL MB and D/D 37 MKL Small BD 20 MPTK Plastic 27 MPTS Cable&Wire 5 MPTS Iron 8 MPTS Plastic 13 MSL MB and D/D 23 MSL Small BD 4 TSD MB and D/D 21報表結構: SITE Cable&Wire Iron MB and D/D Plastic Small BD 總計 % MKL 33 20 53 34.87% MSL 1 23 1 4 29 19.08% MPTK 26 26 17.11% MPTS 4 7 12 23 15.13% TSD 21 21 13.82% 總計 4 8 77 39 24 152 100.00% % 2.63% 5.26% 50.66% 25.66% 15.79% 100.00% 我的sql想這麼寫,但是有&,and等麻煩 select site,sum(decode(category,'Cable&Wire',1,0)) Cable&Wire,sum(decode(scategory,'Iron',1,0)) Iron,sum(decode(category,'MB and D/D',1,0)) MB and D/D,sum(decode(category,'Plastic',1,0)) Plastic,sum(decode(category,'Small BD',1,0)) Small BD from action_memo group by site
select site,sum(decode(stage,'DVT & A',1,0)) DVT,sum(decode(stage,'EOL',1,0)) EOL,sum(decode(stage,'MP',1,0)) MP,sum(decode(stage,'PVT',1,0)) PVT,
sum(decode(stage,'DVT',1,0))+sum(decode(stage,'EOL',1,0))+sum(decode(stage,'MP',1,0))+sum(decode(stage,'PVT',1,0)) 總計
from action_memo group by siteSQL> select site,category,count(*) num from action_memo group by site,category;SITE CATEGORY NUM
---------- -------------------- ----------
MKL MB and D/D 37
MKL Small BD 20
MPTK Plastic 27
MPTS Cable&Wire 5
MPTS Iron 8
MPTS Plastic 13
MSL MB and D/D 23
MSL Small BD 4
TSD MB and D/D 21
SQL> select site,category,count(*) num from action_memo group by site,category;SITE CATEGORY NUM
---------- -------------------- ----------
MKL MB and D/D 37
MKL Small BD 20
MPTK Plastic 27
MPTS Cable&Wire 5
MPTS Iron 8
MPTS Plastic 13
MSL MB and D/D 23
MSL Small BD 4
TSD MB and D/D 21報表結構:
SITE Cable&Wire Iron MB and D/D Plastic Small BD 總計 %
MKL 33 20 53 34.87%
MSL 1 23 1 4 29 19.08%
MPTK 26 26 17.11%
MPTS 4 7 12 23 15.13%
TSD 21 21 13.82%
總計 4 8 77 39 24 152 100.00%
% 2.63% 5.26% 50.66% 25.66% 15.79% 100.00% 我的sql想這麼寫,但是有&,and等麻煩
select site,sum(decode(category,'Cable&Wire',1,0)) Cable&Wire,sum(decode(scategory,'Iron',1,0)) Iron,sum(decode(category,'MB and D/D',1,0)) MB and D/D,sum(decode(category,'Plastic',1,0)) Plastic,sum(decode(category,'Small BD',1,0)) Small BD
from action_memo group by site
'a'|| chr(67)||'b'