MySQL数据透视-如何从同一表中获取数据到不同的列? [英] mysql pivoting - how can I fetch data from the same table into different columns?
本文介绍了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
这篇关于MySQL数据透视-如何从同一表中获取数据到不同的列?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文