SQL从多个表中获取值，然后对其进行排序。

Question

我想做一个MySQL查询。我有四张桌子: A，B，C，D。

我想得到共同的价值：“赢”从所有4个表中“帐户”=1，然后按大多数“胜利”排序。所有命令的“胜利”限制为3。

如果你帮我，我会很高兴的。

我试过这样的方法：

SELECT o.*
FROM (
(SELECT wins FROM 'A')
UNION ALL
(SELECT wins FROM 'B')
UNION ALL
(SELECT wins FROM 'C')
UNION ALL
(SELECT wins FROM 'D')
UNION ALL
(SELECT * FROM 'A'
WHERE
    account=1) 
) AS o
ORDER BY wins DESC

但这确实是错误的。

Answer 1

如果您需要知道从哪个表中获得结果：

SELECT *
FROM (  SELECT wins, 'Table A' AS DataTable
        FROM TableA
        WHERE account = 1
        UNION ALL
        SELECT wins, 'Table B' 
        FROM TableB
        WHERE account = 1
        UNION ALL
        SELECT wins, 'Table C' 
        FROM TableC
        WHERE account = 1
        UNION ALL
        SELECT wins, 'Table D' 
        FROM TableD
        WHERE account = 1) AS X
ORDER BY wins
LIMIT 3

如果这并不重要，而且您只需要最高的不同的wins

SELECT *
FROM (  SELECT wins
        FROM TableA
        WHERE account = 1
        UNION
        SELECT wins
        FROM TableB
        WHERE account = 1
        UNION
        SELECT wins
        FROM TableC
        WHERE account = 1
        UNION
        SELECT wins
        FROM TableD
        WHERE account = 1) AS X
ORDER BY wins DESC
LIMIT 3

Answer 2

从A中选择o.*

加入B

加入C

加入D

论D.account=A.account

和C.account=A.account

和B.account=A.account

和A.account='1‘

A.wins订购