mySQL按组分组数最大

本文介绍了mySQL按组分组数最大的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我已经在这段代码中混淆了相当长的一段时间。首先我把我的SQL表设置为char而不是十进制，因为我不希望数字总是显示一个十进制值，除非它有一个十进制值（是否有另一种方法可以做到这一点？）。我在我的代码中将其更改为小数，但我相信这是问题所在。我试图为每个物种的每个用户提供最大的鱼，但它没有解决问题。

SELECT * FROM（SELECT * FROM entries ORDER BY CAST（weight AS DECIMAL（9,3））DESC）tmp WHERE username =：user GROUP BY species

编辑

我改变了我的表格以使用decimal（10,3）而不是char，并且一直在尝试这个公式

SELECT * FROM（SELECT * FROM entries ORDER BY CAST（weight AS INT）DESC）tmp WHERE username ='BooF'GROUP BY species

但由于某种原因它返回了这个

用户名称位置种类日期长度体重时间戳id BooF Muskellunge Lake Black Crappie 2014-08-31 9.125 0.37 2014-12-20 10:48:06 13 BooF Black Lake Largemouth Bass 2014 -07-03 16.75 2.62 2014-12-20 10:49:00 2 BooF Muskellunge Lake Northern Pike 2014-08-31 32.75 6.86 2014-12-20 10:49:37 14 BooF Lake Bonaparte Rock Bass 2014-09-27 7 0.30 2014-12-20 10:50:50 57 BooF Lake Ozonia Smallmouth Bass 2014-08-15 13 1.19 2014-12-20 10： 51:14 1 BooF斯塔克瀑布水库Walleye 2014-08-15 16 0.97 2014-12-20 10:51:37 49 BooF Lake Bonaparte黄鲈2014-09-27 8.5 0.40 2014-12 -20 10:52:01 56

这是我使用的表格

用户名称位置种类日期长度重量时间戳记ID BooF Lake Ozonia Smallmouth Bass 2014-08-15 13.000 1.190 2014-12-20 10： 51:14 1 BooF Black Lake大嘴巴斯2014-07-03 16.750 2.620 2014-12-20 10:49:00 2 BooF Muskellunge湖大嘴巴斯2014-08-31 12.000 1.000 2014-08 -31 22:04:42 7 BooF Muskellunge Lake Largemouth Bass 2014-08-31 16.000 2.000 2014-08-31 22:04:42 8 BooF Muskellunge Lake Largemouth Bass 2014-08-31 14.000 2014年3月1日1.000 1 22:04:42 9 BooF Muskellunge Lake Largemouth Bass 2014-08-31 16.000 2.000 2014-08-31 22:04:42 10 BooF Muskellunge Lake Largemouth Bass 2014-08-31 14.000 2.000 2014-08-31 22:04:42 11 BooF Muskellunge湖大口黑鲈2014-08-31 16.000 2.000 2014-08-31 22:05:53 12 BooF Muskellunge湖黑鳕鱼2014-08 -31 9.125 0.370 2014-12-20 10:48:06 13 BooF Muskellunge Lake Northern Pike 2014-08-31 32.750 6.860 2014-12-20 10:49:37 14 BooF Narrow Lake Northern Pike 2014-03-15 20.000 2.000 2014-09-01 11:08:21 15 BooF Narrow Lake Largemouth Bass 2014-03-15 14.000 1.000 2014-09-01 11:08:21 16 BooF Butterfield Lake Largemouth Bass 2014-05-26 19.000 3.000 2014-09-01 11:08:21 17 BooF巴特菲尔德湖大嘴巴斯2014-05-26 17.000 2.000 2014-09-01 11:08:21 18 BooF Red Lake Northern Pike 2014-06-21 22.000 2.000 2014-09-01 11:08:21 19 BooF Black Lake Largemouth Bass 2014-07-03 15.000 2.000 2014-09-01 11:12:08 20 BooF Black Lake Largemouth Bass 2014-07-03 15.000 2.000 2014-09-01 11:12:08 21 BooF黑湖Largemouth Bass 2014-07-02 17.000 2.000 2014-09-01 11:12:08 22 BooF黑湖Largemouth Bass 2014-07-01 15.000 2.000 2014-09-01 11:12 ：08 23 BooF Black Lake大嘴巴斯2014-07-01 15.000 2.000 2014-09-01 11:12:08 24 BooF Black Lake大嘴巴斯2014-06-30 19.250 4.100 2014-12- 20 10:53:17 25 BooF Black Lake Northern Pike 2014-06-29 26.750 3.940 2014-12-20 10:52:38 26 BooF斯塔克瀑布水库Walleye 2014-08-15 16.000 0.970 2014-12-20 10:51:37 49 BooF Lake Bonaparte黄鲈鱼2014-09-27 8.500 0.400 2014-12-20 10:52:01 56 BooF Lake Bonaparte Rock Bass 2014-09 -27 7.000 0.300 2014-12-20 10:50:50 57

正如您在查询它张贴一个largemo ub低音提供重量为2.62磅的黑色湖泊，但是在我的数据资料中，它显示了4.1中最大的低音，所以这就是我被卡住的地方。

一般来说，子查询中的ORDER BY是没有意义的。 （它只与FETCH FIRST / LIMIT / TOP等结合使用）。

解决方案是使用相关的子查询来查找main查询的当前行的用户名，位置，物种组合。

SELECT * 从条目e1 WHERE用户名=：用户和CAST（AS AS DECIMAL（9,3）的权重）=（从表中选择max（CAST（AS AS DECIMAL（9,3）的权重）） e2 其中e1。用户名= e2.username 和e1.location = e2.location 和e1.species = e2.species）

