查询子查询

Question

我正在尝试这样做：

SELECT t1.*,
       t2.*
FROM   (SELECT *
        FROM   USER) AS t1,
       (SELECT *
        FROM   t1) AS t2

它在MySQL中不起作用。像上面这样的事情是可能的吗？我的另一个选择是采用最后一个子查询，并将其“包装”在主查询中。想法？

Answer 1

使用：

SELECT t1.*,
       t2.*
  FROM (SELECT *
          FROM USER) AS t1,
       (SELECT *
          FROM (SELECT *
                  FROM USER) AS t1) AS t2

WITH语法允许您在CTE之上构建：

WITH t1 AS (
  SELECT * FROM USER), -- comma separates them
     t2 AS (
  SELECT * FROM t1)
SELECT t1.*, t2.*
  FROM t1, t2

..but MySQL不支持WITH子句。

Answer 2

好呀

SELECT t1.*, t2.* FROM USER as t1, USER as t2

？您还应该添加一个条件，否则这将生成大量的行。

现在，如果您想询问如何引用上一个子查询，则应该重复该查询并添加所需的额外条件：

SELECT t1.*, t2.* FROM (SELECT bar FROM foo WHERE baz=1) as t1, 
(SELECT bluz,bar FROM foo WHERE baz=1 AND quux = 0) as t2 WHERE t2.bar = t1.bar

另一种选择是使用子查询(t1)创建视图或临时表

Answer 3

如果这两个表具有相同的列数，则可以使用UNION，例如

SELECT *
FROM   USER
UNION
SELECT *
FROM   t1

如果它们不具有相同的列，您将需要通过某种方式来连接表，比如使用外键。例如。

SELECT *
FROM   USER as t1
JOIN   t1 as t2 (on t1.id = t2.id)