mysql pivoting - 如何将同一张表中的数据提取到不同的列中?

2022-01-22 00:00:00 pivot mysql

我有一个看起来像这样的查询

I have a query that looks like this

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

我需要的结果集应该包括这样的字段:

The result set I need should include fields like this:

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

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

我尝试了联合、子查询等 - 仍然没有成功.到目前为止，我的实验使我做到了这一点，但显然这不是所需要的

I tried union, subqueries and so on - still no success. My experiments lead me to this so far, but obviously it is not what needed

SELECT a.ps_dmn_id, a.ps_actual_ecpm AS mayecmp, b.ps_actual_ecpm AS aprilecpm
FROM
(
    SELECT *
    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 
) AS a,
(
    SELECT *
    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) = 04
) AS b

GROUP by ps_dmn_id

完成此任务的正确查询是什么?

What would be the right query to accomplish this?

推荐答案

你可以试试这个

SELECT ( CASE WHEN ps_month = '04' THEN ps_target_ecpm
              ELSE 0
         END ) AS april_target_ecmp
       ,( CASE WHEN ps_month = '04' THEN ps_actual_ecpm
               ELSE 0
          END ) AS april_actual_ecpm
       ,( CASE WHEN ps_month = '03' THEN ps_target_ecpm
               ELSE 0
          END ) AS march_target_ecmp
       ,( CASE WHEN ps_month = '03' 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

相关文章