MySQL数据透视查询 [英] MySQL pivot query

问题描述

我曾经有一个查询

SELECT ps_target_ecpm, ps_actual_ecpm
FROM publisher_stats
JOIN domain ON domain.dmn_id = ps_dmn_id
LEFT JOIN langue ON langue.lng_id = domain.default_lng_id
WHERE MONTH(ps_month) = 05 

我需要的结果应类似于

may_target_ecmp，may_actual_ecpm，pril_target_ecpm， april_actual_ecpm，march_target_ecpm，march_actual_ecpm.

may_target_ecmp, may_actual_ecpm, april_target_ecpm, april_actual_ecpm, march_target_ecpm, march_actual_ecpm.

4月MONTH(ps_month)= 04，3月MONTH(ps_month)= 03 分别.

For april MONTH(ps_month) = 04 and for march MONTH(ps_month) = 03 respectively.

经过一番询问后，我最终得到了一个类似这样的查询

After some questioning around I ended up with a query that looks like this

SELECT
(CASE WHEN MONTH(ps_month) = 4 THEN ps_target_ecpm ELSE 0 END) AS april_target_ecpm, 
(CASE WHEN MONTH(ps_month) = 4 THEN ps_actual_ecpm ELSE 0 END) AS april_actual_ecpm,
(CASE WHEN MONTH(ps_month) = 3 THEN ps_target_ecpm ELSE 0 END) AS march_target_ecpm, 
(CASE WHEN MONTH(ps_month) = 3 THEN ps_actual_ecpm ELSE 0 END) AS march_actual_ecpm 
FROM publisher_stats
JOIN domain ON domain.dmn_id = ps_dmn_id
LEFT JOIN langue ON langue.lng_id = domain.default_lng_id

我得到的结果集不是我所需要的. 示例响应为:

The resultset I get is not quite what I need. The example response is:

0           0       0.48    0.27
0.48        0.47    0       0

我需要将它排成一排

0.48    0.47    0.48    0.27

请帮助我找出如何使此查询达到预期目的的方法. 预先感谢

Could you please help me to figure out how to make this query do what it is intended to. Thanks in advance

P.S.这个问题来自这个问题-

P.S. This question come all the way from this question - mysql pivoting - how can I fetch data from the same table into different columns?

推荐答案

只需使用聚合函数，例如MAX即可正常工作，但如果需要获取每个函数的总数，则可能需要使用SUM (如果每个月有多个ps_target_ecpm条目).像这样:

Just use an aggregate function, MAX for example will work fine, but you might need to use SUM if you need to get the total for each month, if there are multiple entries for ps_target_ecpm for each month. Like this:

SELECT
  MAX(CASE WHEN MONTH(ps_month) = 4 THEN ps_target_ecpm ELSE 0 END) AS april_target_ecpm, 
  MAX(CASE WHEN MONTH(ps_month) = 4 THEN ps_actual_ecpm ELSE 0 END) AS april_actual_ecpm,
  MAX(CASE WHEN MONTH(ps_month) = 3 THEN ps_target_ecpm ELSE 0 END) AS march_target_ecpm, 
  MAX(CASE WHEN MONTH(ps_month) = 3 THEN ps_actual_ecpm ELSE 0 END) AS march_actual_ecpm 
FROM publisher_stats
JOIN domain ON domain.dmn_id = ps_dmn_id
LEFT JOIN langue ON langue.lng_id = domain.default_lng_id

这篇关于MySQL数据透视查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！