如何获取max记录?

3vpjnl9f  于 2021-08-09  发布在  Java
关注(0)|答案(2)|浏览(404)

下面是我的sql代码:

  1. SELECT
  2. v.veh_manufname AS "Manufacturer Name",
  3. COUNT(o.off_no) AS "Total No. of Offences"
  4. FROM
  5. VEHICLE v JOIN OFFENCE o ON v.veh_vin = o.veh_vin
  6. GROUP BY
  7. v.veh_manufname

运行上述sql代码将返回:

  1. Manufacturer Name Total Offences
  2. Audi 3
  3. Honda 1
  4. Mack 3
  5. Kia 1
  6. Mercedes Benz 1
  7. Ferrari 4
  8. Yamaha 1
  9. BMW 2
  10. Mini 1
  11. Toyota 1
  12. Harley Davidson 1
  13. Mazda 4

如何以及在何处放置max aggregate函数,该函数将只提供具有最大违规次数的记录。
所需输出为:

  1. Manufacturer Name Total Offences
  2. Ferrari 4
  3. Mazda 4
nr9pn0ug

nr9pn0ug1#

一个简单的方法是 RANK :

  1. WITH cte AS (
  2. SELECT
  3. v.veh_manufname AS "Manufacturer Name",
  4. COUNT(o.off_no) AS "Total No. of Offences",
  5. RANK() OVER (ORDER BY COUNT(o.off_no) DESC) rnk
  6. FROM VEHICLE v
  7. INNER JOIN OFFENCE o ON v.veh_vin = o.veh_vin
  8. GROUP BY v.veh_manufname
  9. )
  10. SELECT "Manufacturer Name", "Total No. of Offences"
  11. FROM cte
  12. WHERE rnk = 1;

我们还可以使用子查询来仅限制具有最大违规次数的车辆:

  1. SELECT "Manufacturer Name", "Total No. of Offences"
  2. FROM cte
  3. WHERE "Total No. of Offences" = (SELECT MAX("Total No. of Offences") FROM cte);

如果希望直接修改当前查询,而不使用cte,可以在 HAVING 条款:

  1. SELECT
  2. v.veh_manufname AS "Manufacturer Name",
  3. COUNT(o.off_no) AS "Total No. of Offences"
  4. FROM VEHICLE v
  5. INNER JOIN OFFENCE o ON v.veh_vin = o.veh_vin
  6. GROUP BY v.veh_manufname
  7. HAVING COUNT(o.off_no) = (SELECT MAX(max_off)
  8. FROM (SELECT COUNT(o.off_no) AS max_off
  9. FROM VEHICLE v
  10. INNER JOIN OFFENCE o ON v.veh_vin = o.veh_vin
  11. GROUP BY v.veh_manufname) t);
展开查看全部
pexxcrt2

pexxcrt22#

有一种不用子查询的方法可以做到这一点,但有一个问题:
如果有两个或多个制造商的违规次数相同,会怎样?
这个 RANK() 函数是有用的,或者您可以使用 ROW_NUMBER() 在子查询中:

  1. x.offences
  2. From (SELECT v.veh_manufname,
  3. COUNT(o.off_no) OVER (PARTITION BY v.veh_manufname) AS offences,
  4. ROW_NUMBER() OVER (PARTITION BY v.veh_manufname ORDER BY o.off_no DESC) AS n
  5. From VEHICLE v (NOLOCK)
  6. Left Outer Join OFFENCE o (NOLOCK) On o.veh_vin = v.veh_vin
  7. ) AS x
  8. Order By x.offences DESC,
  9. x.n DESC

这将给出一个结果,其中将显示违规次数最多和最近违规次数最多的制造商。

相关问题