Resolvi

Moderador: Moderadores
SELECT X.MES, IFNULL(X.TOTAL,0) FROM (
WITH RECURSIVE Ano AS (
SELECT 1 AS mes
UNION
SELECT mes + 1 AS mes
FROM Ano
WHERE
Ano.mes < 12
)
SELECT * FROM Ano
LEFT JOIN
( SELECT D_VENC AS VENCIMENTO, Sum( apa.vl_guia) AS TOTAL, YEAR(D_VENC) AS ANO
FROM cobrancauti APA
WHERE
APA.D_PGTO IS NULL AND
YEAR( APA.D_VENC ) = 2018
GROUP BY
MONTH( APA.D_VENC )
) AS B ON ANO.MES = MONTH( B.VENCIMENTO )
ORDER BY MES ) X
ORDER BY X.MES
JoséQuintas escreveu:Usou recursive e CTE ao mesmo tempo, ou isso já tinha antes?
asimoes escreveu:Executando essa seleção dá erro
WITH recursive Datas AS (
-- "Membro Âncora"
SELECT
1 AS Mes,
2018 AS Ano,
1 as Loops
UNION ALL
-- "Membro Recursivo"
SELECT
CASE
WHEN Loops % 12 = 0 THEN 1
ELSE Mes + 1
end,
CASE
WHEN Loops % 12 = 0 THEN Ano + 1
ELSE Ano
end,
Loops + 1
FROM Datas /* Resultado da última iteração */
WHERE
Loops < 36
)
SELECT *
FROM (
SELECT
D_VENC AS VENCIMENTO,
Coalesce(Sum(apa.vl_guia), 0) AS TOTAL,
Coalesce(Month(D_VENC), Datas.Mes) AS MES,
Coalesce(YEAR(D_VENC), Datas.Ano) AS ANO
FROM cobrancauti APA
WHERE
APA.D_PGTO IS NULL
AND YEAR(APA.D_VENC) BETWEEN 2018
AND 2020
GROUP BY
MONTH(APA.D_VENC) )
AS b
LEFT JOIN ( SELECT Mes, Ano
FROM Datas
ORDER BY Ano, Mes ) AS d
ON d.Mes = MONTH(B.VENCIMENTO)
AND d.Ano = Year(B.VENCIMENTO)
ORDER BY
b.MES
SELECT X.ANO, X.MES AS MES, IFNULL(X.TOTAL,0) AS TOTAL FROM (
WITH RECURSIVE Ano AS (
SELECT 1 AS MES
UNION
SELECT MES + 1 AS MES
FROM ANO
WHERE
Ano.mes < 12
)
SELECT * FROM ANO
LEFT JOIN
( SELECT D_VENC AS VENCIMENTO, Sum( apa.vl_guia) AS TOTAL, YEAR(D_VENC) AS ANO
FROM cobrancauti APA
WHERE
APA.D_PGTO IS NULL AND
YEAR( APA.D_VENC ) = 2018
GROUP BY
YEAR( APA.D_VENC ), MONTH( APA.D_VENC )
) AS B ON ANO.MES = MONTH( B.VENCIMENTO )
ORDER BY MES ) X
ORDER BY X.MES
WITH recursive Datas AS (
-- "Membro Âncora"
SELECT
1 AS Mes,
2018 AS Ano,
1 as Loops
UNION ALL
-- "Membro Recursivo"
SELECT
CASE
WHEN Loops % 12 = 0 THEN 1
ELSE Mes + 1
end,
CASE
WHEN Loops % 12 = 0 THEN Ano + 1
ELSE Ano
end,
Loops + 1
FROM Datas /* Resultado da última iteração */
WHERE
Loops < 36
)
SELECT *
FROM
Datas D
LEFT JOIN
(
SELECT
D_VENC AS VENCIMENTO,
Coalesce(Sum(apa.vl_guia), 0) AS TOTAL,
Coalesce(Month(D_VENC),null) AS MES,
Coalesce(YEAR(D_VENC), null) AS ANO
FROM cobrancauti APA
WHERE
APA.D_PGTO IS NULL
AND YEAR(APA.D_VENC) BETWEEN 2018
AND 2020
GROUP BY
year(APA.D_VENC), MONTH(apa.d_venc) ) AS RES ON RES.ANO = D.ANO AND RES.MES = D.MES
Usuários vendo este fórum: Nenhum usuário registrado online e 4 visitantes