php 匹配多行,PHP-选择与相关表中的多行匹配的行

下表较大,但为方便起见已将其缩小

表1 -exercise_rolladex

Exercise_ID | Exercise_Name

---------------------------

1 Pushups

2 Turkish Get Ups

3 Squats

4 Ice Skater

表2-Exercise_planes

Exercise_Plane_ID | Exercise_Plane

----------------------------------

1 Sagittal

2 Frontal

3 Transverse

表3-Exercise_has_planes

Exercise_ID | Exercise_Plane_ID

-------------------------------

1 1

2 1

2 2

2 3

3 1

4 2

4 3

我的问题是：如何构造查询,在其中可以找到每个具有Exercise_Plane_ID = 1和Exercise_Plane_ID = 2的运动的Exercise_ID.换句话说,找到既具有矢状运动又具有额叶运动的运动.

正确的查询

SELECT e.Exercise_Name, p.Exercise_Plane

FROM exercise_rolladex e

INNER JOIN exercise_has_planes h ON h.Exercise_ID=e.Exercise_ID

INNER JOIN exercise_planes p ON p.Exercise_Plane_ID=h.Exercise_Plane_ID

WHERE p.Exercise_Plane_ID IN(2,1)

GROUP BY e.Exercise_ID

HAVING COUNT(DISTINCT h.Exercise_Plane_ID ) >= 2

更新跟进问题

那我怎么包括一个排除项呢？例如,找到使用plane_id 2和3的练习,但排除使用plane_id 1的练习(正确的结果是“ Ice Skater”)

我继续回答自己的问题：

SELECT e.Exercise_Name, p.Exercise_Plane

FROM exercise_rolladex e

INNER JOIN exercise_has_planes h ON h.Exercise_ID=e.Exercise_ID

INNER JOIN exercise_planes p ON p.Exercise_Plane_ID=h.Exercise_Plane_ID

WHERE p.Exercise_Plane_ID IN(2,3)

AND e.Exercise_ID NOT IN

(SELECT Exercise_ID FROM exercise_has_planes WHERE Exercise_Plane_ID='1')

GROUP BY e.Exercise_ID

HAVING COUNT(DISTINCT h.Exercise_Plane_ID ) >= 2

解决方法:

您可以执行以下操作,这将使用给定的输入ID检查计划ID,并过滤掉每个练习组中的计数(如果count返回一个以上),则表示练习中有飞机,having子句将满足同时拥有两个飞机在运动中

SELECT e.Exercise_Name,

p.Exercise_Plane

FROM exercise_rolladex e

INNER JOIN exercise_has_planes h ON h.Exercise_ID=e.Exercise_ID

INNER JOIN exercise_planes p ON p.Exercise_Plane_ID=h.Exercise_Plane_ID

WHERE p.Exercise_Plane_ID IN(2,1)

GROUP BY e.Exercise_ID

HAVING COUNT(DISTINCT h.Exercise_Plane_ID ) >= 2

标签：inner-join,mysql,php

来源： https://codeday.me/bug/20191121/2054286.html