sql-每个名称对应一行

5ktev3wc 于 2021-06-21 发布在 Mysql

关注(0)|答案(2)|浏览(368)

试图利用https://stackoverflow.com/a/123481 我试图为每一行检索一行 name ，具有特定的排序，即按降序排序后的第一行 active , created ，那么 prid . 然而 active 列可以包含数字或 NULL 值，这将导致 name=bat 案例。任何帮助都将不胜感激。
源表：

+------+-------+--------+---------+
| prid | name  | active | created |
+------+-------+--------+---------+
| 1    | bat   | NULL   | 3       |
| 2    | bat   | 1      | 2       |
| 3    | bat   | 2      | 1       |
| 4    | bat   | 3      | 0       |
| 5    | noise | NULL   | 2       |
| 6    | noise | NULL   | 1       |
| 7    | cup   | NULL   | 0       |
| 8    | cup   | NULL   | 0       |
| 9    | egg   | 4      | 4       |
| 10   | egg   | 4      | 2       |
+------+-------+--------+---------+

期望结果：

+------+-------+--------+---------+
| prid | name  | active | created |
+------+-------+--------+---------+
| 9    | egg   | 4      | 4       |
| 4    | bat   | 3      | 0       |
| 5    | noise | NULL   | 2       |
| 8    | cup   | NULL   | 0       |
+------+-------+--------+---------+

sql语句：

SELECT p1.*
FROM source_table p1
LEFT JOIN source_table p2 ON (
  p1.name = p2.name
  AND (
    p1.active < p2.active
    OR (
      (p1.active = p2.active OR (p1.active IS NULL AND p2.active IS NULL))
      AND (
        p1.created < p2.created
        OR (
          p1.created = p2.created AND p1.prid < p2.prid
        )
      )
    )
  )
)
WHERE p2.prid IS NULL
ORDER BY p1.active DESC, p1.created DESC, p1.prid DESC

实际结果：

+------+-------+--------+---------+
| prid | name  | active | created |
+------+-------+--------+---------+
| 9    | egg   | 4      | 4       |
| 4    | bat   | 3      | 0       |
| 1    | bat   | NULL   | 3       |
| 5    | noise | NULL   | 2       |
| 8    | cup   | NULL   | 0       |
+------+-------+--------+---------+

@戈登林诺夫
谢谢你的帮助，我尝试使用第二个版本的索引 (name, active, created, prid) 以及 (active, created, prid) ，但是速度很慢。
这需要1秒，返回正确的结果，但顺序错误：

SELECT t1.prid
FROM source_table t1
WHERE t1.prid = (
  SELECT t2.prid
  FROM source_table t2
  WHERE t2.name = t1.name
  ORDER BY t2.active DESC, t2.created DESC, t2.prid DESC
  LIMIT 1
)
LIMIT 50

这需要55秒：

SELECT t1.prid
FROM source_table t1
WHERE t1.prid = (
  SELECT t2.prid
  FROM source_table t2
  WHERE t2.name = t1.name
  ORDER BY t2.active DESC, t2.created DESC, t2.prid DESC
  LIMIT 1
)
ORDER BY t1.active DESC, t1.created DESC, t1.prid DESC
LIMIT 50

我真的需要 LIMIT 500 ，有什么想法吗？
@里克·詹姆斯
sql fiddle链接：http://sqlfiddle.com/#!9/f9b39/2/0

sql mysql mariadb aurora

来源：https://stackoverflow.com/questions/51304173/sql-one-row-for-each-name

2条答案

按热度按时间

ar7v8xwq1#

使用ansi标准 row_number() 功能：

select t.*
from (select t.*,
             row_number() over (partition by name
                                order by active desc, created desc, pid desc
                               ) as seqnum
      from source_table t
     ) t
where seqnum = 1;

以上工作在mysql 8+和mariadb 10.2+中。在早期版本中，可以执行以下操作：

select t.*
from source_table t
where t.prid = (select t2.prid
                from source_table t2
                where t2.name = t.name
                order by t2.active desc, t2.created desc, t2.pid desc
                limit 1
               );

为了提高性能，您需要一个索引 source_table(name, actdive desc, created desc, pid desc, prid) .

赞(0）回复(0）举报 2021-06-21

mkshixfv2#

ORDER BY IFNULL(active,  -1) DESC,
         IFNULL(created, -1) DESC,
         IFNULL(pid,     -1) DESC,

完全stmt

SELECT  prid, name, active, created
    FROM  
        ( SELECT  GROUP_CONCAT(prid
                    ORDER BY  IFNULL(active,  -1) DESC,
                              IFNULL(created, -1) DESC,
                              IFNULL(prid,    -1) DESC 
                              ) AS ids
            FROM  source_table
            GROUP BY  name 
        ) AS s1
    JOIN  source_table AS s2
        ON s2.prid = SUBSTRING_INDEX(s1.ids, ',', 1)
    ORDER BY  IFNULL(active,  -1) DESC,
              IFNULL(created, -1) DESC,
              IFNULL(prid,    -1) DESC ;

赞(0）回复(0）举报 2021-06-21

我来回答

sql-每个名称对应一行

2条答案

相关问题

热门标签

最新问答