如何停止这种重复并按日期分组 [英] How to stop this repetition and group by date

问题描述

多次尝试后，我无法从此查询获得所需的结果.

After multiple attempts I'm unable to get my required result from this query.

SELECT 
cc.date as credi_date,
cd.date as debit_date,
cd.month AS month,
ROUND(IFNULL(cc.credit_amount,0),2) AS credit,
ROUND(IFNULL(cd.debit_amount,0),2) AS debit
FROM 
(SELECT
DATE(cc.credit_date) as date,
MONTHNAME(cc.credit_date) as month,
IFNULL(SUM(cc.credit_amount),0) AS credit_amount
FROM
cust_credit cc 
WHERE YEAR(cc.credit_date) = YEAR(NOW()) 
GROUP BY DATE(cc.credit_date)) cc

INNER JOIN 

(SELECT
 DATE(cd.debit_date) as date,
MONTHNAME(cd.debit_date) as month,
IFNULL(SUM(cd.debit_amount),0) AS debit_amount
FROM
cust_debit cd
WHERE YEAR(cd.debit_date) = YEAR(NOW()) 
GROUP BY DATE(cd.debit_date)) cd ON cc.month=cd.month

问题是这个查询重复了 rows 多次.我不知道是什么导致了这种重复以及如何解决这个问题.结果的image如下.

The problem is that this query repeats the rows multiple times. I don't know what thing cause this repetition and how can fix this. The image of result is given below.

我需要的结果是

我的信用表如下

我的借记表如下

推荐答案

你应该在子查询中使用 UNION，然后在 SELECT 中使用 GROUP BY:

Yon should use a UNION in the subquery and then GROUP BY in the SELECT:

SELECT my_date, 
       my_month, 
       SUM(credit), 
       SUM(debit)
FROM (
    SELECT cc.credit_date as my_date,
           MONTHNAME(cc.credit_date) as my_month,
           ROUND(SUM(IFNULL(cc.credit_amount,0)),2) AS credit,
           0 AS debit
    FROM   cust_credit cc
    WHERE YEAR(cc.credit_date) = YEAR(NOW()) 
    GROUP BY cc.credit_date,
             MONTHNAME(cc.credit_date)
    UNION
    SELECT 
           cd.debit_date ,
           MONTHNAME(cd.debit_date) ,
           0 ,
           ROUND(SUM(IFNULL(cd.debit_amount,0)),2)
    FROM   cust_debit cd
    WHERE YEAR(cd.debit_date) = YEAR(NOW()) 
    GROUP BY cd.debit_date, 
             MONTHNAME(cd.debit_date) 
) as TT
GROUP BY my_date,my_month

这就是小提琴

这篇关于如何停止这种重复并按日期分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！