sql - 选择 sum where 子句 SQL

Question

我有以下代码，它可以工作，但我试图为每个 Banksphere.servicio_id 列分隔 SUM，此代码 SUM 只有一个 servicio_id ......我有点迷茫，有人可以帮助我吗？

正如您所看到的，每个 WHERE 子句都完全相同，但 Banksphere.peticion_id 是唯一改变的子句......所以也许有更好的方法来过滤一次通用子句并只为 OK 和 KO 留下 peticion_id？

SELECT
(SELECT
    SUM(valor)
FROM
    Banksphere
WHERE
    Banksphere.fecha = '2013-01-14'
AND
    Banksphere.servicio_id = '6'
AND
    Banksphere.entidad_id = '2'
AND
    Banksphere.peticion_id = '0') AS OK,
(SELECT
    SUM(valor)
FROM
    Banksphere
WHERE
    Banksphere.fecha = '2013-01-14'
AND
    Banksphere.servicio_id = '6'
AND
    Banksphere.entidad_id = '2'
AND
    Banksphere.peticion_id = '1') AS KO

使用工作代码编辑

SELECT  Servicios.nombre as servicio,
        SUM(case when peticion_id = '0' then valor end) as OK,
        SUM(case when peticion_id = '1' then valor end) as KO
FROM    Banksphere
INNER JOIN
    Servicios
ON
    Banksphere.servicio_id = Servicios.id
WHERE   Banksphere.fecha = '2013-01-14'
        AND Banksphere.entidad_id = '2'
        AND Banksphere.peticion_id in ('0', '1')
group by Servicios.nombre

Answer 1

我认为您想要以下方面的内容:

SELECT  banksphere.servicio_id, SUM(valor),
        SUM(case when peticion_id = '0' then valor end) as OK,
        SUM(case when peticion_id = '1' then valor end) as KO
FROM    Banksphere
WHERE   Banksphere.fecha = '2013-01-14'
        AND Banksphere.entidad_id = '2'
        AND Banksphere.peticion_id in ('0', '1', ...)
group by banksphere.servicio_id

这有一个 group by所以你可以获得多个“servicio_ids”，它为 OK 和 KO 添加了单独的列。如果您只想要 servicio_id = 6，则将其添加回 where条款。而且，您可能需要 group by 中的其他变量也可以，但是您只在问题中提到了服务。