MySQL ROLL UP 查询

Question

我的 mysql 查询的最后一行有问题。我只想要最后一列(总计)，其余行我希望它们为 NULL。

查询:

SELECT q.id_socio, q.nome, q.nif, q.num_mecanografico, SUM(IFNULL(qt.quota_nova, quota)) AS 'quota'
FROM 
    (SELECT id_socio, quota_nova FROM QUOTAS_TEMPORARIAS WHERE ANO = 2019 AND MES = 10) 
    AS qt 
RIGHT JOIN 
    (SELECT id_socio, nome, nif, num_mecanografico, quota FROM SOCIO s, TIPO_PAGAMENTO tp, AGRUPAMENTO a, ORGANIZACAO o WHERE s.ID_TIPO_PAGAMENTO = tp.ID_TIPO_PAGAMENTO AND s.id_agrupamento = a.id_agrupamento AND a.id_organizacao = o.id_organizacao AND o.id_organizacao = 1 AND s.id_tipo_pagamento = 1) 
    AS q
ON q.id_socio = qt.id_socio 
GROUP BY q.id_socio WITH ROLLUP 
HAVING q.nome IS NOT NULL AND q.nif IS NOT NULL AND q.num_mecanografico IS NOT NULL OR q.id_socio IS NULL 

结果:

预期结果:

Answer 1

我建议你做这样的事情。

WITH main_query AS (
    SELECT
        id_socio,
        nome,
        nif,
        num_mecanografico,
        IFNULL(
            (SELECT
                quota_nova
            FROM QUOTAS_TEMPORARIAS temp_quota
            WHERE id_socio = temp_quota.id_socio
                AND ANO = 2019
                AND MES = 10),
            quota) quota
    FROM SOCIO socio,
        TIPO_PAGAMENTO tipo_pag,
        AGRUPAMENTO agrup,
        ORGANIZACAO org
    WHERE socio.ID_TIPO_PAGAMENTO = tipo_pag.ID_TIPO_PAGAMENTO
        AND socio.id_agrupamento = agrup.id_agrupamento
        AND agrup.id_organizacao = o.id_organizacao
        AND org.id_organizacao = 1
        AND socio.id_tipo_pagamento = 1
)
    SELECT * FROM main_query
UNION
    SELECT NULL, NULL, NULL, NULL, SUM(quota) FROM main_query

GROUP BY id_socio WITH ROLLUP
HAVING nome IS NOT NULL
    AND nif IS NOT NULL
    AND num_mecanografico IS NOT NULL
    OR id_socio IS NULL

您需要建立一个联合，将总行包含到您的结果中，而无需其他值。事实上，我不建议这样做，对于应用程序查询结果，因为这需要特殊处理才能恢复总结果，我建议您使用两个单独的查询。但现在取决于你了:)