SQL转置 (CASE),
SELECT (CASE WHEN startdate >= '2003-12-31'
THEN startdate ELSE '2003-12-31' END) as thedate,
market, COUNT(*) as numstarts, 0 as numstops
FROM subscribers s
WHERE startdate IS NOT NULL
GROUP BY (CASE WHEN startdate >= '2003-12-31'
THEN startdate ELSE '2003-12-31' END), market
ORDER BY thedate
计算三个市场中每天开始的订阅客户数量。利用子查询分组,将早于2003-12-31的订单归为一组。以thedate和market字段分组。得到
thedate market numstarts numstops
2003-12-31 Gotham 1049470 0
2003-12-31 Metropolis 588557 0
2003-12-31 Smallville 368107 0
2004-01-01 Gotham 218 0
2004-01-01 Metropolis 129 0
2004-01-01 Smallville 2 0
用UNION ALL 联合结束的人数。最后在外层查询中用CASE将每一个市场拆分出来从而完成转置。
本站文章为和通数据库网友分享或者投稿,欢迎任何形式的转载,但请务必注明出处.
同时文章内容如有侵犯了您的权益,请联系QQ:970679559,我们会在尽快处理。