sql查询结果的行记录转换为列属性

版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/Agly_Clarlie/article/details/87978445

sql查询结果的行记录转换为列属性

数据库查询结果后,某一列的结果意义是代表了不同的属性,根据不同的属性,进行转化为列。

实战1

转化前

mysql> select logid,game,account,platform from tbaccess_mgame limit 2;

+-------+------+------------------+----------+
| logid | game | account          | platform |
+-------+------+------------------+----------+
|     1 | game | 114++++++0292533 | 2        |
|     2 | game | 114++++++0292870 | 2        |
+-------+------+------------------+----------+

转化后

mysql>SELECT 
		access.game,access.platform, 
		CASE WHEN access.logid = 1 THEN access.account ELSE 0 END AS account1, 
		CASE WHEN access.logid = 2 THEN access.account ELSE 0 END AS account2
	FROM table access;

+------+----------+------------------+---------------------+
| game | platform | account1         | account2            |
+------+----------+------------------+---------------------+
| game | 2        | 114++++++0292533 | 1.14++++++029287e15 |
+------+----------+------------------+---------------------+

实战2

转化前

+------------+------+---------+----------+-------------+-------------+
| reg_dt     | game | channel | platform | duowanb_sum | pay_dt_type |
+------------+------+---------+----------+-------------+-------------+
| 2018-06-07 | game | -1      |       -1 |     1138.00 | sum1        |
| 2018-06-07 | game | -1      |       -1 |     1326.00 | sum2        |
| 2018-06-07 | game | -1      |       -1 |     1736.00 | sum3        |
| 2018-06-07 | game | -1      |       -1 |     1870.00 | sum4        |
| 2018-06-07 | game | -1      |       -1 |     2220.00 | sum5        |
| 2018-06-07 | game | -1      |       -1 |     2446.00 | sum6        |
| 2018-06-08 | game | -1      |       -1 |      470.00 | sum1        |
| 2018-06-08 | game | -1      |       -1 |      518.00 | sum2        |
| 2018-06-08 | game | -1      |       -1 |      530.00 | sum3        |
| 2018-06-08 | game | -1      |       -1 |      628.00 | sum4        |
| 2018-06-08 | game | -1      |       -1 |      634.00 | sum5        |
| 2018-06-09 | game | -1      |       -1 |      126.00 | sum1        |
| 2018-06-09 | game | -1      |       -1 |      174.00 | sum2        |
| 2018-06-09 | game | -1      |       -1 |      186.00 | sum3        |
+------------+------+---------+----------+-------------+-------------+

转化后

SELECT
	p.reg_dt,
	p.game,
	p.channel,
	p.platform, 
	SUM(CASE WHEN p.pay_dt_type = 'sum1' THEN p.duowanb_sum ELSE 0 END) AS duowanb_sum1, 
	SUM(CASE WHEN p.pay_dt_type = 'sum2' THEN p.duowanb_sum ELSE 0 END) AS duowanb_sum2, 
	SUM(CASE WHEN p.pay_dt_type = 'sum3' THEN p.duowanb_sum ELSE 0 END) AS duowanb_sum3, 
	SUM(CASE WHEN p.pay_dt_type = 'sum4' THEN p.duowanb_sum ELSE 0 END) AS duowanb_sum4, 
	SUM(CASE WHEN p.pay_dt_type = 'sum5' THEN p.duowanb_sum ELSE 0 END) AS duowanb_sum5, 
	SUM(CASE WHEN p.pay_dt_type = 'sum6' THEN p.duowanb_sum ELSE 0 END) AS duowanb_sum6, 
	SUM(CASE WHEN p.pay_dt_type = 'sum7' THEN p.duowanb_sum ELSE 0 END) AS duowanb_sum7
FROM TABLE p
GROUP BY reg_dt

+------------+------+---------+----------+--------------+--------------+--------------+--------------+--------------+--------------+--------------+
| reg_dt     | game | channel | platform | duowanb_sum1 | duowanb_sum2 | duowanb_sum3 | duowanb_sum4 | duowanb_sum5 | duowanb_sum6 | duowanb_sum7 |
+------------+------+---------+----------+--------------+--------------+--------------+--------------+--------------+--------------+--------------+
| 2018-06-07 | game | -1      |       -1 |      1138.00 |      1326.00 |      1736.00 |      1870.00 |      2220.00 |      2446.00 |         0.00 |
| 2018-06-08 | game | -1      |       -1 |       470.00 |       518.00 |       530.00 |       628.00 |       634.00 |         0.00 |         0.00 |
| 2018-06-09 | game | -1      |       -1 |       126.00 |       174.00 |       186.00 |       186.00 |         0.00 |         0.00 |         0.00 |
+------------+------+---------+----------+--------------+--------------+--------------+--------------+--------------+--------------+--------------+

没有更多推荐了,返回首页