请注意，对于重量而言，字符仍然是一个不错的选择，因为在比较值时您必须投射两边。回到表格中的小数点！

I have messed around with this code for quite sometime. First thing I have my SQL table setup as char instead of decimal because I don't want the number to always show a decimal value unless it has a decimal value (Is there another way to do this?). I change it to decimal within my code but I believe this is where the problem lies. I am trying to pull the largest fish per user per species but it isn't working out.

SELECT * FROM (SELECT * FROM entries ORDER BY CAST(weight AS DECIMAL(9,3)) DESC) tmp WHERE username = :user GROUP BY species

EDIT

I have changed my table to use decimal(10,3) instead of char and have been trying this formula

SELECT * FROM (SELECT * FROM entries ORDER BY CAST(weight AS INT) DESC) tmp WHERE username = 'BooF' GROUP BY species

But for some reason it returns this

username location species date length weight timestamp id BooF Muskellunge Lake Black Crappie 2014-08-31 9.125 0.37 2014-12-20 10:48:06 13 BooF Black Lake Largemouth Bass 2014-07-03 16.75 2.62 2014-12-20 10:49:00 2 BooF Muskellunge Lake Northern Pike 2014-08-31 32.75 6.86 2014-12-20 10:49:37 14 BooF Lake Bonaparte Rock Bass 2014-09-27 7 0.30 2014-12-20 10:50:50 57 BooF Lake Ozonia Smallmouth Bass 2014-08-15 13 1.19 2014-12-20 10:51:14 1 BooF Stark Falls Reservoir Walleye 2014-08-15 16 0.97 2014-12-20 10:51:37 49 BooF Lake Bonaparte Yellow Perch 2014-09-27 8.5 0.40 2014-12-20 10:52:01 56

This is the table I am working with

username location species date length weight timestamp id BooF Lake Ozonia Smallmouth Bass 2014-08-15 13.000 1.190 2014-12-20 10:51:14 1 BooF Black Lake Largemouth Bass 2014-07-03 16.750 2.620 2014-12-20 10:49:00 2 BooF Muskellunge Lake Largemouth Bass 2014-08-31 12.000 1.000 2014-08-31 22:04:42 7 BooF Muskellunge Lake Largemouth Bass 2014-08-31 16.000 2.000 2014-08-31 22:04:42 8 BooF Muskellunge Lake Largemouth Bass 2014-08-31 14.000 1.000 2014-08-31 22:04:42 9 BooF Muskellunge Lake Largemouth Bass 2014-08-31 16.000 2.000 2014-08-31 22:04:42 10 BooF Muskellunge Lake Largemouth Bass 2014-08-31 14.000 2.000 2014-08-31 22:04:42 11 BooF Muskellunge Lake Largemouth Bass 2014-08-31 16.000 2.000 2014-08-31 22:05:53 12 BooF Muskellunge Lake Black Crappie 2014-08-31 9.125 0.370 2014-12-20 10:48:06 13 BooF Muskellunge Lake Northern Pike 2014-08-31 32.750 6.860 2014-12-20 10:49:37 14 BooF Narrow Lake Northern Pike 2014-03-15 20.000 2.000 2014-09-01 11:08:21 15 BooF Narrow Lake Largemouth Bass 2014-03-15 14.000 1.000 2014-09-01 11:08:21 16 BooF Butterfield Lake Largemouth Bass 2014-05-26 19.000 3.000 2014-09-01 11:08:21 17 BooF Butterfield Lake Largemouth Bass 2014-05-26 17.000 2.000 2014-09-01 11:08:21 18 BooF Red Lake Northern Pike 2014-06-21 22.000 2.000 2014-09-01 11:08:21 19 BooF Black Lake Largemouth Bass 2014-07-03 15.000 2.000 2014-09-01 11:12:08 20 BooF Black Lake Largemouth Bass 2014-07-03 15.000 2.000 2014-09-01 11:12:08 21 BooF Black Lake Largemouth Bass 2014-07-02 17.000 2.000 2014-09-01 11:12:08 22 BooF Black Lake Largemouth Bass 2014-07-01 15.000 2.000 2014-09-01 11:12:08 23 BooF Black Lake Largemouth Bass 2014-07-01 15.000 2.000 2014-09-01 11:12:08 24 BooF Black Lake Largemouth Bass 2014-06-30 19.250 4.100 2014-12-20 10:53:17 25 BooF Black Lake Northern Pike 2014-06-29 26.750 3.940 2014-12-20 10:52:38 26 BooF Stark Falls Reservoir Walleye 2014-08-15 16.000 0.970 2014-12-20 10:51:37 49 BooF Lake Bonaparte Yellow Perch 2014-09-27 8.500 0.400 2014-12-20 10:52:01 56 BooF Lake Bonaparte Rock Bass 2014-09-27 7.000 0.300 2014-12-20 10:50:50 57

As you can see after the query it posts a largemouth bass for black lake weighing 2.62 pounds but on my data sheet it shows the largest bass at 4.1 so this is where I am stuck.

解决方案

In general ORDER BY in a sub-query makes no sense. (It only does when combined with FETCH FIRST/LIMIT/TOP etc.)

The solution is to use a correlated sub-query to find the heaviest fish for the "main query"'s current row's username, location, species combination. If it's a tie, both rows will be returned.

SELECT * FROM entries e1 WHERE username = :user AND CAST(weight AS DECIMAL(9,3)) = (select max(CAST(weight AS DECIMAL(9,3))) from entries e2 where e1.username = e2.username and e1.location = e2.location and e1.species = e2.species)

Note that char for weight is still a bad choice, beacause of that you have to cast both sides when comparing values. Go back to decimal in your table!