连接表并查找列值的最大值。

Question

我有三个表table_1、table_2和table_3，它们都有一个公共列comm_name作为外键。我想通过连接这三个表来找到列data_id的最大值。使用条件是comm_name。

简而言之：合并这三个表并找到最大的data_id，即返回9

我尝试了以下方法：

SELECT max(data_id) FROM (( SELECT table_1.data_id FROM table_1 where comm_name='aa') UNION(SELECT table_2.data_id FROM table_2 where comm_name='aa') UNION(SELECT table_3.data_id FROM table_2 where comm_name='aa'));

但是它显示错误

An expression was expected. (near "(" at position 26)
Unexpected token. (near "(" at position 26)
Unexpected token. (near "(" at position 27)
This type of clause was previously parsed. (near "SELECT" at position 29)
This type of clause was previously parsed. (near "SELECT" at position 125)
This type of clause was previously parsed. (near "SELECT" at position 220)

- Jasir alwafaa

Answer 1

联合运算符应该帮助您拥有一个数据集，您可以查询（未经测试）：

select max(data_id) from (select data_id from table_1 union select data_id from table_2 union select data_id from table_3)

- ka_lin

Answer 2

试一试

SELECT Name, MAX(data_id) as MaxId
    FROM
    (
        SELECT data_id
        FROM table1
        UNION ALL
       SELECT data_id
        FROM table2
        UNION ALL
       SELECT data_id
        FROM table3
    );

- Harshad Hirapara

---

如何添加where条件来检查三个表的comm_name是否相同 - Jasir alwafaa