하위 쿼리에서 MYSQL 그룹을 구성하지만 모든 데이터가 필요합니다.

하위 쿼리에서 MYSQL 그룹을 구성하지만 모든 데이터가 필요합니다.

테이블이 두 개 있는데 하나는 거래이고 다른 하나는 경비입니다.

트랜잭션 테이블:

id  amount  created
1   300     2019-10-01 00:00:00
2   200     2019-11-01 00:00:00
3   230     2019-11-13 00:00:00
4   130     2019-11-13 00:00:00

비용표:

id  amount  created
1   600     2019-11-13 00:00:00

두 표의 합계 금액으로, 저는 아래 문의를 작성했는데, 저는 문제 없습니다.

(SELECT IFNULL(date(t1.created), date(ex.created)) as Date , sum(t1.amount) as ReceiveAmount,ex.amount as ExpensesAmount 
    FROM transactions as t1
    LEFT JOIN (
        SELECT sum(e.amount) as amount, created
           FROM expenses as e 
           group by date(e.created)
        ) as ex
    ON date(ex.created) =  date(t1.created)
    GROUP BY date(t1.created))

    UNION

    (SELECT IFNULL(date(t1.created), date(ex.created)) as Date, sum(t1.amount) as Receive,ex.amount as ExpensesAmount 
    FROM transactions as t1

    RIGHT JOIN (
        SELECT sum(e.amount) as amount, created
        FROM expenses as e 
        group by date(e.created)
    ) as ex
    ON date(t1.created) = date(ex.created)
    GROUP BY date(t1.created)) ORDER BY Date

출력:

Date        ReceiveAmount   ExpensesAmount  
2019-10-01  300             NULL
2019-11-01  200             NULL
2019-11-13  360             600

그러나 이제 합계 없이 양쪽 테이블에서 모든 금액을 가져오려면날짜상 하나의 트랜잭션이 누락되었습니다.2019-11-13.

(SELECT IFNULL(date(t1.created), date(ex.created)) as Date , t1.amount as ReceiveAmount,ex.amount as ExpensesAmount 
FROM transactions as t1
LEFT JOIN (
    SELECT e.amount as amount, created
       FROM expenses as e 
       group by date(e.created)
    ) as ex
ON date(ex.created) =  date(t1.created)
GROUP BY date(t1.created))

UNION

(SELECT IFNULL(date(t1.created), date(ex.created)) as Date, t1.amount as Receive,ex.amount as ExpensesAmount 
FROM transactions as t1

RIGHT JOIN (
    SELECT e.amount as amount, created
    FROM expenses as e 
    group by date(e.created)
) as ex
ON date(t1.created) = date(ex.created)
GROUP BY date(t1.created)) ORDER BY Date

출력:

2019-10-01  300     NULL
2019-11-01  200     NULL
2019-11-13  230     600

여기 날짜 중 하나의 트랜잭션이 누락되었습니다.2019-11-13.

예상 결과:

2019-10-01  300     NULL
2019-11-01  200     NULL
2019-11-13  230     600
2019-11-13  130     NULL

자, 어떻게 하면 원하는 결과를 얻을 수 있을까요?

MySQL 8.0을 실행하고 있는 경우,row_number()각 테이블에서 나오는 레코드에 대해created파티션을 만들고 나서left join:

select t.created, t.amount, e.amount
from (
    select 
        t.*, 
        row_number() over(partition by created order by id)  rn 
    from transactions t
) t
left join (
    select
        e.*, 
        row_number() over(partition by created order by id)  rn 
    from expenses e
) e
    on e.created = t.created and e.rn = t.rn

---

2개의 테이블 중 1개의 테이블이 다른 테이블에 존재하지 않는 날짜를 가질 수 있는 경우는 조금 더 복잡합니다.기본적으로, 우리는 이 기술을 모방할 필요가 있다.full join는 MySQL에 존재하지 않습니다.다음 방법 중 하나가 있습니다.union all:

select created, max(t_amount) t_amount, max(e_amount) e_amount
from (
    select 
        created,
        amount t_amount,
        null e_amount,
        row_number() over(partition by created order by id)  rn 
    from transactions
    union all
    select 
        created,
        null,
        amount,
        row_number() over(partition by created order by id)
    from expenses
) d
group by created, rn

합산하기 싫으면 무리짓지 마세요.다른 rdbms에서는 이 에러가 발생합니다.그룹화하지 않고 모든 기록을 얻을 수 있습니다.

하지만 2019-11-13년 두 거래 모두 expenses가 참여하기 때문에 원하는 결과를 얻지 못할 것 같습니다.

언급URL : https://stackoverflow.com/questions/59319218/mysql-group-by-in-subquery-but-need-all-data