dpwu16132 2013-08-29 01:28
浏览 90
已采纳

使用日期列创建类似于表的数据透视表的最佳方法

I have a few tables with data and I am attempting to generate a pivot like table in the following format:

Area  | Type | 2013-08-25 | 2013-08-26 | 2013-08-27, etc..
South | Red  |     5      |      2     |       9
North | Blue |     3      |      0     |       7

Currently to generate this I am using PHP to loop through each date and generate the sum(if statements for each date based upon a given start & end date.

I know I could run the query and group by date before and then assemble it in PHP but an SQL solution seems easier than that.

Is this the best way to accomplish what I am trying to do or is there another easier way I am missing?

Example Query:

SELECT 
iw.display_name as Area, 
iet.type as 'Type', 
sum(if(date(iac.created_at) = '2013-08-25',1,0)) as '2013-08-25', 
sum(if(date(iac.created_at) = '2013-08-26',1,0)) as '2013-08-26', 
sum(if(date(iac.created_at) = '2013-08-27',1,0)) as '2013-08-27', 
sum(if(date(iac.created_at) = '2013-08-28',1,0)) as '2013-08-28', 
sum(if(date(iac.created_at) = '2013-08-29',1,0)) as '2013-08-29', 
sum(if(date(iac.created_at) = '2013-08-30',1,0)) as '2013-08-30', 
sum(if(date(iac.created_at) = '2013-08-31',1,0)) as '2013-08-31',  
count(iac.id) as total 
FROM iac
JOIN ioc on ioc.id = iac.ioc_id
JOIN iet on iet.id = ioc.iet_id
JOIN iw  on  iw.id = iac.iw_id
WHERE date(iac.created_at) between '2013-08-25' and '2013-08-31'
GROUP BY iw.id, iet.id
ORDER BY iw.display_name, iet.type
  • 写回答

2条回答 默认 最新

  • doqrjrc95405 2013-08-29 01:37
    关注

    Your method is fine. You can simplify it (because you are using MySQL) by removing the if conditions. Booleans are treated as 1 for true and 0 for false:

    SELECT iw.display_name as Area, iet.type as `Type`, 
           sum(date(iac.created_at) = '2013-08-25') as `2013-08-25`, 
           sum(date(iac.created_at) = '2013-08-26') as `2013-08-26`, 
           sum(date(iac.created_at) = '2013-08-27') as `2013-08-27`, 
           sum(date(iac.created_at) = '2013-08-28') as `2013-08-28`, 
           sum(date(iac.created_at) = '2013-08-29') as `2013-08-29`, 
           sum(date(iac.created_at) = '2013-08-30') as `2013-08-30`, 
           sum(date(iac.created_at) = '2013-08-31') as `2013-08-31`,  
           count(iac.id) as total 
    FROM iac
    JOIN ioc on ioc.id = iac.ioc_id
    JOIN iet on iet.id = ioc.iet_id
    JOIN iw  on  iw.id = iac.iw_id
    WHERE iac.created_at >= date('2013-08-25') and iac.created_at < date('2013-09-01')
    GROUP BY iw.id, iet.id
    ORDER BY iw.display_name, iet.type;
    

    I also replaced the single quotes in the column aliases with back quotes. You should only use single quotes for string constants.

    I also replaced the date logic with inequalities. This removes the function call (date()) around the column, making it more likely that an index would be used for this filtering.

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(1条)

报告相同问题?

悬赏问题

  • ¥15 求差集那个函数有问题,有无佬可以解决
  • ¥15 MATLAB动图问题
  • ¥15 【提问】基于Invest的水源涵养
  • ¥20 微信网友居然可以通过vx号找到我绑的手机号
  • ¥15 寻一个支付宝扫码远程授权登录的软件助手app
  • ¥15 解riccati方程组
  • ¥15 display:none;样式在嵌套结构中的已设置了display样式的元素上不起作用?
  • ¥15 使用rabbitMQ 消息队列作为url源进行多线程爬取时,总有几个url没有处理的问题。
  • ¥15 Ubuntu在安装序列比对软件STAR时出现报错如何解决
  • ¥50 树莓派安卓APK系统签名