我有兩個表 table1 和 table2。第三個表 table3 將這兩個表關聯起來，因此 table3 中的每條記錄都是主鍵，表 1 的外鍵和表 2 的外鍵。
我有一個表 2 的選擇，并且想要查找表 1 中的所有記錄，這些記錄在表 3 中對于從表 2 中選擇的每個記錄都有相應的記錄
| 表格1 |
|---|
| 一個 |
| b |
| C |
| 表 2 選擇 |
|---|
| 1 |
| 2 |
| 3 |
表3
| fk1 | fk2 |
|---|---|
| 一個 | 1 |
| 一個 | 2 |
| b | 1 |
| b | 2 |
| b | 3 |
| C | 2 |
| C | 3 |
| C | 4 |
在這個例子中，我想找到記錄 b 但不記錄 a 或 c
我可以加入：
select *
from table3
inner join table1
inner join table2 on table3.fk1=table1.id and table3.fk2=table2.id
where *table2 selection criteria*
但這只是給了我一張桌子上的所有東西，我不知道如何要求您匹配選擇的所有成員，而不是任何成員
select eveonline_evecharacter.*
from eveonline_evecharacter
inner join mechanicus_characterskillmap on eveonline_evecharacter.id = mechanicus_characterskillmap.character_id
inner join mechanicus_skill on mechanicus_skill.id = mechanicus_characterskillmap.skill_id
inner join mechanicus_blueprintskill on mechanicus_blueprintskill.skill_id = mechanicus_skill.id
inner join mechanicus_blueprintoutput on mechanicus_blueprintoutput.blueprint_id=mechanicus_blueprintskill.blueprint_id
inner join mechanicus_item on mechanicus_item.id = mechanicus_blueprintoutput.item_id
where mechanicus_item.name = "Small Trimark Armor Pump I"
group by eveonline_evecharacter.id
having count (*) = (select count(*) from mechanicus_skill
inner join mechanicus_blueprintskill on mechanicus_blueprintskill.skill_id = mechanicus_skill.id
inner join mechanicus_blueprintoutput on mechanicus_blueprintoutput.blueprint_id=mechanicus_blueprintskill.blueprint_id
inner join mechanicus_item on mechanicus_item.id = mechanicus_blueprintoutput.item_id
where mechanicus_item.name = "Small Trimark Armor Pump I");
uj5u.com熱心網友回復：
獲取連接行的計數并將其與表 2 中所有行的計數進行比較。
SELECT t1.*
FROM table1 AS t1
JOIN table3 AS t3 ON t3.fk1 = t1.id
JOIN table2 AS t2 ON t3.fk2 = t2.id
WHERE *some condition on table 2*
GROUP BY t1.id
HAVING COUNT(*) = (
SELECT COUNT(*) FROM table2 WHERE *some condition on table 2*
)
這假設 table1 和 table2 中的所有 ID 都是唯一的，并且所有對都是唯一的。如果沒有，請使用HAVING COUNT(DISTINCT t1.id) = (SELECT COUNT(DISTINCT id) FROM table2).
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/516981.html
標籤：mysql
上一篇：替代子查詢（mysql）