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

好吧，这是我的疑问:

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 的最大结果时，我得到的是 max ID，但是是表中的 url、 date、 title 和 description 的第一行。

如何让它拉出与最大 ID 结果对应的其他列？

编辑: 固定。

SELECT
*
FROM
videos
WHERE
video_id IN
(
SELECT
DISTINCT
MAX(video_id)
FROM
videos
GROUP BY
video_category
)
ORDER BY
video_category ASC
128896 次浏览
小开
最佳答案

I would try something like this:

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

小开

Here is a more general solution (handles duplicates)

CREATE TABLE test(
i INTEGER,
c INTEGER,
v INTEGER
);




insert into test(i, c, v)
values
(3, 1, 1),
(3, 2, 2),
(3, 3, 3),
(4, 2, 4),
(4, 3, 5),
(4, 4, 6),
(5, 3, 7),
(5, 4, 8),
(5, 5, 9),
(6, 4, 10),
(6, 5, 11),
(6, 6, 12);






SELECT t.c, t.v
FROM test t
JOIN (SELECT test.c, max(i) as mi FROM test GROUP BY c) j ON
t.i = j.mi AND
t.c  = j.c
ORDER BY c;
小开

I recently released a new technique to handle this type of problem in MySQL.

SCALAR-AGGREGATE REDUCTION

Scalar-Aggregate Reduction is by far the highest-performance approach and simplest method (in DB engine terms) for accomplishing this, because it requires no joins, no subqueries, and no CTE.

For your query, it would look something like this:

SELECT
video_category,
MAX(video_id) AS video_id,
SUBSTRING(MAX(CONCAT(LPAD(video_id, 11, '0'), video_url)), 12) AS video_url,
SUBSTRING(MAX(CONCAT(LPAD(video_id, 11, '0'), video_date)), 12) AS video_date,
SUBSTRING(MAX(CONCAT(LPAD(video_id, 11, '0'), video_title)), 12) AS video_title,
SUBSTRING(MAX(CONCAT(LPAD(video_id, 11, '0'), short_description)), 12) AS short_description
FROM
videos
GROUP BY
video_category

The combination of scalar and aggregate functions does the following:

  1. LPADs the intra-aggregate correlated identifier to allow proper string comparison (e.g. "0009" and "0025" will be properly ranked). I'm LPADDING to 11 characters here assuming an INT primary key. If you use a BIGINT, you will want to increase this to support your table's ordinality. If you're comparing on a DATETIME field (fixed length), no padding is necessary.
  2. CONCATs the padded identifier with the output column (so you get "00000000009myvalue" vs "0000000025othervalue")
  3. MAX the aggregate set, which will yield "00000000025othervalue" as the winner.
  4. SUBSTRING the result, which will truncate the compared identifier portion, leaving only the value.

If you want to retrieve values in types other than CHAR, you may need to performa an additional CAST on the output, e.g. if you want video_date to be a DATETIME:

CAST(SUBSTRING(MAX(CONCAT(LPAD(video_id, 11, '0'), video_date)), 12) AS DATETIME)

Another benefit of this method over the self-joining method is that you can combine other aggregate data (not just latest values), or even combine first AND last item in the same query, e.g.

SELECT
-- Overall totals
video_category,
COUNT(1) AS videos_in_category,
DATEDIFF(MAX(video_date), MIN(video_date)) AS timespan,
    

-- Last video details
MAX(video_id) AS last_video_id,
SUBSTRING(MAX(CONCAT(LPAD(video_id, 11, '0'), video_url)), 12) AS last_video_url,
...
    

-- First video details
MIN(video_id) AS first_video_id,
SUBSTRING(MIN(CONCAT(LPAD(video_id, 11, '0'), video_url)), 12) AS first_video_url,
...
    

-- And so on

For further details explaining the benefits of this method vs other older methods, my full blog post is here: https://www.stevenmoseley.com/blog/tech/high-performance-sql-correlated-scalar-aggregate-reduction-queries

小开

SELECT video_category,video_url,video_date,video_title,short_description,video_id FROM videos t1 where video_id in (SELECT max(video_id) FROM videos t2 WHERE t1.video_category=t2.video_category );

Please provide your input and output records so that it can be understood properly and tested.

小开

A slightly more "rustic" solution, but should do the job just the same:

SELECT
video_category,
video_url,
video_date,
video_title,
short_description,
video_id
FROM
videos
ORDER BY video_id DESC
LIMIT 1;

In other words, just produce a table with all of the columns that you want, sort it so that your maximum value is at the top, and chop it off so you only return one row.


提交答案