mySQL按组分组数最大 [英] mySQL Largest number by group
问题描述
我已经在这段代码中混淆了相当长的一段时间。首先我把我的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!
这篇关于mySQL按组分组数最大的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
