作者：sense宏江 | 来源：互联网 | 2023-01-18 12:18
Dears,下面这种语句如何简化？就是从表中最大日期的所有数据，归类各类数据。select10astype,count(*)fromssenwherepercent>
Dears,
下面这种语句如何简化？ 就是从表中最大日期的所有数据，归类各类数据。
select 10 as type,count(*) from ssen where percent>=10.1 and date in (select date from ssen order by date DESC limit 1)
union
select 9 as type,count(*) from ssen where percent>=9 and percent<10.1 and date in (select date from ssen order by date DESC limit 1)
union
select 8 as type,count(*) from ssen where percent>=8 and percent<9 and date in (select date from ssen order by date DESC limit 1)
union
select 7 as type,count(*) from ssen where percent>=7 and percent<8 and date in (select date from ssen order by date DESC limit 1)
union
select 6 as type,count(*) from ssen where percent>=6 and percent<7 and date in (select date from ssen order by date DESC limit 1)
union
select 5 as type,count(*) from ssen where percent>=5 and percent<6 and date in (select date from ssen order by date DESC limit 1)
union
select 4 as type,count(*) from ssen where percent>=4 and percent<5 and date in (select date from ssen order by date DESC limit 1)
union
select 3 as type,count(*) from ssen where percent>=3 and percent<4 and date in (select date from ssen order by date DESC limit 1)
union
select 2 as type,count(*) from ssen where percent>=2 and percent<3 and date in (select date from ssen order by date DESC limit 1)
union
select 1 as type,count(*) from ssen where percent>=1 and percent<2 and date in (select date from ssen order by date DESC limit 1)
union
select 0 as type,count(*) from ssen where percent>=0 and percent<1 and date in (select date from ssen order by date DESC limit 1)
union
select -11 as type,count(*) from ssen where percent<=-10.1 and date in (select date from snbk order by date DESC limit 1)
union
select -10 as type,count(*) from ssen where percent<=-9 and percent>-10.1 and date in (select date from ssen order by date DESC limit 1)
union
select -9 as type,count(*) from ssen where percent<=-8 and percent>-9 and date in (select date from ssen order by date DESC limit 1)
union
select -8 as type,count(*) from ssen where percent<=-7 and percent>-8 and date in (select date from ssen order by date DESC limit 1)
union
select -7 as type,count(*) from ssen where percent<=-6 and percent>-7 and date in (select date from ssen order by date DESC limit 1)
union
select -6 as type,count(*) from ssen where percent<=-5 and percent>-6 and date in (select date from ssen order by date DESC limit 1)
union
select -5 as type,count(*) from ssen where percent<=-4 and percent>-5 and date in (select date from ssen order by date DESC limit 1)
union
select -4 as type,count(*) from ssen where percent<=-3 and percent>-4 and date in (select date from ssen order by date DESC limit 1)
union
select -3 as type,count(*) from ssen where percent<=-2 and percent>-3 and date in (select date from ssen order by date DESC limit 1)
union
select -2 as type,count(*) from ssen where percent<=-1 and percent>-2 and date in (select date from ssen order by date DESC limit 1)
union
select -1 as type,count(*) from ssen where percent<0 and percent>-1 and date in (select date from ssen order by date DESC limit 1)
结果如下：
type count(*)
-11 0
-10 13
-9 7
-8 7
-7 7
-6 28
-5 32
-4 60
-3 106
-2 165
-1 160
0 275
1 87
2 59
3 30
4 26
5 13
6 15
7 1
8 1
9 15
10 0
2 个解决方案
[尝试一下CASE WHEN, 谢谢楼上，但还有一个小问题。
SELECT CASE
WHEN percent>=10.1 THEN 10
WHEN percent>=9 and percent<10.1 THEN 9
WHEN percent>=8 and percent<9 THEN 8
WHEN percent>=7 and percent<8 THEN 7
WHEN percent>=6 and percent<7 THEN 6
WHEN percent>=5 and percent<6 THEN 5
WHEN percent>=4 and percent<5 THEN 4
WHEN percent>=3 and percent<4 THEN 3
WHEN percent>=2 and percent<3 THEN 2
WHEN percent>=1 and percent<2 THEN 1
WHEN percent>=0 and percent<1 THEN 0
WHEN percent<=-10.1 THEN -11
WHEN percent<=-9 and percent>-10.1 THEN -10
WHEN percent<=-8 and percent>-9 THEN -9
WHEN percent<=-7 and percent>-8 THEN -8
WHEN percent<=-6 and percent>-7 THEN -7
WHEN percent<=-5 and percent>-6 THEN -6
WHEN percent<=-4 and percent>-5 THEN -5
WHEN percent<=-3 and percent>-4 THEN -4
WHEN percent<=-2 and percent>-3 THEN -3
WHEN percent<=-1 and percent>-2 THEN -2
WHEN percent<0 and percent>-1 THEN -1
ELSE NULL END percent,
COUNT(*) as count FROM ssen WHERE date in (select date from ssen order by date DESC limit 1)
GROUP BY CASE
WHEN percent>=10.1 THEN 10
WHEN percent>=9 and percent<10.1 THEN 9
WHEN percent>=8 and percent<9 THEN 8
WHEN percent>=7 and percent<8 THEN 7
WHEN percent>=6 and percent<7 THEN 6
WHEN percent>=5 and percent<6 THEN 5
WHEN percent>=4 and percent<5 THEN 4
WHEN percent>=3 and percent<4 THEN 3
WHEN percent>=2 and percent<3 THEN 2
WHEN percent>=1 and percent<2 THEN 1
WHEN percent>=0 and percent<1 THEN 0
WHEN percent<=-10.1 THEN -11
WHEN percent<=-9 and percent>-10.1 THEN -10
WHEN percent<=-8 and percent>-9 THEN -9
WHEN percent<=-7 and percent>-8 THEN -8
WHEN percent<=-6 and percent>-7 THEN -7
WHEN percent<=-5 and percent>-6 THEN -6
WHEN percent<=-4 and percent>-5 THEN -5
WHEN percent<=-3 and percent>-4 THEN -4
WHEN percent<=-2 and percent>-3 THEN -3
WHEN percent<=-1 and percent>-2 THEN -2
WHEN percent<0 and percent>-1 THEN -1
ELSE NULL END;
结果少了count为0的统计，应该如何加上？
percent count
-10 13
-9 7
-8 7
-7 7
-6 28
-5 32
-4 60
-3 106
-2 165
-1 160
0 275
1 87
2 59
3 30
4 26
5 13
6 15
7 1
8 1
9 15
少了-11和10的行，应该都是count为0的。]
京公网安备 11010802041100号