mysql - 选择最大Mysql所在位置

Question

请帮助让sql选择数据库。有这样的数据。

我的 table 是:

id  news_id  season  seria   date_update
---|------|---------|-----|--------------------
1  | 4    | 1       | 7   | 2017-04-14 16:38:10
2  | 4    | 1       | 7   | 2017-04-14 17:38:10
5  | 4    | 1       | 7   | 2017-04-14 16:38:10
3  | 4    | 1       | 7   | 2017-04-14 16:38:10
4  | 4    | 1       | 7   | 2017-04-14 16:38:10
6  | 4    | 1       | 7   | 2017-04-14 16:38:10
7  | 4    | 1       | 7   | 2017-04-14 16:38:10
8  | 1    | 1       | 25  | 2017-04-23 18:42:00

需要将所有单元格按最大季节、系列和日期分组，并按 date_update DESC 排序。

结果我需要下一行

id  news_id  season  seria   date_update
---|------|---------|-----|--------------------
8  | 1    | 1       | 25  | 2017-04-23 18:42:00
2  | 4    | 1       | 7   | 2017-04-14 17:38:10

因为这一行有最高的季节和序列以及每个新闻 ID 的日期更新。即我需要选择具有最高季节和序列的数据，以及按 news_id 分组并按 date_update DESC 排序的 date_update

我尝试过，但数据并不总是正确的，并且由于某种原因它并不总是覆盖所有符合条件的单元格。

SELECT serial.* 
FROM serial as serial 
INNER JOIN (SELECT id, MAX(season) AS maxseason, MAX(seria) AS maxseria FROM serial GROUP BY news_id) as one_serial 
ON serial.id = one_serial.id 
WHERE serial.season = one_serial.maxseason AND serial.seria = one_serial.maxseria 
ORDER BY serial.date_update 

请帮忙。谢谢。

Answer 1

规范不明确。

但我们确实知道 GROUP BY news_id 子句会将具有公共(public)值 news_id 的所有行折叠成一个单独的行排。 (其他数据库会使用此语法抛出错误；如果我们在 sql_mode 中包含 ONLY_FULL_GROUP_BY，我们可以让 MySQL 抛出类似的错误。)

我的建议是从查询末尾删除 GROUP BY news_id 子句。

但这只是猜测。根本不清楚您想要实现什么目标。

编辑

 SELECT t.* 
   FROM ( 
          SELECT r.news_id
               , r.season
               , r.seria
               , MAX(r.date_update) AS max_date_update
            FROM ( 
                   SELECT p.news_id
                        , p.season
                        , MAX(p.seria) AS max_seria
                     FROM ( 
                            SELECT n.news_id
                                 , MAX(n.season) AS max_season
                              FROM serial n 
                             GROUP BY n.news_id
                          ) o
                     JOIN serial p
                       ON p.news_id = o.news_id
                      AND p.season  = o.max_season
                 ) q
            JOIN serial r
              ON r.news_id = q.news_id
             AND r.season  = q.season
             AND r.seria   = q.max_seria
         ) s
    JOIN serial t      
      ON t.news_id     = s.news_id
     AND t.season      = s.season
     AND t.seria       = s.seria
     AND t.date_update = s.max_date_update
   GROUP BY t.news_id
   ORDER BY t.news_id 

或者，使用 MySQL 用户定义变量的替代方法...

 SELECT s.id
      , s.season
      , s.seria
      , s.date_update 
   FROM ( 
          SELECT IF(q.news_id = @p_news_id,0,1) AS is_max
               , q.id
               , @p_news_id := q.news_id AS news_id
               , q.season
               , q.seria
               , q.date_update
            FROM ( SELECT @p_news_id := NULL ) r
           CROSS
            JOIN serial q
           ORDER
              BY q.news_id     DESC
               , q.season      DESC
               , q.seria       DESC
               , q.date_update DESC
        ) s
  WHERE s.is_max
  ORDER BY s.news_id