php - MySQL Query以某种方式显示信息

Question

我在尝试执行查询以某种方式显示结果时遇到困难。我需要的是在每个用户的一行中显示所有用户答案，我无法连接所有答案，因为我的程序需要访问每个答案的结果以及我所理解的串联将返回一个字符串。

表格是这样定义的:

这是存储用户信息的地方。用户

+------+---------+-------------+
| u_id | email   | name        |
+------+---------+-------------+
|    1 | bob@b   | bob         |
|    2 | r@e.com | sam         |
|    3 | ra@se   | steve       |
|    4 | tv@we   | rob         |
|    5 | tr@sd   | ted         |
|    6 | qw@as   | john        |
+------+---------+-------------+

Questions
+------+---------+-------------+
| q_id | question_name         |
+------+---------+-------------+
|    1 | Age range?            |
|    2 | Do you use Amazon?    |
|    3 | Do you use Facebook?  |
|    4 | Interested in toys?   |
+------+---------+-------------+

这些是用户可以选择的可能答案。

Option_Choices
+------+---------+-------------+
|oc_id | opt_choice_name       |
+------+---------+-------------+
|    1 | YES                   |
|    2 | NO                    |
|    3 | 18-24 Years           |
|    4 | 25-35 Years           |
|    5 | Very Interested       |
|    6 | Not Interested        |
+------+---------+-------------+

这是一个与所有可能相关的问题回答那个具体问题。

Question_Options
+------+---------+-------------+
|qo_id | q_id    | oc_id       |
+------+---------+-------------+
|    1 | 1       | 3           | // Age range?: 18-24
|    2 | 1       | 4           | // Age range?: 25-35
|    3 | 2       | 1           | // Do you use Amz? Yes
|    4 | 2       | 2           | ...
|    5 | 3       | 1           | ...
|    6 | 3       | 2           | ...
|    7 | 4       | 5           | ...
|    8 | 4       | 6           |
+------+---------+-------------+

这里存储了每个用户给出的答案。

Answers
+------+---------+-------------+
| a_id | u_id    | qo_id       |
+------+---------+-------------+
|    1 | 1       | 2           |
|    2 | 1       | 4           |
|    3 | 1       | 6           |
|    4 | 1       | 7           |
|    1 | 2       | 1           |
|    2 | 2       | 3           |
|    3 | 2       | 6           |
|    4 | 2       | 8           |
|    1 | 3       | 2           |
|    2 | 3       | 4           |
|    3 | 3       | 5           |
|    4 | 3       | 8           |
+------+---------+-------------+

我现在需要的是建立一个查询来显示信息以类似的方式:

+---------+-------------+-------------+-------------+---------------------+
| email   | Age range?  | Use Amazon? | Use FB?     | Interested in toys? |
+---------+-------------+-------------+-------------+---------------------+
| bob@b   | 18-24 Years |    YES      |    NO       | Very Interested     |
| r@e.com | 25-35 Years |    NO       |    YES      | Not Interested      |
| ra@se   | 25-35 Years |    NO       |    YES      | Not Interested      |
| tv@we   | 18-24 Years |    YES      |    YES      | Not Interested      |
| tr@sd   | 18-24 Years |    YES      |    NO       | Not Interested      |
| qw@as   | 25-35 Years |    YES      |    YES      | Very Interested     |
+------+---------+--------------------+-------------+---------------------+

但仅使用 JOIN 我会得到类似这样的结果"

+---------+----------------+-----------------+
| email   | question_name  | opt_choice_name |
+---------+----------------+-----------------+
| bob@b   | Age range?     |  18-24 Years    |
| bob@b   | Use Amazon?    |    YES          |
| bob@b   | Use FB?        |    NO           |
| bob@b   | Inter in toys? |Very Interested  |
| r@e.com | Age range?     |  25-35 Years    |
| r@e.com | Use Amazon?    |    NO       |
| r@e.com | Use FB?        |    YES          |
| r@e.com | Inter in toys? | Not Interested  |

   ...         ...               ...
+---------+----------------+-----------------+

