gpt4 book ai didi

php - SQL查询: Rows to Columns Pivot into PHP Array/HTML Output

转载 作者:行者123 更新时间:2023-11-29 14:01:11 25 4
gpt4 key购买 nike

感谢您提前提供的所有帮助。

我在 MySQL PHPmyAdmin 中有一个名为“data”的表,如下所示;

Primary | Date| User | Response
1 Mon Tom Yes
2 Mon Terry No
3 Mon Lucas Yes
4 Tue Tom No
5 Tue Terry No
6 Tue Lucas Yes
7 Wed Tom Yes
8 Wed Terry Yes
9 Wed Lucas No

我如何通过最有可能的 SQL 查询或 Pivot/php 数组输出到表中,为 HTML 表输出做好准备;

Date | Tom | Terry | Lucas
Mon Yes No Yes
Tue No No Yes
Wed Yes Yes No

最佳答案

SELECT  Date,
MAX(CASE WHEN user = 'Tom' THEN Response ELSE NULL END) Tom,
MAX(CASE WHEN user = 'Terry' THEN Response ELSE NULL END) Terry ,
MAX(CASE WHEN user = 'Lucas' THEN Response ELSE NULL END) Lucas
FROM tableName
GROUP BY Date

如果您的天数未知,则更首选动态sql,

SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'MAX(case when user = ''',
user,
''' then Response end) AS ',
user
)
) INTO @sql
FROM tableName;

SET @sql = CONCAT('SELECT Date, ', @sql, '
FROM tableName
GROUP BY Date');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

关于php - SQL查询: Rows to Columns Pivot into PHP Array/HTML Output,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/15028124/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com