社区所有版块导航
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 年前 • 2923 次点击  

这个问题已经有了答案:

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

+------+-------+--------------------------------------+
| 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 年前
Ian A McElhenny
Reply   •   1 楼
Ian A McElhenny    7 年前

解释

这不是纯sql。这将使用sqlalchemy orm。

我来这里是为了寻求sqlalchemy的帮助,所以我将用python/sqlalchemy版本复制adrian carneiro的答案,特别是外部连接部分。

此查询回答以下问题:

“您能把这组记录(基于同一个id)中版本号最高的记录还给我吗?”

这允许我复制记录,更新它,增加它的版本号,并以这样的方式拥有旧版本的副本,我可以显示随着时间的变化。

代码

MyTableAlias = aliased(MyTable)
newest_records = appdb.session.query(MyTable).select_from(join(
    MyTable, 
    MyTableAlias, 
    onclause=and_(
        MyTable.id == MyTableAlias.id,
        MyTable.version_int < MyTableAlias.version_int
    ),
    isouter=True
    )
).filter(
    MyTableAlias.id  == None,
).all()

在PostgreSQL数据库上测试。

zovio
Reply   •   2 楼
zovio    8 年前

当您组合 rev 和 id 合而为一 maxRevId 价值观 MAX() 然后将其拆分回原始值:

SELECT maxRevId & ((1 << 32) - 1) as id, maxRevId >> 32 AS rev
FROM (SELECT MAX(((rev << 32) | id)) AS maxRevId
      FROM YourTable
      GROUP BY id) x;

当有一个复杂的连接而不是一个表时,这尤其快。使用传统的方法,复杂的连接将执行两次。

当 转速 和 身份证件 是 INT UNSIGNED (32位)组合值适合 BIGINT UNSIGNED (64位)。当 身份证件 和; 转速 大于32位值或由多个列组成,则需要将该值组合成具有适当填充的二进制值 最大() .

Richard Ball
Reply   •   3 楼
Richard Ball    8 年前

我用下面的方法自己解决了一个问题。我首先创建了一个临时表,并插入了每个唯一id的最大rev值。

CREATE TABLE #temp1
(
    id varchar(20)
    , rev int
)
INSERT INTO #temp1
SELECT a.id, MAX(a.rev) as rev
FROM 
    (
        SELECT id, content, SUM(rev) as rev
        FROM YourTable
        GROUP BY id, content
    ) as a 
GROUP BY a.id
ORDER BY a.id

然后我将这些最大值(temp1)加入到所有可能的id/内容组合中。通过这样做,我自然过滤掉了非最大id/内容组合,只剩下每个组合的最大rev值。

SELECT a.id, a.rev, content
FROM #temp1 as a
LEFT JOIN
    (
        SELECT id, content, SUM(rev) as rev
        FROM YourTable
        GROUP BY id, content
    ) as b on a.id = b.id and a.rev = b.rev
GROUP BY a.id, a.rev, b.content
ORDER BY a.id
Axel NAGARAJ
Reply   •   4 楼
Axel NAGARAJ    9 年前

这里有另一个解决方案,只检索具有该字段最大值的字段的记录。这适用于我工作的平台sql400。在本例中,字段field5中最大值的记录将由以下sql语句检索。

SELECT A.KEYFIELD1, A.KEYFIELD2, A.FIELD3, A.FIELD4, A.FIELD5
  FROM MYFILE A
 WHERE RRN(A) IN
   (SELECT RRN(B) 
      FROM MYFILE B
     WHERE B.KEYFIELD1 = A.KEYFIELD1 AND B.KEYFIELD2 = A.KEYFIELD2
     ORDER BY B.FIELD5 DESC
     FETCH FIRST ROW ONLY)
qaisjp
Reply   •   5 楼
qaisjp    9 年前

这些答案对我都不起作用。

这就是我的工作。

with score as (select max(score_up) from history)
select history.* from score, history where history.score_up = score.max
Abdul Samad
Reply   •   6 楼
Abdul Samad    9 年前

这是另一个解决方案,希望它能帮助某些人

