我可以生成以下输出:
+----------+------------+------------+------------+
| startt | 2013-04-01 | 2013-04-02 | 2013-04-03 |
+----------+------------+------------+------------+
| 08:00:00 | Donald | Daisy | Mickey |
| 12:00:00 | Pluto | Goofy | Minnie |
| 14:00:00 | NULL | Mickey | NULL |
+----------+------------+------------+------------+
从这个原始数据:
mysql> select * from test;
+------------+----------+----------+--------+
| startd | startt | duration | name |
+------------+----------+----------+--------+
| 2013-04-01 | 08:00:00 | 4 | Donald |
| 2013-04-02 | 08:00:00 | 4 | Daisy |
| 2013-04-03 | 08:00:00 | 4 | Mickey |
| 2013-04-03 | 12:00:00 | 4 | Minnie |
| 2013-04-01 | 12:00:00 | 4 | Pluto |
| 2013-04-02 | 12:00:00 | 4 | Goofy |
| 2013-04-02 | 14:00:00 | 4 | Mickey |
+------------+----------+----------+--------+
mysql>
使用此MySQL动态查询:
1 set @sql = null;
2 select
3 group_concat(distinct
4 concat(
5 'group_concat(case when startd = ''',
6 `startd`,
7 ''' then `name` end ) as `',
8 `startd`,'`'
9 )
10 ) into @sql
11 from test;
12
13 set @sql = concat('select startt, ',@sql,'
14 from test
15 group by startt');
16
17 prepare stmt from @sql;
18 execute stmt;
19 deallocate prepare stmt;
感谢你在这一点上的帮助@hims056。
如何将这个动态查询的结果传递给一个可以在PHP中循环的变量?
过去我使用过:
$result=mysqli_query($con,"select ...");
... lines deleted ...
while ($row=mysqli_fetch_array($result))
... lines deleted ...
在这种情况下,这种方法似乎不合适。
如有任何协助,我们将不胜感激。
一个可能的解决方案是将其封装在存储过程中
DELIMITER $$
CREATE PROCEDURE sp_test()
BEGIN
SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'GROUP_CONCAT(CASE WHEN startd = ''',
`startd`,
''' THEN `name` END ) AS `',
`startd`,'`'
)
) INTO @sql
FROM test;
SET @sql = CONCAT('SELECT startt, ', @sql, '
FROM test
GROUP BY startt');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;
并使用
CALL sp_test();
这是SQLFiddle演示
更新:在php端,您可以执行
$db = new mysqli('localhost', 'user', 'password', 'dbname');
$sql = "CALL sp_test()";
$query = $db->query($sql);
$result = array();
while ($row = $query->fetch_assoc()) {
$result[] = $row;
}
$query->close();
$db->close();
// then do whatever you need to do to present it
var_dump($result);
为简洁起见,有意省略了所有错误处理