mysql - 如何获取MySQL :[select * from mytable AS t]?的结果

Question

我有这样一个 SQL:

SELECT m1.id1,m2.user,m3.pass,m4.cap,m5.test
FROM
(SELECT * FROM mytable) AS m1,
(SELECT * FROM mytable) AS m2,
(SELECT * FROM mytable) AS m3,
(SELECT * FROM mytable) AS m4,
(SELECT * FROM mytable) AS m5
WHERE m1.id1=m2.id2 AND m1.id1=m3.id3 AND m1.id1=m4.id4 AND m1.id1=m5.id5;

表 mytable 很大，但我需要从中获取所有信息，并且我需要多次使用子查询，问题是:如何保存查询结果(SELECT * FROM mytable)，例如:

SELECT m1.id1,m2.user,m3.pass,m4.cap,m5.test
FROM
(SELECT * FROM mytable) AS m1,
m1 AS m2,
m1 AS m3,
m1 AS m4,
m1 AS m5
WHERE m1.id1=m2.id2 AND m1.id1=m3.id3 AND m1.id1=m4.id4 AND m1.id1=m5.id5;

非常感谢。

Answer 1

你应该使用JOIN:

SELECT m1.id1,m2.user,m3.pass,m4.cap,m5.test
FROM mytable m1
JOIN mytable m2 ON m1.id1 = m2.id2
JOIN mytable m3 ON m1.id1 = m3.id3
JOIN mytable m4 ON m1.id1 = m4.id4
JOIN mytable m5 ON m1.id1 = m5.id5