我尝试将数据透视表与这样的查询一起使用:

SELECT u.email,
(CASE q.question_name WHEN q_id = 1 THEN oc.opt_choice_name ELSE 0) AS 'Age_Range',
(CASE q.question_name WHEN q_id = 2 THEN oc.opt_choice_name ELSE 0) AS 'Amazon',
(CASE q.question_name WHEN q_id = 3 THEN oc.opt_choice_name ELSE 0) AS 'FB',
(CASE q.question_name WHEN q_id = 4 THEN oc.opt_choice_name ELSE 0) AS 'Toys'
FROM Users u
INNER JOIN Ansers a ON u.u_id = a.u_id
INNER JOIN Question_Options qo ON a.qo_id = qo.qo_id
INNER JOIN Questions q ON qo.q_id = q.q_id
INNER JOIN Option_Choices oc ON qo.oc_id = oc.oc_id
GROUP BY u.email

但我得到的结果好坏参半。

任何建议将不胜感激。

谢谢。

我试过使用 MAX

SELECT u.email,
MAX( CASE q.question_name WHEN q.question_id = 1 THEN oc.option_choice_name ELSE 0 END) AS 'AgeRange',
MAX( CASE q.question_name WHEN q.question_id = 2 THEN oc.option_choice_name ELSE 0 END) AS 'Amazon',
MAX( CASE q.question_name WHEN q.question_id = 3 THEN oc.option_choice_name ELSE 0 END) AS 'FB',
MAX( CASE q.question_name WHEN q.question_id = 4 THEN oc.option_choice_name ELSE 0 END) AS 'toys',
FROM Users u
INNER JOIN Answers a ON u.u_id = a.u_id
INNER JOIN Question_Options qo ON a.qo_id = qo.qo_id
INNER JOIN Questions q ON qo.q_id = q.q_id
INNER JOIN Option_Choices oc ON qo.oc_id = oc.oc_id
GROUP BY u.email

但我得到的结果是这样的:

+---------+-------------+-------------+-------------+---------------------+
| email   | Age range?  | Use Amazon? | Use FB?     | Interested in toys? |
+---------+-------------+-------------+-------------+---------------------+
| bob@b   | YES         |    YES      |    NO       |          NO         |
| r@e.com | YES         |    YES      |    YES      | YES                 |
| ra@se   | YES         |    YES      |    YES      | YES                 |
| tv@we   | YES         |    YES      |    YES      | YES                 |
+------+---------+--------------------+-------------+---------------------+

Answer 1

虽然您可以使用 MySQL 执行此操作，但效率不高，因为 MySQL 索引的是行而不是列。

Indexes are used to find rows with specific column values quickly. Without an index, MySQL must begin with the first row and then read through the entire table to find the relevant rows. The larger the table, the more this costs.

http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html

大多数人处理这个问题的方式是用 JOIN 枚举所有问题，就像您拥有的那样:

+---------+----------------+-----------------+
| email   | question_name  | opt_choice_name |
+---------+----------------+-----------------+
| bob@b   | Age range?     |  18-24 Years    |
| bob@b   | Use Amazon?    |    YES          |
| bob@b   | Use FB?        |    NO           |
| bob@b   | Inter in toys? |Very Interested  |
| r@e.com | Age range?     |  25-35 Years    |
| r@e.com | Use Amazon?    |    NO       |
| r@e.com | Use FB?        |    YES          |
| r@e.com | Inter in toys? | Not Interested  |

   ...         ...               ...
+---------+----------------+-----------------+

然后在您的 PHP 中您将执行类似(免责声明:未经测试的代码)的操作:

$i = 0;
echo "<table><tr><th>Age range?</th><th>Use Amazon?</th><th>Use Facebook?</th><th>Interested in Toys?</th></tr><tr>";
foreach ($results as $column) {
    echo "<td>".$column."</td>"; 
    if ($i != 0 && $i % 4) // Just arbitrarily chose 4, but it would be however many columns you have
        echo "</tr><tr>";

    $i++;
}
echo "</tr></table>";