我有四个表格,他们的'参数'
Movie(name, movie_ID,....)
Ticket(movie_ID, person_ID, ....)
Person(person_DNI, person_ID,....)
Date(person_DNI, Segment)
我需要下表,
person_ID | person_DNI | movie_ID | Segment
所以,为此,我加入了前两个桌子(电影和票(,然后加入以下两个表(日期和人(
SELECT person_ID, movie_ID
FROM `Movie` A JOIN `Ticket` B ON A.movie_ID = B.movie_ID
SELECT person_DNI, B.person_ID, A.segment
FROM Date A
LEFT JOIN Person B ON A.person_dni=B.person_DNI
但是现在,如何加入这两个"现在"表?
我的想法是加入票务和人的person_id
SELECT D.person_DNI, D.Segment, P.person_ID, T.movie_ID
FROM Date D
JOIN Person P
ON D.person_DNI = P.person_DNI
JOIN Ticket T
ON P.person_ID = T.person_ID
-- Because you dont use anything from Movie like Movie.name
-- you dont really need join Movie to get movie_id
JOIN Movie M
ON T.movie_ID = M.movie_ID
,由于您没有详细详细介绍每列的意义,因此我的答案在不关注顺序的情况下表现出内在连接。
最好是,您可以在单个查询中加入多个表:
SELECT person_ID, t.person_ID, m.movie_ID, Segment
FROM Person p
JOIN Ticket t ON t.person_ID = p.person_ID
JOIN Movie m ON m.movie_ID = t.movie_ID
JOIN Date d ON d.person_DNI = p.person_DNI
或(不太好(,您可以通过嵌套查询查询临时表:
SELECT t1.person_ID, person_DNI, movie_ID, Segment
FROM (
SELECT person_ID, movie_ID
FROM `Movie` A JOIN `Ticket` B ON A.movie_ID = B.movie_ID
) t1
JOIN (
SELECT person_DNI, B.person_ID, A.segment
FROM Date A
LEFT JOIN Person B ON A.person_dni = B.person_DNI
) t2
ON t1.person_ID = t2.person_ID
您可以在mysql中使用自然加入:
SELECT person_ID , person_DNI , movie_ID , Segment
FROM (((Date NATURAL JOIN Person) NATURAL JOIN Ticket ) NATURAL JOIN Movie)
有关更多信息,请查看以下链接:https://www.w3resource.com/mysql/advance-query-in-in-mysql/mysql-natural-join.php
我希望这会有所帮助。