社区所有版块导航
Python
python开源   Django   Python   DjangoApp   pycharm  
DATA
docker   Elasticsearch  
aigc
aigc   chatgpt  
WEB开发
linux   MongoDB   Redis   DATABASE   NGINX   其他Web框架   web工具   zookeeper   tornado   NoSql   Bootstrap   js   peewee   Git   bottle   IE   MQ   Jquery  
机器学习
机器学习算法  
Python88.com
反馈   公告   社区推广  
产品
短视频  
印度
印度  
Py学习  »  DATABASE

3左连接mysql

Carlos Pulgarin • 7 年前 • 2930 次点击  

这个问题已经有了答案:

我有这个文件表(这里是简化版):

+------+-------+--------------------------------------+
| id   | rev   | content                              |
+------+-------+--------------------------------------+
| 1    | 1     | ...                                  |
| 2    | 1     | ...                                  |
| 1    | 2     | ...                                  |
| 1    | 3     | ...                                  |
+------+-------+--------------------------------------+

如何为每个id选择一行并且只选择最大的rev?
对于上述数据,结果应包含两行: [1, 3, ...] 和 [2, 1, ..] . 我在用 MySQL .

目前我在 while 循环以检测并重写结果集中的旧rev。但这是唯一能达到效果的方法吗?难道没有 SQL 解决方案?

更新
如答案所示 是 一个sql解决方案,以及 here a sqlfiddle demo .

更新2
我在加上以上内容后注意到 闲逛 ,问题的上票率已超过答案的上票率。不是故意的!小提琴的基础是答案,特别是公认的答案。

Python社区是高质量的Python/Django开发社区
本文地址:http://www.python88.com/topic/40797
文章 [ 27 ]  |  最新文章 7 年前
Marc B
Reply   •   1 楼
Marc B    15 年前

像这样的?

SELECT yourtable.id, rev, content
FROM yourtable
INNER JOIN (
    SELECT id, max(rev) as maxrev FROM yourtable
    WHERE yourtable
    GROUP BY id
) AS child ON (yourtable.id = child.id) AND (yourtable.rev = maxrev)
Bikramjeet Singh HoldOffHunger
Reply   •   2 楼
Bikramjeet Singh HoldOffHunger    7 年前

我认为这是最简单的解决方案:

SELECT *
FROM
    (SELECT *
    FROM Employee
    ORDER BY Salary DESC)
AS employeesub
GROUP BY employeesub.Salary;
  • SELECT * :返回所有字段。
  • FROM Employee :表已搜索。
  • (SELECT *...) 子查询:返回所有人员,按薪资排序。
  • GROUP BY employeesub.Salary :强制将每个员工的排名靠前的薪资行作为返回结果。

如果你碰巧只需要一排,那就更简单了:

SELECT *
FROM Employee
ORDER BY Employee.Salary DESC
LIMIT 1

我还认为最容易分解、理解和修改为其他目的:

  • ORDER BY Employee.Salary DESC :按薪资排序结果,薪资最高者优先。
  • LIMIT 1 :只返回一个结果。

理解这种方法,解决任何类似的问题都变得很简单:获得最低工资的员工(变更 DESC 到 ASC ),获得收入最高的前十名员工(变动 极限1 到 LIMIT 10 ),按其他字段排序(更改 ORDER BY Employee.Salary 到 ORDER BY Employee.Commission 等)。

David Foster
Reply   •   3 楼
David Foster    13 年前

我不能保证它的性能,但这里有一个灵感来自于microsoft excel的局限性的技巧。它有一些很好的特点

好东西

  • 它应该只强制返回一个“最大记录”,即使有平局(有时有用)
  • 不需要加入

途径

它有点难看,要求您了解 转速 列。假设我们知道 转速 列是一个介于0.00和999之间的数字,包括小数,但小数点右侧将只有两位数字(例如,34.17将是有效值)。

其要点是,通过将主比较字段与所需数据串接/打包,创建一个合成列。这样,您就可以强制sql的max()聚合函数返回所有数据(因为它已经打包到一个列中)。然后你必须把数据解包。

上面的例子是用sql编写的

SELECT id, 
       CAST(SUBSTRING(max(packed_col) FROM 2 FOR 6) AS float) as max_rev,
       SUBSTRING(max(packed_col) FROM 11) AS content_for_max_rev 
FROM  (SELECT id, 
       CAST(1000 + rev + .001 as CHAR) || '---' || CAST(content AS char) AS packed_col
       FROM yourtable
      ) 
GROUP BY id

