表1 :Client
ClientNo, ClientName
0001 上海A公司
0002 上海B公司
0003 上海C公司表2:Sales(销售记录表,字段客户编号,月份,销售额)
ClientNo , YF , XSE
0001 1 100
0001 1 10
0002 1 50
0001 2 80
0002 3 500注:同一客户同月会有重复记录,有的客户当月会没有销售记录,但在结果表中要以0反映。
SQL 查询后的结果:所有客户1-12月份的销售额,如果当月客户没有销售记录则按0处理,结果如下:
客户编号 客户名称 1月销售额 2月销售额 3月销售额.......12月销售额
0001 上海A公司 110 80 0 0
0002 上海B公司 50 0 500 0
0003 上海C公司 0 0 0 0
ClientNo, ClientName
0001 上海A公司
0002 上海B公司
0003 上海C公司表2:Sales(销售记录表,字段客户编号,月份,销售额)
ClientNo , YF , XSE
0001 1 100
0001 1 10
0002 1 50
0001 2 80
0002 3 500注:同一客户同月会有重复记录,有的客户当月会没有销售记录,但在结果表中要以0反映。
SQL 查询后的结果:所有客户1-12月份的销售额,如果当月客户没有销售记录则按0处理,结果如下:
客户编号 客户名称 1月销售额 2月销售额 3月销售额.......12月销售额
0001 上海A公司 110 80 0 0
0002 上海B公司 50 0 500 0
0003 上海C公司 0 0 0 0
sum( case yf when 1 then xse else 0 end) as '1月份销售额',
sum( case yf when 1 then xse else 0 end) as '1月份销售额',
.
.
.
sum( case yf when 12 then xse else 0 end) as '12月份销售额'
group by yf中间的case 你可以用个循环灵活替换
select clientno,clientname,
sum(xse1) as xse1,
sum(xse2) as xse2,
sum(xse3) as xse3,
sum(xse4) as xse4,
sum(xse5) as xse5,
sum(xse6) as xse6,
sum(xse7) as xse7,
sum(xse8) as xse8,
sum(xse9) as xse9,
sum(xse10) as xse10,
sum(xse11) as xse11,
sum(xse12) as xse12
from
(
select client.clientno,client.clientname,
a.xse1,
a.xse2,
a.xse3,
a.xse4,
a.xse5,
a.xse6,
a.xse7,
a.xse8,
a.xse9,
a.xse10,
a.xse11,
a.xse12
from client
left join
(
select clientno,
case yf when 1 then xse else 0 end as xse1,
case yf when 2 then xse else 0 end as xse2,
case yf when 3 then xse else 0 end as xse3,
case yf when 4 then xse else 0 end as xse4,
case yf when 5 then xse else 0 end as xse5,
case yf when 6 then xse else 0 end as xse6,
case yf when 7 then xse else 0 end as xse7,
case yf when 8 then xse else 0 end as xse8,
case yf when 9 then xse else 0 end as xse9,
case yf when 10 then xse else 0 end as xse10,
case yf when 11 then xse else 0 end as xse11,
case yf when 12 then xse else 0 end as xse12
from
(
select clientno,yf,sum(xse) as xse from sales1 group by clientno,yf
)t)a on client.clientno=a.clientno)bgroup by clientno,clientname