如何在Mysql中的Group_Concat中使用if语句
问题描述:
在我的头撞到墙上之后,我试图使用此示例表内容获取数据透视数据。如何在Mysql中的Group_Concat中使用if语句
id--------------lastname-----total----rd
0511-02-0318 Salvador 8.99 2015-09-08
0214-02-0616 Alfaro 0 2015-09-08
0214-02-0616 Alfaro 7.95 2015-09-07
0214-02-0616 Alfaro 8.69 2015-09-04
0214-02-0616 Alfaro 6.81 2015-09-01
0214-02-0616 Alfaro 8.86 2015-08-29
0214-02-0616 Alfaro 6.16 2015-08-28
0214-02-0616 Alfaro 8.66 2015-08-27
0214-02-0616 Alfaro 8.03 2015-08-26
0214-02-0616 Alfaro 8.68 2015-08-25
0214-02-0616 Alfaro 8.75 2015-08-24
0511-02-0318 Salvador 9.17 2015-09-09
0511-02-0318 Salvador 9.28 2015-09-10
0415-02-0960 Martin 0 2015-09-18
0415-02-0960 Martin 10.39 2015-09-17
0415-02-0960 Martin 8.44 2015-09-16
0415-02-0960 Martin 10.9 2015-09-15
0415-02-0960 Martin 10.91 2015-09-14
0415-02-0960 Martin 9.91 2015-09-11
0415-02-0960 Martin 10.31 2015-09-10
0415-02-0960 Martin 11.16 2015-09-09
0415-02-0960 Martin 12.04 2015-09-08
0415-02-0960 Martin 9.42 2015-09-07
0415-02-0960 Martin 10.43 2015-09-04
0415-02-0960 Martin 10.39 2015-09-03
这是我工作的学尝试查询,但我想我错过了一些东西:
SET @sql = NULL;
set @sdate = '2015-09-01';
set @edate = '2015-09-06';
SELECT GROUP_CONCAT(
distinct concat('total=',list_of_dates)
/*distinct concat('if(p.rd=''',list_of_dates,''',p.total',0,')')*/
) INTO @sql from
(SELECT ADDDATE(date(@sdate), INTERVAL @i:[email protected]+1 DAY) AS list_of_dates
FROM (
SELECT a.a
FROM (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS a
CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS b
CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS c
) a
JOIN (SELECT @i := -1) r1
WHERE
@i < DATEDIFF(date(@edate),date(@sdate))) as a;
set @sql = concat('select id, lastname, total, rd,',@sql,' from
(select ''0511-02-0318'' as id, ''Salvador'' as lastname, 8.99 as total, ''2015-09-08'' as rd union all
select ''0214-02-0616'', ''Alfaro'', 0 ,''2015-09-08'' union all
select ''0214-02-0616'', ''Alfaro'', 7.95, ''2015-09-07'' union all
select ''0214-02-0616'', ''Alfaro'', 8.69, ''2015-09-04'' union all
select ''0214-02-0616'', ''Alfaro'', 6.81, ''2015-09-01'' union all
select ''0214-02-0616'', ''Alfaro'', 8.86, ''2015-08-29'' union all
select ''0214-02-0616'', ''Alfaro'', 6.16, ''2015-08-28'' union all
select ''0214-02-0616'', ''Alfaro'', 8.66, ''2015-08-27'' union all
select ''0214-02-0616'', ''Alfaro'', 8.03, ''2015-08-26'' union all
select ''0214-02-0616'', ''Alfaro'', 8.68, ''2015-08-25'' union all
select ''0214-02-0616'', ''Alfaro'', 8.75, ''2015-08-24'' union all
select ''0511-02-0318'', ''Salvador'', 9.28, ''2015-09-10'' union all
select ''0415-02-0960'', ''Martin'', 0, ''2015-09-18'' union all
select ''0415-02-0960'', ''Martin'', 10.39, ''2015-09-17'' union all
select ''0415-02-0960'', ''Martin'', 8.44, ''2015-09-16'' union all
select ''0415-02-0960'', ''Martin'', 10.9, ''2015-09-15'' union all
select ''0415-02-0960'', ''Martin'', 10.91, ''2015-09-14'' union all
select ''0415-02-0960'', ''Martin'', 9.91, ''2015-09-11'' union all
select ''0415-02-0960'', ''Martin'', 10.31, ''2015-09-10'' union all
select ''0415-02-0960'', ''Martin'', 11.16, ''2015-09-09'' union all
select ''0415-02-0960'', ''Martin'', 12.04, ''2015-09-08'' union all
select ''0415-02-0960'', ''Martin'', 9.42, ''2015-09-07'' union all
select ''0415-02-0960'', ''Martin'', 10.43, ''2015-09-04'' union all
select ''0415-02-0960'', ''Martin'', 10.39, ''2015-09-03'')data');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
这里是我的尝试输出:
我想要的输出,如果pivot date
是等于rd
那么枢纽date content
就变成了value
的total
,但如果不相等,则值变为0.还有id and lastname become distinct
也与关键日期total=
应该删除。
我试过这个示例注释的代码,但没有运气。
这一个工作,但它只返回一行。
GROUP_CONCAT(DISTINCT CONCAT("SUM(IF(data.rd='",DATE(list_of_dates),"',`total`,0)) AS `",list_of_dates,"`")) as `dates`
任何帮助表示赞赏。
在此先感谢
答
UPDATE:我改变部分代码的命令
我开始通过创建一个select
声明产生最终结果的日期子合计列,然后写代码来生成select
语句。我还创建了一个SQL Fiddle来生成声明和结果。
而不是伪造过程中的数据,我创建了一个真正的表。您将不得不修改代码以适应您的特定需求,因为我没有您要使用的表格的名称。
这工作分几个步骤:
- 设置参数(开始/结束日期)
- 获取从指定的范围的数据日期的列表。
- 生成SQL语句。
- 执行SQL语句。
这里是我写的模型测试SQL语句:
SELECT
`id`,
`lastname`,
SUM(`total`) AS `total`,
SUM(if(`rd` = '2015-09-01',`total`,0)) AS `total:2015-09-01`,
SUM(if(`rd` = '2015-09-02',`total`,0)) AS `total:2015-09-02`,
SUM(if(`rd` = '2015-09-03',`total`,0)) AS `total:2015-09-03`,
SUM(if(`rd` = '2015-09-04',`total`,0)) AS `total:2015-09-04`,
SUM(if(`rd` = '2015-09-05',`total`,0)) AS `total:2015-09-05`,
SUM(if(`rd` = '2015-09-06',`total`,0)) AS `total:2015-09-06`
FROM `test_table`
WHERE `rd` BETWEEN @sdate AND @edate
GROUP BY `id`,`lastname`
ORDER BY `id`,`lastname`
而这里的代码来生成并运行该SQL语句:
/* Set the parameters */
SET @sql = NULL;
SET @dates = NULL;
SET @sdate = '2015-09-01';
SET @edate = '2015-09-06';
/* Get a list of dates, and format as the column def for the SQL SELECT */
SELECT
GROUP_CONCAT(CONCAT("SUM(IF(`rd` = '",DATE(`rd`),"',`total`,0)) AS `total:",DATE(`rd`),"`")) as `dates`
FROM (SELECT `rd` FROM `test_table` GROUP BY `rd` ORDER BY `rd`) AS a
WHERE a.`rd` BETWEEN @sdate AND @edate
INTO @dates;
/* Generate the SQL SELECT */
SELECT CONCAT("SELECT `id`,`lastname`,SUM(`total`) AS `total`,",@dates," FROM `test_table` WHERE `rd` BETWEEN @sdate AND @edate GROUP BY `id`,`lastname` ORDER BY `id`,`lastname`")
INTO @sql;
/* Execute the SELECT */
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
是在一个表中的数据?在你的代码中,你似乎在生成数据而不是从表中读取数据。桌子的名字是什么? –
我正在准备要更新的数据,但它来自我的表,它是一样的..你可以随时运行查询。没有任何需要的表格。 – redzsol
@redzol:这是否适合你?如果是这样,请标记为已接受并投票。如果没有,让我知道你有什么错误,或者什么都不起作用。 –