我有两个表
1) Student
id | Student_name
--------------------
1 | John
2 | Joy
3 | Raju
2) Category
id | category_name
-------------------------
1 | Maths Fest
2 | Science Fest
3 | IT Fest
4 | English Fest
5 | Cultural Fest
3) Student_category
id | student_id | category_id
------------------------------------
1 | 1 | 4
2 | 1 | 5
3 | 1 | 1
4 | 2 | 1
5 | 2 | 4
6 | 3 | 1
7 | 3 | 5
8 | 3 | 3
我需要写一个查询来选择参加过数学测试和amp;英语节。
我使用了这个查询
SELECT distinct student_name
FROM student A,student_category B
WHERE A.id=B.student_id
and B.category_id IN ('1','4')
但是它给出了参加数学测试或英语测试的学生的结果。
如果你必须有两个不同的类别，你可以简单地连接两次:
SELECT student_name
FROM student A
INNER JOIN student_category B ON A.id=B.student_id AND B.category_id = 1
INNER JOIN student_category C ON A.id=C.student_id AND C.category_id = 4
这样你将得到两个连接都存在的学生
对于动态选择类别(超过2个，如果您知道数量并且连接表不包含重复项)，您可以执行
SELECT student_name
FROM student A
INNER JOIN student_category B on A.id = B.student_id
AND B.category IN (1,4,5) -- one more
GROUP BY student_name
HAVING count(*) = 3 -- Number of categories in IN clause
试试这个:
SELECT student_name
FROM student A
INNER JOIN student_category B
ON A.id = B.student_id AND B.category_id IN ( 1, 4 )
GROUP BY student_name HAVING count( * ) = 2
这个查询只会在学生名的计数为两次时返回学生名。一次是英语考试，一次是数学考试。
如果有更多的类别，那么您可以简单地计算在逗号分隔的字符串中有多少个类别，并将count(*) = 2替换为count(*) = no. of categories。
示例查看参加过所有类别或2个以上类别的学生:
$category_id = 1, 2, 3, 4, 5
$a = substr_count($category_id, ","); // this will count number of times comma is appearing in your string.
$a = $a + 1; // number of items is + 1 than number of commas.
查询如下:
SELECT A.student_name
FROM student A,
student_category B
WHERE A.id = B.student_id AND B.category_id IN ('1', '4')
HAVING count(*) = $a;
希望能有所帮助。