Select a.id , a.rev, a.content from Table1 a
inner join 
(SELECT id, max(rev) rev FROM Table1 GROUP BY id) x on x.id =a.id and x.rev =a.rev
blokeish
Reply   •   7 楼
blokeish    10 年前

按相反的顺序对rev字段进行排序,然后按id分组,id给出每个分组的第一行,即rev值最高的那一行。

SELECT * FROM (SELECT * FROM table1 ORDER BY id, rev DESC) X GROUP BY X.id;

测试在 http://sqlfiddle.com/ 使用以下数据

CREATE TABLE table1
    (`id` int, `rev` int, `content` varchar(11));

INSERT INTO table1
    (`id`, `rev`, `content`)
VALUES
    (1, 1, 'One-One'),
    (1, 2, 'One-Two'),
    (2, 1, 'Two-One'),
    (2, 2, 'Two-Two'),
    (3, 2, 'Three-Two'),
    (3, 1, 'Three-One'),
    (3, 3, 'Three-Three')
;

这在mysql 5.5和5.6中产生了以下结果

id  rev content
1   2   One-Two
2   2   Two-Two
3   3   Three-Two
user5124980
Reply   •   8 楼
user5124980    11 年前

我喜欢按某个列对记录进行排序。在这种情况下,等级 rev 值分组依据 id . 高一点的 转速 会有更低的排名。如此最高 转速 排名为1。

select id, rev, content
from
 (select
    @rowNum := if(@prevValue = id, @rowNum+1, 1) as row_num,
    id, rev, content,
    @prevValue := id
  from
   (select id, rev, content from YOURTABLE order by id asc, rev desc) TEMP,
   (select @rowNum := 1 from DUAL) X,
   (select @prevValue := -1 from DUAL) Y) TEMP
where row_num = 1;

不确定引入变量是否会使整个过程变慢。但至少我没有质疑 YOURTABLE 两次。

Ashish Kakkad
Reply   •   9 楼
Ashish Kakkad    11 年前

这是个很好的方法

使用以下代码:

with temp as  ( 
select count(field1) as summ , field1
from table_name
group by field1 )
select * from temp where summ = (select max(summ) from temp)
schlebe
Reply   •   10 楼
schlebe    7 年前

做这项工作的另一种方法是 MAX() 超分割子句中的解析函数

SELECT t.*
  FROM
    (
    SELECT id
          ,rev
          ,contents
          ,MAX(rev) OVER (PARTITION BY id) as max_rev
      FROM YourTable
    ) t
  WHERE t.rev = t.max_rev 

其他的 ROW_NUMBER() 本文中已经记录的分区上解决方案是

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

这两个选择在Oracle 10g上运行良好。

max()解决方案运行速度肯定更快 行数() 解决方案,因为 最大() 复杂性是 O(n) 虽然 行数() 复杂性最低 O(n.log(n)) 哪里 n 表示表中的记录数!

Cody Gray
Reply   •   11 楼
Cody Gray    7 年前
SELECT *
FROM Employee
where Employee.Salary in (select max(salary) from Employee group by Employe_id)
ORDER BY Employee.Salary
Munim Munna
Reply   •   12 楼
Munim Munna    7 年前

这个怎么样:

SELECT all_fields.*  
FROM (SELECT id, MAX(rev) FROM yourtable GROUP BY id) AS max_recs  
LEFT OUTER JOIN yourtable AS all_fields 
ON max_recs.id = all_fields.id
Marek Wysmułek
Reply   •   13 楼
Marek Wysmułek    11 年前

我会用这个:

select t.*
from test as t
join
   (select max(rev) as rev
    from test
    group by id) as o
on o.rev = t.rev

子查询select可能不太有效,但in join子句似乎是可用的。我不是优化查询的专家,但我在mysql、postgresql、firebird都试过,而且效果非常好。

您可以在多个联接和where子句中使用此架构。这是我的工作示例(使用“firmy”表解决与您相同的问题):

select *
from platnosci as p
join firmy as f
on p.id_rel_firmy = f.id_rel
join (select max(id_obj) as id_obj
      from firmy
      group by id_rel) as o
on o.id_obj = f.id_obj and p.od > '2014-03-01'

