SQL查询以获取与另一列的MAX值对应的列值?

2021-11-20 00:00:00 max sql group-by mysql aggregate-functions

好的，这是我的查询:

SELECT
  video_category,
  video_url,
  video_date,
  video_title,
  short_description,
  MAX(video_id) 
FROM
  videos
GROUP BY
  video_category

当它提取数据时，我得到了 video_id 的正确行，但它为其他类别提取了每个类别的第一行.因此，当我获得类别 1 的 video_id 的最大结果时，我获得了最大 ID，但表格中的第一行是 url、日期、标题和描述.

When it pulls the data, I get the correct row for the video_id, but it pulls the first row for each category for the others. So when I get the max result for the video_id of category 1, I get the max ID, but the first row in the table for the url, date, title, and description.

我怎样才能让它拉出与最大 ID 结果相对应的其他列?

How can I have it pull the other columns that correspond with the max ID result?

固定.

SELECT
    *
FROM
    videos
WHERE
    video_id IN
    (
        SELECT
            DISTINCT
            MAX(video_id)
        FROM
            videos
        GROUP BY
            video_category
    ) 
ORDER BY
    video_category ASC

推荐答案

我会尝试这样的事情:

SELECT
   s.video_id
   ,s.video_category
   ,s.video_url
   ,s.video_date
   ,s.video_title
   ,short_description
FROM videos s
   JOIN (SELECT MAX(video_id) AS id FROM videos GROUP BY video_category) max
      ON s.video_id = max.id

这比您自己的解决方案要快得多

which is quite faster that your own solution

相关文章