编写 SQL 子查询时出现问题

发布于 2024-09-11 16:24:30 字数 832 浏览 2 评论 0原文

我知道我一定错过了一些简单的东西...但是我在编写 SQL 子查询时遇到了问题。给定以下数据,

user_id question_id answer_text
89          1           value1
89          2           value2
80          2           value2
99          2           value2
96          1           value1
96          2           value2
111        1           value1
111        2           value2

我需要获取同时具有 Question_id 1 = 'value1' 和 Question_id 2 = 'value2' 的 user_id

上面的结果是使用此查询生成的:

SELECT `User_answer`.`user_id`, `User_answer`.`question_id`, `User_answer`.`answer_text` FROM `user_answers` AS `User_answer` WHERE `User_answer`.`question_id` IN (1, 2) AND `User_answer`.`answer_text` IN ('value1', 'value2')

但是当然,这会返回使用“value1”回答问题 1 的用户”但问题 2 的答案与“value2”完全不同(反之亦然)。

我需要能够添加更多条件,但我想如果我至少能让这两个条件正常工作,我可以添加这一点。

I know I must be missing something simple here...but I'm having problems with writing an SQL sub query. Given the following data

user_id question_id answer_text
89          1           value1
89          2           value2
80          2           value2
99          2           value2
96          1           value1
96          2           value2
111        1           value1
111        2           value2

I need to get the user_id's that have BOTH question_id 1 = 'value1' AND have question_id 2 = 'value2'

The results above are generated using this query:

SELECT `User_answer`.`user_id`, `User_answer`.`question_id`, `User_answer`.`answer_text` FROM `user_answers` AS `User_answer` WHERE `User_answer`.`question_id` IN (1, 2) AND `User_answer`.`answer_text` IN ('value1', 'value2')

but of course this returns users that have answered question 1 with "value1" but question 2 with a completely different answer than "value2" (and vice versa).

I need to be able to add in more conditions but I think I can add that if I can get at least these two working.

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(3

柠栀 2024-09-18 16:24:30

一种方法是使用自连接,例如,

select a1.user_id, a1.answer_text AS Answer1, a2.answer_text AS Answer2
from user_answers a1 join user_answers a2 on a1.userid=a2.userid and a1.question_id=1     and a2.question_id=2
where a1.answer_text <> a2.answer_text

抱歉,如果语法有点偏离(自从我使用 MySQL 以来已经有一段时间了),但你明白了。

One method is to use a self join, eg

select a1.user_id, a1.answer_text AS Answer1, a2.answer_text AS Answer2
from user_answers a1 join user_answers a2 on a1.userid=a2.userid and a1.question_id=1     and a2.question_id=2
where a1.answer_text <> a2.answer_text

sorry if the syntax is a little off (It's been a while since I used MySQL), but you get the idea.

神妖 2024-09-18 16:24:30

我将使用如下内部联接来执行此操作:

SELECT
  user_id
FROM
  user_answers AS a1
  INNER JOIN user_answers AS a2 ON
    a2.user_id = a1.user_id
    AND a2.question_id=2
    AND a2.answer_text='value2'
WHERE
  a1.question_id=1
  AND a1.answer_text='value1'

翻译为:

  1. 查找 Question_id 为 1、answer_text 为“value1”的所有答案
  2. 对于这些答案,查找具有相同用户 id、question_id 为 2、answer_text 为“value2”的相应答案。
  3. 进行内部连接,即丢弃那些不能同时满足两者的连接。

I would do this using an inner join like this:

SELECT
  user_id
FROM
  user_answers AS a1
  INNER JOIN user_answers AS a2 ON
    a2.user_id = a1.user_id
    AND a2.question_id=2
    AND a2.answer_text='value2'
WHERE
  a1.question_id=1
  AND a1.answer_text='value1'

Translates to:

  1. Find all answers with a question_id of 1 and answer_text of 'value1'
  2. For these answers, find corresponding answers with same user id and question_id of 2 and answer_text of 'value2'.
  3. Do an inner join, ie throw away those which don't satisfy both.
蝶…霜飞 2024-09-18 16:24:30

我会尝试在没有子查询的情况下执行此操作,如下所示:

SELECT `User_answer`.`user_id`, 
  MAX(CASE `User_answer`.`question_id` WHEN 1 THEN `User_answer`.`answer_text` END) AS `q1_answer`,
  MAX(CASE `User_answer`.`question_id` WHEN 2 THEN `User_answer`.`answer_text` END) AS `q2_answer`
FROM `user_answers` AS `User_answer` 
WHERE (`User_answer`.`question_id` = 1 AND `User_answer`.`answer_text` = 'value1')
  OR  (`User_answer`.`question_id` = 2 AND `User_answer`.`answer_text` = 'value2')
GROUP BY `User_answer`.`user_id`
HAVING COUNT(DISTINCT `User_answer`.`question_id`) = 2;

I'd try doing it without a subquery, like this:

SELECT `User_answer`.`user_id`, 
  MAX(CASE `User_answer`.`question_id` WHEN 1 THEN `User_answer`.`answer_text` END) AS `q1_answer`,
  MAX(CASE `User_answer`.`question_id` WHEN 2 THEN `User_answer`.`answer_text` END) AS `q2_answer`
FROM `user_answers` AS `User_answer` 
WHERE (`User_answer`.`question_id` = 1 AND `User_answer`.`answer_text` = 'value1')
  OR  (`User_answer`.`question_id` = 2 AND `User_answer`.`answer_text` = 'value2')
GROUP BY `User_answer`.`user_id`
HAVING COUNT(DISTINCT `User_answer`.`question_id`) = 2;
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文