SQL:对内部联接表上的多行进行条件处理 [英] SQL: conditioning multiple rows on an inner joined table
问题描述
我需要有关SQL语法的帮助.
I am needing some help with SQL syntax.
说我有一个成员表，一个问题表和一个答案表，这些表的基本知识如下:
Say I have a members table, a questions table and an answers table, basics of the tables are as follows:
成员表:
memberId: primary key
问题表:
questionId: primary key,
questionText:varchar
答案表:
answerId: primary key,
questionId: int (relating to the row of the question in the questions table)
memberId: int (relating to the row of the member in the members table)
answerValue:varchar
表将具有更多的列，但出于这个问题的目的，这些就足够了.
The tables will have more columns but for the purpose of this problem these should suffice.
现在在某些情况下，我将要运行一个查询，该查询将返回成员ID的唯一列表，其中成员对问题列表的答案与某个值匹配.
Now In some instances I will want to run a query that will return a distinct list of member ids where the members answers to a list of questions match a certain value.
例如:
说问题表(第1行)中有一个问题，问题文本为:你喜欢猫还是狗?":
Say there is a question in the questions table (row 1) with the question text: "Do you like cats or dogs?":
questionId questionText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 Do you like cats or dogs?
然后在成员表中有10个成员，其ID为1到10，在答案表中有相应的答案:
Then there are 10 members in the members table from id 1 to 10, and corresponding answers in the answers table:
answerid questionId memberId answerId
---------------------------------------------------------------------------------------
1 1 1 cats
2 1 2 both
3 1 3 cats
4 1 4 cats
5 1 5 cats
6 1 6 dogs
7 1 7 dogs
8 1 8 dogs
9 1 9 dogs
10 1 10 both
用这个简单的查询即可，例如那些回答狗的人:
It would be simple enough to query with this for example, those who answered dogs:
SELECT DISTINCT memberId FROM members INNER JOIN answers ON members.memberId = answers.answerId WHERE answers.questionId = 1 AND answers.answerValue = 'dogs'
这将返回:
memberId
---------------
6
7
8
9
但是如果我要在问题表中添加另一个问题怎么办?
But what if I were to add another question to the questions table:
questionId questionText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 Do you like cats or dogs?
2 What is your favourite color?
答案表更新如下:
answerid questionId memberId answerId
---------------------------------------------------------------------------------------
1 1 1 cats
2 1 2 both
3 1 3 cats
4 1 4 cats
5 1 5 cats
6 1 6 dogs
7 1 7 dogs
8 1 8 dogs
9 1 9 dogs
10 1 10 both
11 2 1 blue
12 2 2 red
13 2 3 green
14 2 5 green
15 2 4 black
16 2 6 violet
17 2 7 pink
18 2 8 green
19 2 9 red
20 2 10 yellow
我将如何查询多个问题?
How would I query for multiple questions?
我要寻找的是一种固定的语法，该语法可用于查询已回答许多问题的特定答案的不同成员的列表，例如:
What I am looking for is a set syntax that would work for querying a list of distinct members who have answered specific answers to any number of questions, for instance:
对喜欢((狗和红色)或(猫和绿色))的成员的查询应返回:
The query for members who like ((dogs AND red) OR (cats AND green)) should return:
memberId
-----------------
9
3
5
答案可以任意组合.它的本质是如何查询内部联接表的多个值?
There could be any combination of answers. The essence of it is how to I query against multiple values for an inner joined table?
感谢任何人都可以提供的帮助，如果有点困惑，则表示歉意.
Thanks for any help anyone can give, sorry if its a little confusing.
推荐答案
您可以选择回答其中一个问题的单个ID，如下所示:
You can select the individual Ids who answered one of the questions, like this:
select distinct memberId where answerId = 'dogs'
select distinct memberId where answerId = 'red'
然后用内部联接替换ands，如下所示:
Then replace the ands with inner joins, like this:
select memberId from
(select distinct memberId where answerId = 'dogs') D
inner join
(select distinct memberId where answerId = 'red') R
on D.MemberId = R.memberId
并用UNION替换OR，如下所示:
And replace the OR with UNION, like this:
select memberId from
(select distinct memberId where answerId = 'dogs') D
inner join
(select distinct memberId where answerId = 'red') R
on D.MemberId = R.memberId
union
select memberId from
(select distinct memberId where answerId = 'cats') D
inner join
(select distinct memberId where answerId = 'green') R
on D.MemberId = R.memberId
正如您所说的，这正是定罪":
This is exactly, as you say, a "set sintax":
Set: each indivdual select
Intersection of sets : inner join
Union of sets: union
如果您需要其他条件，例如仅检查答案1和2，则可以将CTE与(1,2，answer)中的answerId"之类的常见条件一起使用，以避免在每次选择时都写入相同的条件，从而产生正弦更清晰.
If you need extra conditions, for example checking only answers 1 and 2, you can use a CTE with the common conditions like "answerId in (1,2)" to avoid writing this same condition in every select, making the sintax more clear.
您还可以使用此正弦波，它与您的原始文字更相似:
You can also use this sintax, which is more similar to your original writing:
select memberId from members M
where
( exists (select 1 from answers where answerId='dogs' and memberId=M.member Id)
and exists (select 1 from answers where answerId='red' and memberId=M.member Id) )
or
( exists (select 1 from answers where answerId='cats' and memberId=M.member Id)
and exists (select 1 from answers where answerId='green' and memberId=M.member Id) )
-- I select 1 instead of * or any column for performance reasons
我省略了添加额外支票(answerId等)的详细信息，以使其更易于理解.
I leave out the details of adding extra checks (answerId and so on) to make it easier to understand.
这篇关于SQL:对内部联接表上的多行进行条件处理的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
