以某种方式显示信息的MySQL查询
以某种方式显示信息的MySQL查询
提问于 2013-12-27 15:58:09
我很难尝试以某种方式进行查询以显示结果。我需要的是在每个用户的一行中显示所有的用户答案，我不能连接所有的答案，因为我的程序需要访问每个答案的结果，并且我理解的连接将返回一个字符串。
表是这样定义的：
这里是存储用户信息的地方。用户
+------+---------+-------------+
| 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 |
+------+---------+--------------------+-------------+---------------------+但只要加入我就能得到这样的东西“
+---------+----------------+-----------------+
| 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 |
... ... ...
+---------+----------------+-----------------+我尝试通过这样的查询使用pivots：
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 |
+------+---------+--------------------+-------------+---------------------+Stack Overflow用户
发布于 2013-12-27 16:38:25
解决这一问题的另一种方法是为每个问题分别创建临时表，最后创建一个mysql查询，以便一次水平地获取所有数据。
http://www.tutorialspoint.com/mysql/mysql-temporary-tables.htm
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/20803657
复制相关文章
相似问题
腾讯云开发者
