GROUP BY只有具有特定条件和日期的行

本文介绍了GROUP BY只有具有特定条件和日期的行的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 我在我的数据库中有一个基本的表格，它有一个简单的交易记录，例如存款到账户，取款和退款。每个条目都有一个相关的日期。 我有一个php页面，它逐行显示这个事务信息，它看起来很棒。但是，mySQL查询中有一种方法，我可以在白天将所有退款货币交易折叠为分组？在交易类型上执行GROUP_BY相当容易，但我只想如果交易类型属于退款类型，并且这些退款是在同一天发生的，则由群组进行分组。感谢！ 我的当前示例表： Jan 3，存款，$ 100 Jan 3，存款，$ 200 > Jan 2，取款，$ 50 一月二日，退款，$ 100 一月二日，退款，$ 100 一月二日，存款，$ 200 一月二日，退款，$ 100 一月一日，存款，$ 100 $ / $> 我想如何显示数据 1月3日，存款，$ 100 1月3日，存款，$ 200 1月2日，取款，$ 50 1月2日，退款， $ 300 <此行已将所有1月2日的退款折成1行 1月2日，存款，$ 200 1月1日，存款，$ 100 当前的SQL类似于： SELECT transaction_date，transaction_type，transaction_amount FROM transaction_history WHERE customer_id = $ customer_id ORDER BY transaction_date DESC 解决方案试试这个， SELECT transaction_date，transaction_type，SUM（transaction_amount） FROM TableName - WHERE customer_id = $ customer_id GROUP BY transaction_date， CASE WHEN transaction_type ='refund'THEN 1 ELSE RAND（）END ORDER BY transaction_date DESC SQLFiddle演示 I have a basic table in my database that has a simple transactions history such as depositing money to an account, withdrawing money, and having money refunded. Each entry has a date associated with it.I have a php page that displays this transactional information row by row and it looks great. However, is there a way within the mySQL query that I can "collapse" all "refunded" money transactions into groupings by day? It's quite easy to do a GROUP_BY on the transaction type but I only want to group by on if the transaction_type is of type "refund" and those refunds happened on the same day. Thanks!My Current Example Table:Jan 3, Deposit, $100Jan 3, Deposit, $200Jan 2, Withdraw, $50Jan 2, Refund, $100Jan 2, Refund, $100Jan 2, Deposit, $200Jan 2, Refund, $100Jan 1, Deposit, $100How I would like the data to displayJan 3, Deposit, $100Jan 3, Deposit, $200Jan 2, Withdraw, $50Jan 2, Refund, $300 < this row has collapsed all Refunds for Jan 2nd into 1 rowJan 2, Deposit, $200Jan 1, Deposit, $100The current SQL is something like:SELECT transaction_date, transaction_type, transaction_amountFROM transaction_historyWHERE customer_id = $customer_idORDER BY transaction_date DESC 解决方案 give this a try,SELECT transaction_date, transaction_type, SUM(transaction_amount)FROM TableName-- WHERE customer_id = $customer_idGROUP BY transaction_date, CASE WHEN transaction_type = 'refund' THEN 1 ELSE RAND() ENDORDER BY transaction_date DESCSQLFiddle Demo 这篇关于GROUP BY只有具有特定条件和日期的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！