需要更改sql请求 - Need change sql request

Need change sql request

本文关键字：sql 请求 | 更新日期: 2023-09-27

我有sql请求:

SELECT Bank_ID, Status, COUNT(Bank_ID) as Number 
FROM int_client_bank 
WHERE status = 30 or status = 50 or status = 35 or status = 37 
GROUP BY Bank_ID, Status;

见数据:

"Bank_ID"   "Status"    "Number"
"1"         "30"        "772"
"1"         "35"        "58"
"1"         "50"        "151"
"2"         "30"        "124"
"2"         "35"        "27"
"2"         "50"        "25"
"3"         "30"        "227"
"3"         "35"        "16"
"3"         "37"        "1"
"3"         "50"        "143"
"4"         "30"        "337"
"4"         "35"        "23"
"4"         "37"        "1"
"4"         "50"        "98"
"5"         "30"        "72"
"5"         "35"        "7"
{
    "data":[
        {"Bank_Id":"1","Status":"30","Number":"772"},
        {"Bank_Id":"1","Status":"35","Number":"58"},
        ...
    ],
    "success":true
}

我需要改变请求，把数据像这样:

我需要4个状态- 30,35,50,37

 Bank_ID Status30 Status35 Status50 Status37
     1        772       58       151      0
     2        30        27        0       0
{
"data":[
            {"Bank_Id":"1","Status30":"772","Status35":"58","Status50":"151","Status37":",},
{"Bank_Id":"2","Status30":"124","Status35":"27","Status50":"25","Status37":"0"},
...
       ],
       "success":true
       }

我怎么能做json编码或sql请求??

您可以使用IF语句结果的SUM来计算状态为特定值的行数:-

SELECT Bank_ID, 
    SUM(IF(Bank_ID = 30, 1, 0)) as Status30,
    SUM(IF(Bank_ID = 50, 1, 0)) as Status50,
    SUM(IF(Bank_ID = 35, 1, 0)) as Status35,
    SUM(IF(Bank_ID = 37, 1, 0)) as Status37
FROM int_client_bank 
WHERE status IN (30, 50, 35, 37 )
GROUP BY Bank_ID;