So erhalten Sie verwandte Zeilen zwischen zwei Tabellen

Question

Meine Formen: Form 1 Übung|primaryMuscleGroup|motionName||------------------|------------------ - -||Brust|Barren-Dips||Brust|Bankdrücken||Brust|Liegestütze||Brust|Fliegen||Beine|Kniebeugen||Beine|Ausfallschritte||Rücken|Kreuzheben|Tabelle 2 Fitnessrekorde |Name|Bewegungsname ||------------------|--------------------|

P粉642919823 · Answer

您可以使用外连接或者使用not exists来实现，看看下面的查询是否符合您的需求：

select * 
from exercises e
where not exists (
  select * from exercises x 
    where exists (
      select * from fitnessRecords fr
      where fr.name = 'john smith' and fr.motionName = x.motionName
    ) and x.primaryMuscleGroup = e.primaryMuscleGroup
)

P粉388945432 · Answer

SELECT *
FROM excercises t1
WHERE NOT EXISTS ( SELECT NULL 
                   FROM fitnessRecords t2
                   JOIN excercises t3 USING (motionName)
                   WHERE t2.name = '给定的名字'
                     AND t1.primaryMuscleGroup = t3.primaryMuscleGroup )

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=eb216b7579d5fcd0c0ab628717f3d676