sum over partition by函式的用法-但是查出來的資料格式不好給前臺用，廢棄

ZHOU_VIP發表於2017-04-18

要求：按車牌號HOST_NO分組，合計CAR_PAY金額，最後合計所有金額，還要過濾出本月的時間

說明：雖然查出來了，但是這種資料格式不好給前臺用，前臺要的是列表的格式


{
    "retCode":1,
    "retMsg":"操作成功",
    "list":[
        {
            "recordId":"20161201200445671015895",
            "carEtcNo":"測試0002",
            "carHostNO":"蘇AM10001",
            "carStartEtcTime":"2017-04-01 17:43:50",
            "carEndEtcTime":"2017-04-18 20:05:44",
            "carLine":"三牌樓--古林公園111",
            "carPay":10,
            "carProvince":"320000",
            "carProvinceDesc":"江蘇省"
        },
        {
            "carPay":10,
            "carProvince":"消費合計",
            "carProvinceDesc":""
        },
        {
            "recordId":"20161129180514209018381",
            "carEtcNo":"1333333333",
            "carHostNO":"蘇A2002",
            "carStartEtcTime":"2017-04-02 17:43:50",
            "carEndEtcTime":"2017-04-15 12:12:12",
            "carLine":"中山門到漢中門",
            "carPay":66,
            "carProvince":"120000",
            "carProvinceDesc":"天津市"
        },
        {
            "recordId":"20161129180325044015371",
            "carEtcNo":"1333333333",
            "carHostNO":"蘇A2002",
            "carStartEtcTime":"2017-04-02 17:43:50",
            "carEndEtcTime":"2017-04-15 12:12:12",
            "carLine":"中山門到漢中門",
            "carPay":66,
            "carProvince":"120000",
            "carProvinceDesc":"天津市"
        },
        {
            "recordId":"20161129175757881014238",
            "carEtcNo":"1333333333",
            "carHostNO":"蘇A2002",
            "carStartEtcTime":"2017-04-02 17:43:50",
            "carEndEtcTime":"2017-04-15 12:12:12",
            "carLine":"中山門到漢中門",
            "carPay":14,
            "carProvince":"120000",
            "carProvinceDesc":"天津市"
        },
        {
            "carPay":146,
            "carProvince":"消費合計",
            "carProvinceDesc":""
        },
        {
            "recordId":"20161129180513905010007",
            "carEtcNo":"1222222222",
            "carHostNO":"蘇A2001",
            "carStartEtcTime":"2017-04-02 17:43:50",
            "carEndEtcTime":"2017-04-15 12:12:12",
            "carLine":"中山門到漢中門",
            "carPay":88,
            "carProvince":"120000",
            "carProvinceDesc":"天津市"
        },
        {
            "recordId":"20161129180309437011679",
            "carEtcNo":"1222222222",
            "carHostNO":"蘇A2001",
            "carStartEtcTime":"2017-04-02 17:43:50",
            "carEndEtcTime":"2017-04-15 12:12:12",
            "carLine":"中山門到漢中門",
            "carPay":55,
            "carProvince":"120000",
            "carProvinceDesc":"天津市"
        },
        {
            "carPay":143,
            "carProvince":"消費合計",
            "carProvinceDesc":""
        },
        {
            "recordId":"20170323192547109019398",
            "carEtcNo":"ee94",
            "carHostNO":"人a1002",
            "carStartEtcTime":"2017-04-01 12:12:12",
            "carEndEtcTime":"2017-04-15 12:12:12",
            "carLine":"中山門到漢中門",
            "carPay":16,
            "carProvince":"120000",
            "carProvinceDesc":"天津市"
        },
        {
            "recordId":"20170323192547121011329",
            "carEtcNo":"ee96",
            "carHostNO":"人a1002",
            "carStartEtcTime":"2017-04-03 12:12:12",
            "carEndEtcTime":"2017-04-15 12:12:12",
            "carLine":"中山門到漢中門",
            "carPay":150,
            "carProvince":"120000",
            "carProvinceDesc":"天津市"
        },
        {
            "recordId":"20170323192547128013962",
            "carEtcNo":"ee97",
            "carHostNO":"人a1002",
            "carStartEtcTime":"2017-04-02 12:12:12",
            "carEndEtcTime":"2017-04-15 12:12:12",
            "carLine":"中山門到漢中門",
            "carPay":100,
            "carProvince":"120000",
            "carProvinceDesc":"天津市"
        },
        {
            "recordId":"20170323192547134010071",
            "carEtcNo":"ee98",
            "carHostNO":"人a1002",
            "carStartEtcTime":"2017-04-05 12:12:12",
            "carEndEtcTime":"2017-04-15 12:12:12",
            "carLine":"中山門到漢中門",
            "carPay":100,
            "carProvince":"120000",
            "carProvinceDesc":"天津市"
        },
        {
            "recordId":"20170323192547140018430",
            "carEtcNo":"ee99",
            "carHostNO":"人a1002",
            "carStartEtcTime":"2017-04-20 12:12:12",
            "carEndEtcTime":"2017-04-25 12:12:12",
            "carLine":"中山門到漢中門",
            "carPay":100,
            "carProvince":"120000",
            "carProvinceDesc":"天津市"
        },
        {
            "recordId":"20170323192547147016616",
            "carEtcNo":"ee100",
            "carHostNO":"人a1002",
            "carStartEtcTime":"2017-04-01 12:12:12",
            "carEndEtcTime":"2017-04-15 12:12:12",
            "carLine":"中山門到漢中門",
            "carPay":100,
            "carProvince":"120000",
            "carProvinceDesc":"天津市"
        },
        {
            "recordId":"20170323192547115010841",
            "carEtcNo":"ee95",
            "carHostNO":"人a1002",
            "carStartEtcTime":"2017-04-01 12:12:12",
            "carEndEtcTime":"2017-04-15 12:12:12",
            "carLine":"中山門到漢中門",
            "carPay":100,
            "carProvince":"120000",
            "carProvinceDesc":"天津市"
        },
        {
            "carPay":666,
            "carProvince":"消費合計",
            "carProvinceDesc":""
        },
        {
            "recordId":"20161202101559495018075",
            "carEtcNo":"333",
            "carHostNO":"2900",
            "carStartEtcTime":"2017-04-01 17:43:50",
            "carEndEtcTime":"2017-04-15 12:12:12",
            "carLine":"中山門到漢中門",
            "carPay":44,
            "carProvince":"120000",
            "carProvinceDesc":"天津市"
        },
        {
            "carPay":44,
            "carProvince":"消費合計",
            "carProvinceDesc":""
        },
        {
            "recordId":"20161130111132840016135",
            "carEtcNo":"2656",
            "carHostNO":"15000",
            "carStartEtcTime":"2017-04-02 17:43:50",
            "carEndEtcTime":"2017-04-15 12:12:12",
            "carLine":"中山門到漢中門",
            "carPay":15,
            "carProvince":"120000",
            "carProvinceDesc":"天津市"
        },
        {
            "carPay":15,
            "carProvince":"消費合計",
            "carProvinceDesc":""
        },
        {
            "recordId":"20161202110607795014435",
            "carEtcNo":"003",
            "carHostNO":"14000",
            "carStartEtcTime":"2017-04-02 17:43:50",
            "carEndEtcTime":"2017-04-15 12:12:12",
            "carLine":"中山門到漢中門",
            "carPay":42,
            "carProvince":"120000",
            "carProvinceDesc":"天津市"
        },
        {
            "carPay":42,
            "carProvince":"消費合計",
            "carProvinceDesc":""
        },
        {
            "recordId":"20161130183025739012483",
            "carEtcNo":"2",
            "carHostNO":"1301",
            "carStartEtcTime":"2017-04-01 17:43:50",
            "carEndEtcTime":"2017-04-23 18:47:04",
            "carLine":"中山門到漢中門",
            "carPay":87,
            "carProvince":"120000",
            "carProvinceDesc":"天津市"
        },
        {
            "carPay":87,
            "carProvince":"消費合計",
            "carProvinceDesc":""
        },
        {
            "recordId":"20170221091233259012244",
            "carEtcNo":"112233",
            "carHostNO":"1010",
            "carStartEtcTime":"2017-04-02 17:43:50",
            "carEndEtcTime":"2017-04-23 18:47:04",
            "carLine":"中山門到漢中門",
            "carPay":25,
            "carProvince":"230000",
            "carProvinceDesc":"黑龍江省"
        },
        {
            "carPay":25,
            "carProvince":"消費合計",
            "carProvinceDesc":""
        },
        {
            "recordId":"20161202110501256014722",
            "carEtcNo":"3",
            "carHostNO":"0213",
            "carStartEtcTime":"2017-04-01 17:43:50",
            "carEndEtcTime":"2017-04-23 18:47:04",
            "carLine":"中山門到漢中門",
            "carPay":10,
            "carProvince":"120000",
            "carProvinceDesc":"天津市"
        },
        {
            "carPay":10,
            "carProvince":"消費合計",
            "carProvinceDesc":""
        },
        {
            "carEtcNo":"合計",
            "carPay":1188
        }
    ]
}

