查询返回每行的最大数字

2 回答

• 1

  使用子查询尝试以下内容 .

  注意：您可以使用临时表作为内部子查询 .

  select day_id,section_mod_id,totalColumns from 
      (
      select t1.day_id ,t1.section_mod_id,t1.totalColumns
      ,count(*) as rowNumber 
      from        
           (SELECT  day_id, section_mod_id, count(day_id) as totalColumns FROM supervision sup
             INNER JOIN section_mod s ON sup.section = s.section_mod_id  GROUP BY day_id, section_mod_id 
           ) t1
            inner join 
           (SELECT  day_id, section_mod_id, count(day_id) as totalColumns FROM supervision sup
             INNER JOIN section_mod s ON sup.section = s.section_mod_id  GROUP BY day_id, section_mod_id 
           )t2 
         on t1.section_mod_id=t2.section_mod_id
         and t1.totalColumns<=t2.totalColumns
      group by t1.day_id, t1.section_mod_id,t1.totalColumns
      ) t where rowNumber=1
  

  回复于 2024-05-05T17:23:09+08:00
• 1

  在大多数数据库中，您可以使用ANSI标准 row_number() 函数：

  SELECT sm.*
  FROM (SELECT day_id, section_mod_id, count(*) as totalColumns,
               ROW_NUMBER() OVER (PARTITION BY section_mod_id ORDER BY count(*) DESC) as seqnum
        FROM supervision s INNER JOIN
             section_mod m ON s.section = m.section_mod_id
        GROUP BY day_id, section_mod_id 
       ) sm
  WHERE seqnum = 1;
  

  在MySQL中，最简单的方法可能是这个技巧：

  SELECT SUBSTRING_INDEX(GROUP_CONCAT(day_id ORDER BY totalColumns DESC), ',', 1) as day_id,
         section_mod_id, MAX(totalColumns)
  FROM (SELECT day_id, section_mod_id, count(*) as totalColumns
        FROM supervision s INNER JOIN
             section_mod m ON s.section = m.section_mod_id
        GROUP BY day_id, section_mod_id 
       ) sm
  WHERE seqnum = 1
  GROUP BY section_mod_id
  

  回复于 2024-05-05T17:23:09+08:00