包装开始时 转速 列的长度为已知字符数,而不考虑 转速 比如说

  • 3.2变成1003.201
  • 57变成1057.001
  • 923.88变成1923.881

如果做得对,两个数字的字符串比较应该产生与两个数字的数值比较相同的“max”,并且使用substring函数很容易将其转换回原始数字(在任何地方都可以使用这种或那种形式)。

topchef
Reply   •   4 楼
topchef    10 年前

我大吃一惊,没有答案提供SQL窗口函数解决方案:

SELECT a.id, a.rev, a.contents
  FROM (SELECT id, rev, contents,
               ROW_NUMBER() OVER (PARTITION BY id ORDER BY rev DESC) rank
          FROM YourTable) a
 WHERE a.rank = 1 

在sql标准ansi/iso标准sql:2003中添加了window(或windowing)函数,并用ansi/iso标准sql:2008进行了扩展。有更多类型的等级函数可用于处理平局问题: RANK, DENSE_RANK, PERSENT_RANK .

Vajk Hermecz
Reply   •   5 楼
Vajk Hermecz    12 年前

另一个解决方案是使用相关子查询:

select yt.id, yt.rev, yt.contents
    from YourTable yt
    where rev = 
        (select max(rev) from YourTable st where yt.id=st.id)

在(id,rev)上有一个索引会将子查询呈现为一个简单的查找…

以下是与@adriancarneiro的答案(subquery,leftjoin)中的解决方案的比较,基于mysql测量,innodb表约有100万条记录,组大小为:1-3。

对于全表扫描,子查询/leftjoin/correlated timings作为6/8/9相互关联,而对于直接查找或批处理( id in (1,2,3) ,子查询比其他查询慢得多(由于重新运行子查询)。但是我无法区分leftjoin和相关解的速度。

最后一点,当leftjoin在组中创建n*(n+1)/2个连接时,它的性能会受到组大小的严重影响…

Kevin Burton
Reply   •   6 楼
Kevin Burton    12 年前

我倾向于使用尽可能少的代码…

你可以用 IN 试试这个:

SELECT * 
FROM t1 WHERE (id,rev) IN 
( SELECT id, MAX(rev)
  FROM t1
  GROUP BY id
)

在我看来不那么复杂…易于阅读和维护。

Adrian Carneiro Egli Becerra
Reply   •   7 楼
Adrian Carneiro Egli Becerra    11 年前

乍一看…

你只需要一个 GROUP BY 条款 MAX 聚合函数:

SELECT id, MAX(rev)
FROM YourTable
GROUP BY id

从来没有这么简单,是吗?

我刚注意到你需要 content 也列。

在sql中,这是一个非常常见的问题:在每一个组标识符的列中查找具有某个最大值的行的整个数据。在我的职业生涯中我经常听到这样的话。实际上,这是我在当前工作的技术面试中回答的问题之一。

实际上,stackoverflow社区创建了一个标签来处理这样的问题是很常见的: .

基本上,你有两种方法来解决这个问题:

与简单连接 group-identifier, max-value-in-group 子查询

在这种方法中,您首先发现 组标识符,组中的最大值 (上面已经解决)在子查询中。然后将表连接到子查询,在两个查询上都相等 group-identifier 和 max-value-in-group :

SELECT a.id, a.rev, a.contents
FROM YourTable a
INNER JOIN (
    SELECT id, MAX(rev) rev
    FROM YourTable
    GROUP BY id
) b ON a.id = b.id AND a.rev = b.rev

使用self左连接,调整连接条件和过滤器

在这种方法中,您将左键与表本身连接。平等,当然,在 组标识符 . 然后,两个聪明的动作:

  1. 第二个连接条件是左侧值小于右侧值
  2. 执行步骤1时,实际具有最大值的行将具有 NULL 在右边 LEFT JOIN 记得吗?。然后,我们过滤连接的结果,只显示右侧所在的行 无效的 .

所以你的结局是:

SELECT a.*
FROM YourTable a
LEFT OUTER JOIN YourTable b
    ON a.id = b.id AND a.rev < b.rev
WHERE b.id IS NULL;

结论

两种方法的结果完全相同。

如果你有两排 组中的最大值 对于 组标识符 ,两种方法的结果中都将包含这两行。

这两种方法都与sql ansi兼容,因此,不管它的“风格”如何,都可以与您喜欢的rdbms一起工作。

这两种方法都对性能友好,但是您的里程数可能不同(RDBMS、数据库结构、索引等)。所以当你选择一种方法时, 基准 . 一定要选对你最有意义的。