感谢您提前提供的所有帮助。
我在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
- SQLFiddle演示
如果您有未知数量的days
,则更倾向于使用动态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;
- SQLFiddle演示