我有一个这样的数据库：
id User_id Question_id Answer
1 john Question_1 b
2 john Question_2 a
3 john Question_3 d
4 harry Question_1 a
5 harry Question_2 a
6 harry Question_3 c
7 colleen Question_1 c
8 colleen Question_2 a
9 colleen Question_3 b
我想以以下格式显示上述数据的内容-
id User_id Question_1 Question_2 Question_3
1 john b a d
2 harry a a c
4 colleen c a b
如何使用sql实现这一点？我使用的是mysql和php。
您正在寻找PIVOT表：
http://en.wikibooks.org/wiki/MySQL/Pivot_table
SELECT
id,
User_id,
MAX(IF(querstion_id = 'Question_1', answer, NULL)) AS Question_1,
MAX(IF(querstion_id = 'Question_2', answer, NULL)) AS Question_2,
MAX(IF(querstion_id = 'Question_3', answer, NULL)) AS Question_3
FROM answers
GROUP BY User_id
编辑1
添加了php版本，请参阅评论
$table = array();
$query = "SELECT * FROM answers ORDER BY User_id, Question_id";
... fetch data depending on what interface you use ...
foreach/while(....)
{
if(!isset($table[$result['User_id']]))
{
$table[$result['User_id']] = array();
$table[$result['User_id']]['id'] = $result['id'];
$table[$result['User_id']]['User_id'] = $result['User_id'];
}
$table[$result['User_id']][$result['Question_id']] = $result['Answer'];
}
编辑2如何显示：
然后像处理普通查询一样显示它，这就是我通常将php数组转换为html:的方式
echo '<table><thead>';
echo '<tr><th>' . implode('</th><th>', array_keys(current($table))) . '</th></tr>';
echo '</thead><tbody>';
foreach($table as $row)
{
echo '<tr><td>' . implode('</td><td>', $row) . '</td></tr>';
}
echo '</tbody></table>';
试试这个。。。
SELECT tbl.user_id,
(SELECT answer
FROM your_table
WHERE user_id = tbl.user_id AND question_id = 'Question_1') q1,
(SELECT answer
FROM your_table
WHERE user_id = tbl.user_id AND question_id = 'Question_2') q2,
(SELECT answer
FROM your_table
WHERE user_id = tbl.user_id AND question_id = 'Question_3') q3
FROM (SELECT DISTINCT user_id
FROM your_table) tbl
select id,User_id,
sum(Answer*(1-abs(sign(Question_id-1)))) as Question_1,
sum(Answer*(1-abs(sign(Question_id-2)))) as Question_2,
sum(Answer*(1-abs(sign(Question_id-3)))) as Question_3,
sum(Answer*(1-abs(sign(Question_id-4)))) as Question_4
from results group by User_id
如果你确定只有3个字段，你可以使用这样的东西。请记住，这个查询非常重，因此您可能更适合在PHP中处理它。。
select a.user_id,
a.question_id as Question_1,
b.question_id as Question_2,
c.question_id as Question_3
from TABLE_NAME a,
TABLE_NAME b,
TABLE_NAME c
where a.question_id="Question_1" and a.user in(select user_id from TABLE_NAME)
and b.question_id="Question_2" and b.user_id =a.user_id
and c.question_id="Question_3" and c.user_id =a.user_id