參考：

http://blog.csdn.net/wawmg/article/details/40840093

OVER(PARTITION BY)函式用法
2017-02-22
函式
sum()over()和count()over()分析函式
2012-02-09
函式
Oracle 中分析函式用法之--rank(),dense_rank(),partition,over()
2011-04-21
Oracle函式
Oracle統計分析函式集,over(partition by..) 的運用
2010-10-10
Oracle函式
oracle的分析函式over(Partition by...) 及開窗函式
2013-08-20
Oracle函式
sql(oracle)資料處理實用總結開窗函式(over partition)使用
2018-06-05
SQLOracle函式
ROW_NUMBER() OVER() 分析函式的用法
2017-08-28
函式
ROW_NUMBER() OVER函式的基本用法
2014-04-21
函式
語法:OVER(PARTITION BY)及開窗函式
2014-06-30
函式
【Mysql】Mysql似oracle分析函式sum over的實現
2016-04-27
MySqlOracle函式
Oracle 中的 ROW_NUMBER() OVER() 分析函式的用法
2017-06-30
Oracle函式
如何給列表降維？sum()函式的妙用
2019-04-17
函式
如何給列表降維？sum() 函式的妙用
2019-04-23
函式
Python 中求和函式 sum 用法
2017-03-25
Python函式
(轉)Oracle語法之OVER(PARTITION BY)及開窗函式
2009-11-24
Oracle函式
over (partition by)
2015-05-18
Oracle 語法之 OVER (PARTITION BY ..) 及開窗函式轉載
2011-09-09
Oracle函式
【函式】oracle視窗函式over()的理解
2012-04-25
函式Oracle
oracle資料庫常用分析函式與聚合函式的用法
2019-01-27
Oracle資料庫函式
mysql自動排序函式dense_rank() over()、rank() over()、row_num() over()用法和區別
2020-12-31
MySql排序函式
oracle的分析函式over 及開窗函式
2010-08-25
Oracle函式
分析函式 over
2013-09-26
函式
sql server 2005中的分割槽函式用法(partition by 欄位)
2013-11-06
SQLServer函式
Oracle over()函式使用
2010-02-02
Oracle函式
exchange partition 的用法
2007-12-11
over partition簡單使用
2015-10-19
Sql Server資料庫開窗函式Over()的使用例項詳解
2019-01-01
SQLServer資料庫函式
MySQL中的SUM函式使用教程
2021-09-09
MySql函式
分析函式系列之sum(col1) over(partition by col2 order by col3):實現分組彙總或遞增彙總
2020-04-05
函式
row_number() over函式
2013-10-24
函式
GetModuleFileName函式的用法
2021-07-02
函式
createStyleSheet()函式的用法
2017-03-26
函式
qsort函式的用法
2013-08-29
函式
COALESCE函式的用法。
2008-01-08
函式
ROWNUMBER() OVER( PARTITION BY COL1
2021-09-09
BOSS，給點開發james的資料好不好
2002-12-24
ascii函式和substr函式的用法
2007-09-29
ASCII函式
理解了row_number()over (partition by order by )的方法
2008-01-08

sum over partition by函式的用法-但是查出來的資料格式不好給前臺用，廢棄

相關文章