在MySQL中使用函数创建列别名? [英] creating column alias using function in mysql?
问题描述
我有这张桌子
sales(productid, sales_date)
我想获取最近12个月的月销售量.
I want to get the monthly sales count for last 12 month.
如何在列别名上生成月份名称(无需硬编码)?
How can I generate the month name on the column alias (without hard coding)?
我尝试过这个:-
http://sqlfiddle.com/#!2/ee777/9
但是我希望结果表采用这种格式
But I want the result table in this format
productid Oct-12 Nov-12 Dec-12 Jan-13 Feb-13 Mar-13 Apr-13 May-13
1 0 0 3 4 2 8 2 11
2 0 5 6 8 2 0 0 0
我尝试使用函数 DATE_FORMAT(sales_date,'%M')
为列赋予别名,但这是语法错误.
I tried to give the column alias using function DATE_FORMAT(sales_date, '%M')
but it is
syntax error.
select productid,sum(if(DATE_FORMAT(sales_date,'%Y%m')=
DATE_FORMAT(date_sub(curdate(),interval 12 month),'%Y%m'),1,0))
`12 Months Before`,
sum(if(DATE_FORMAT(sales_date,'%Y%m')=
DATE_FORMAT(date_sub(curdate(),interval 11 month),'%Y%m'),1,0))
`11 Months Before`,
sum(if(DATE_FORMAT(sales_date,'%Y%m')=
DATE_FORMAT(date_sub(curdate(),interval 10 month),'%Y%m'),1,0))
`10 Months Before`,
sum(if(DATE_FORMAT(sales_date,'%Y%m')=
DATE_FORMAT(date_sub(curdate(),interval 9 month),'%Y%m'),1,0))
`9 Months Before`,
sum(if(DATE_FORMAT(sales_date,'%Y%m')=
DATE_FORMAT(date_sub(curdate(),interval 8 month),'%Y%m'),1,0))
`8 Months Before`,
sum(if(DATE_FORMAT(sales_date,'%Y%m')=
DATE_FORMAT(date_sub(curdate(),interval 7 month),'%Y%m'),1,0))
`7 Months Before`,
sum(if(DATE_FORMAT(sales_date,'%Y%m')=
DATE_FORMAT(date_sub(curdate(),interval 6 month),'%Y%m'),1,0))
`6 Months Before`,
sum(if(DATE_FORMAT(sales_date,'%Y%m')=
DATE_FORMAT(date_sub(curdate(),interval 5 month),'%Y%m'),1,0))
`5 Months Before`,
sum(if(DATE_FORMAT(sales_date,'%Y%m')=
DATE_FORMAT(date_sub(curdate(),interval 4 month),'%Y%m'),1,0))
`4 Months Before`,
sum(if(DATE_FORMAT(sales_date,'%Y%m')=
DATE_FORMAT(date_sub(curdate(),interval 3 month),'%Y%m'),1,0))
`3 Months Before`,
sum(if(DATE_FORMAT(sales_date,'%Y%m')=
DATE_FORMAT(date_sub(curdate(),interval 2 month),'%Y%m'),1,0))
`2 Months Before`,
sum(if(DATE_FORMAT(sales_date,'%Y%m')=
DATE_FORMAT(date_sub(curdate(),interval 1 month),'%Y%m'),1,0))
`1 Months Before`
from sales
group by productid;
如何获取月份和年份的别名(例如:-Oct-12),以代替 12个月之前
, 11个月之前
等.>
How can I get month and year as alias (eg:- Oct-12) in place of 12 Months Before
, 11 Months Before
and so on.
推荐答案
MySql没有 PIVOT
,因此您必须使用条件 SUM()
.
MySql doesn't have PIVOT
so you have to use conditional SUM()
.
有两种选择:
- 如果周期数是有限的并且事先已知,则您可以手动执行
- 您可以使用动态SQL即时生成查询,然后执行该查询
对option1的查询可能如下所示
A query for option1 might look like this
SELECT productid
,SUM(CASE WHEN DATE_FORMAT(sales_date, '%Y%m') = '201210' THEN 1 ELSE 0 END) `Oct12`
,SUM(CASE WHEN DATE_FORMAT(sales_date, '%Y%m') = '201211' THEN 1 ELSE 0 END) `Nov12`
,SUM(CASE WHEN DATE_FORMAT(sales_date, '%Y%m') = '201212' THEN 1 ELSE 0 END) `Dec12`
,SUM(CASE WHEN DATE_FORMAT(sales_date, '%Y%m') = '201301' THEN 1 ELSE 0 END) `Jan13`
,SUM(CASE WHEN DATE_FORMAT(sales_date, '%Y%m') = '201302' THEN 1 ELSE 0 END) `Feb13`
,SUM(CASE WHEN DATE_FORMAT(sales_date, '%Y%m') = '201303' THEN 1 ELSE 0 END) `Mar13`
,SUM(CASE WHEN DATE_FORMAT(sales_date, '%Y%m') = '201304' THEN 1 ELSE 0 END) `Apr13`
,SUM(CASE WHEN DATE_FORMAT(sales_date, '%Y%m') = '201305' THEN 1 ELSE 0 END) `May13`
,SUM(CASE WHEN DATE_FORMAT(sales_date, '%Y%m') = '201306' THEN 1 ELSE 0 END) `Jun13`
FROM sales
WHERE sales_date BETWEEN '2012-10-01' AND '2013-06-30'
GROUP BY productid
示例输出:
| PRODUCTID | OCT12 | NOV12 | DEC12 | JAN13 | FEB13 | MAR13 | APR13 | MAY13 | JUN13 |
-------------------------------------------------------------------------------------
| 1 | 0 | 0 | 0 | 1 | 1 | 0 | 0 | 2 | 1 |
| 2 | 1 | 1 | 1 | 0 | 0 | 1 | 1 | 1 | 2 |
这里是 SQLFiddle 演示
现在是使用动态SQL的选项2的版本(
PREPARE
, EXECUTE
)
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'SUM(CASE WHEN DATE_FORMAT(sales_date, ''%Y%m'') = ''',
DATE_FORMAT(period, '%Y%m'), ''' THEN 1 ELSE 0 END) `', DATE_FORMAT(period, '%b%y'), '`'
)
) INTO @sql
FROM
(
SELECT DATE_FORMAT(sales_date, '%Y-%m-01') period
FROM sales
WHERE sales_date BETWEEN DATE_FORMAT(CURDATE() - INTERVAL 11 MONTH, '%Y-%m-01') AND CURDATE()
GROUP BY DATE_FORMAT(sales_date, '%Y-%m-01')
) s;
SET @sql = CONCAT
('SELECT productid, ', @sql, '
FROM sales
WHERE sales_date BETWEEN DATE_FORMAT(CURDATE() - INTERVAL 11 MONTH, ''%Y-%m-01'') AND CURDATE()
GROUP BY productid'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
输出是相同的.
| PRODUCTID | JUL12 | AUG12 | SEP12 | OCT12 | NOV12 | DEC12 | JAN13 | FEB13 | MAR13 | APR13 | MAY13 | JUN13 |
-------------------------------------------------------------------------------------------------------------
| 1 | 1 | 2 | 1 | 0 | 0 | 0 | 1 | 1 | 0 | 0 | 2 | 1 |
| 2 | 0 | 0 | 0 | 1 | 1 | 1 | 0 | 0 | 1 | 1 | 1 | 2 |
这里是 SQLFiddle 演示
Here is SQLFiddle demo
这篇关于在MySQL中使用函数创建列别名?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!