从两个不同的表中选择具有某一列最大值的行的SQL SELECT。

Question

我的两个表的设置如下：

table1

+------+---------+--------------------------------------+
| id   | tail    | content                              |
+------+---------+--------------------------------------+
| 1    | abc     | ...                                  |
| 2    | def     | ...                                  |
| 3    | ghi     | ...                                  |
| 4    | def     | ...                                  |
| 5    | jkl     | ...                                  |
+------+-------+----------------------------------------+

table2

+------+--------+---------------------------------------+
| id   | tailID | value   |  others                     |
+------+--------+---------------------------------------+
| 1    | 2      | 412     |                             |
| 2    | 3      | 215     |                             |
| 1    | 2      | 571     |                             |
| 1    | 4      | 123     |                             |
+------+--------+---------------------------------------+

我想要在一行中获取这两个表中所有的列，满足tail = tailID但不包含具有相同tail的重复行。

对于重复的TAIL，只需要获取具有最大VALUE的单个行即可。

我目前正在使用以下查询:

SELECT table1.tail, table2.other_column 
FROM table1 
INNER JOIN table2 
on table1.id = table2.tailID 
WHERE table1.some_coloum = "a sepecific string" 
ORDER BY table2.value

但是它会返回许多具有相同tail的重复行。

我只需要具有最高table2的VALUE的重复TAIL的单个行。

- Bishawjit Das

---

MySQL还是MS SQL Server？（不要标记未涉及的产品。） - jarlh

在编辑问题时，同时添加预期结果。 - jarlh

你尝试运行过任何代码吗？ - Tedo G.

问题已经纠正。 - Bishawjit Das

@gofr1 我的问题基于mysql。你的答案对我的查询非常有效，非常感谢。 - Bishawjit Das

@BishawjitDas 不客气！ - gofr1

Answer 1

使用CROSS APPLY进行DISTINCT：

SELECT DISTINCT t1.tail,
                t2.other_column,
                t3.[value]
FROM table1 t1
CROSS APPLY (
    SELECT  tailid,
            MAX([value]) as [value]
    FROM table2
    WHERE tailid = t1.id
    GROUP BY tailid
    ) as t3
INNER JOIN table2 t2
    ON t2.tailid = t3.tailid AND t3.[value] = t2.[value]
WHERE t1.some_coloum = "a sepecific string" 

- gofr1

---

这是错误的。因为你没有为t2.other_column指定聚合函数，所以DBMS会任意选择它们。要选择正确的列，你需要一个函数来获取组中value最大的t2.other_column。这样的函数（例如Oracle的KEEP FIRST/LAST）在MySQL中不存在。因此，你需要采用不同的方法。例如Serg所示的方法。 - Thorsten Kettner

@ThorstenKettner 这是关于 SQL Server 的问题，由 OP 标记了这个标签。我询问 OP 使用的 RDBMS。如果 SQL Server 是错误标记的 - 我会删除我的答案。 - gofr1

啊，好的。我甚至没有看到t2.other_column在GROUP BY中。所以它当然不会被任意选择，无论是在MySQL还是SQL Server中。但这并不起作用，因为它给你每个other_column的最大value，而你实际上想要相反的结果。 - Thorsten Kettner

那很有道理，我改变了我的答案。 - gofr1

Answer 2

先将表2分组，然后进行连接操作。

SELECT table1.tail, table2.other_column 
FROM table1 
INNER JOIN (
   SELECT tailID, max(value) as value
   FROM table2
   GROUP BY tailID
) t2g ON t2g.tailID = table1.ID
INNER JOIN table2 
on t2g.tailID = table2.tailID AND  t2g.value = table2.value
WHERE table1.some_coloum = "a sepecific string" 
ORDER BY table2.value

如果在table2中有两行或更多行的max(value)和tailID相同，那么查询仍然可能会为table1的一行返回多个结果。

- Serg

Answer 3

选择仅包含列value最大值的行。

SELECT table1.tail, MAX(table2.value)
FROM
  table1
  INNER JOIN table2 ON table1.id = table2.tailID
  WHERE table1.content = "test"

http://sqlfiddle.com/#!9/b70d29/3/0

- Dave