有张表数据格式如下 :
Id | connectname | region | amount |
1 | a01 | 1 | 100.0 |
2 | A002 | 2 | 105.0 |
3 | a01 | 2 | 50.0 |
4 | A3 | 2 | 60.0 |
5 | b04 | 1 | 200.0 |
6 | b001 | 2 | 90.0 |
.......
现在要根据connectname按region 分组统计amount的总量,要求按connectname,region排序,需要的结果展示如下:
1 | a01 | 1 | 100.0 |
2 | a01 | 1 | 50.0 |
3 | A002 | 2 | 105.0 |
4 | A3 | 2 | 60.0 |
5 | b001 | 2 | 90.0 |
6 | b04 | 1 | 200.0 |
刚开始客户那边登记的connectname不严谨,导致出现的名称长短不一,但是还好名称都是以【单英文字母】+【数字】格式组合出来的。想到用order by case when 组合可以实现。
SELECT tbb.connectname AS connectname
,trg.regionname
,FORMAT(FLOOR(SUM(tbb.sumamount) / 1000) * 1000,0) AS sumamount
FROM tb_brushmap tbb
LEFT JOIN tb_region trg ON(tbb.region = trg.regionid)
WHERE tbb.reporttime - 24 * 60 * 60 BETWEEN @todaystart AND @todayend
GROUP BY connectname,region
ORDER BY (
CASE WHEN ASCII(LEFT(UPPER(tbb.connectname),1)) >= 65 AND ASCII(LEFT(UPPER(tbb.connectname),1)) <= 97 THEN (CAST(SUBSTRING(tbb.connectname,2) AS SIGNED INTEGER) + ASCII(LEFT(UPPER(tbb.connectname),1)) * 100)
END
)
,region
;
<待续>