它被要求在有十几岁记录的桌子上,而在真正不太强大的机器上,它只需要不到0,01秒。

我不会用in子句(正如上面提到的)。in用于constans的短列表,而不是作为建立在子查询上的查询过滤器。这是因为对每个扫描的记录都执行in中的子查询,这会使查询花费非常长的时间。

plavozont
Reply   •   14 楼
plavozont    12 年前

此解决方案只从您的表中进行一次选择,因此速度更快。根据sqlfiddle.com上的测试,它只适用于mysql和sqlite(用于sqlite remove desc)。也许它可以被调整到我不熟悉的其他语言上。

SELECT *
FROM ( SELECT *
       FROM ( SELECT 1 as id, 1 as rev, 'content1' as content
              UNION
              SELECT 2, 1, 'content2'
              UNION
              SELECT 1, 2, 'content3'
              UNION
              SELECT 1, 3, 'content4'
            ) as YourTable
       ORDER BY id, rev DESC
   ) as YourTable
GROUP BY id
Abhishek Rana
Reply   •   15 楼
Abhishek Rana    7 年前

我想,你想要这个?

select * from docs where (id, rev) IN (select id, max(rev) as rev from docs group by id order by id)  

SQL小提琴: Check here

seahawk
Reply   •   16 楼
seahawk    11 年前

如果select语句中有多个字段,并且希望通过优化代码获得所有这些字段的最新值:

select * from
(select * from table_name
order by id,rev desc) temp
group by id 
KyleMit SLaks
Reply   •   17 楼
KyleMit SLaks    8 年前

不是MySQL ,但对于发现此问题并使用SQL的其他人来说,另一种方法可以解决 问题是使用 Cross Apply 在MS SQL中

WITH DocIds AS (SELECT DISTINCT id FROM docs)

SELECT d2.id, d2.rev, d2.content
FROM DocIds d1
CROSS APPLY (
  SELECT Top 1 * FROM docs d
  WHERE d.id = d1.id
  ORDER BY rev DESC
) d2

Here's an example in SqlFiddle

Jannes
Reply   •   18 楼
Jannes    12 年前

第三个解决方案是mysql特有的,看起来是这样的:

SELECT id, MAX(rev) AS rev
 , 0+SUBSTRING_INDEX(GROUP_CONCAT(numeric_content ORDER BY rev DESC), ',', 1) AS numeric_content
FROM t1
GROUP BY id

是的,它看起来很糟糕(转换成字符串和返回等),但根据我的经验,它通常比其他解决方案更快。也许这只是为了我的用例,但是我已经在有数百万条记录和许多唯一id的表上使用了它。可能是因为mysql在优化其他解决方案方面做得很差(至少在我提出这个解决方案的5.0天内)。

一个重要的事情是group_concat对于它可以建立的字符串有一个最大长度。你可能想通过设置 group_concat_max_len 变量。请记住,如果有大量行,这将限制缩放。

无论如何,如果您的内容字段已经是文本,则上面的操作不会直接起作用。在这种情况下,您可能需要使用不同的分隔符,例如\0 maybe。你也会遇到 组 限制得更快。

Bulat
Reply   •   19 楼
Bulat    7 年前

我喜欢用 NOT EXIST -基于此问题的解决方案:

SELECT 
  id, 
  rev
  -- you can select other columns here
FROM YourTable t
WHERE NOT EXISTS (
   SELECT * FROM YourTable t WHERE t.id = id AND rev > t.rev
)

这将选择组中具有最大值的所有记录,并允许您选择其他列。

reformed
Reply   •   20 楼
reformed    7 年前

既然这是关于这个问题最流行的问题,我也将在这里发布另一个答案:

看起来有更简单的方法来做这个(但是 仅在MySQL中 ):

select *
from (select * from mytable order by id, rev desc ) x
group by id

请相信用户波希米亚的回答 在里面 this question 为这个问题提供了一个简洁而优雅的答案。

编辑: 尽管这个解决方案对很多人都有效,但从长远来看,它可能并不稳定,因为mysql不能保证group by语句会为不在group by list中的列返回有意义的值。因此,使用这个解决方案的风险由你自己承担!