按列分组.如果为空，则按其他列分组MySQL [英] Group by column. If null, group by other column MySQL

问题描述

我有这个查询:

SELECT 
 vcl.id,
     vcl.batch_id,
     vcl.type,
     vcl.amount,
     vcl.date
FROM vrcorporateledger vcl
LEFT JOIN payroll_list pl ON pl.id = vcl.batch_id

给出以下输出:

每当有"CREDIT"字样时，在col类型中，我想通过col金额中的值来增加运行余额；每当有借方"在col类型中，我想按batch_id col分组后，以col金额中的值减少累计余额.因此，预期结果是:

Whenever there is "CREDIT" in col type I want to increase the running balance by the value in col amount; whenever there is "DEBIT" in col type I want to decrease the accumulated balance by the value in col amount after grouping by batch_id col. So expected result is:

1000-2 + 5-4-49 = 950.

1000-2+5-4-49=950.

如果可能的话，我还想创建一个列"balance"(余额).在每个点/每个步骤，我都会看到所产生的平衡.

If possible I want to also create a column "balance" where at each point/step I see the resulting balance.

预期的输出，例如:

推荐答案

WITH cte AS (
SELECT type,
       SUM(amount) OVER (PARTITION BY CASE type WHEN 'CREDIT' THEN RAND()
                                                WHEN 'DEBIT' THEN batchID
                                                ELSE 0 END ) amount,
       MIN(`date`) OVER (PARTITION BY CASE type WHEN 'CREDIT' THEN RAND()
                                                WHEN 'DEBIT' THEN batchID
                                                ELSE 0 END ) `date`,
       SUM(CASE type WHEN 'CREDIT' THEN amount
                     WHEN 'DEBIT' THEN -amount
                     ELSE 0 END) OVER (ORDER BY `date`) balance,
       batchID,
       LEAD(batchID) OVER (ORDER BY `date`) next_batchID
FROM source_data
)
SELECT type, 
       amount, 
       balance, 
       `date`
FROM cte
WHERE CASE WHEN batchID = next_batchID THEN 0 ELSE 1 END

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle= 75255728f6d64a91a2ebf62edc2d0a0b

这篇关于按列分组.如果为空，则按其他列分组MySQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！