GROUP BY只有具有特定条件和日期的行 [英] GROUP BY only rows with a certain condition and date

问题描述

我在我的数据库中有一个基本的表格，它有一个简单的交易记录，例如存款到账户，取款和退款。每个条目都有一个相关的日期。

我有一个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, $100
Jan 3, Deposit, $200
Jan 2, Withdraw, $50
Jan 2, Refund, $100
Jan 2, Refund, $100
Jan 2, Deposit, $200
Jan 2, Refund, $100
Jan 1, Deposit, $100

How I would like the data to display
Jan 3, Deposit, $100
Jan 3, Deposit, $200
Jan 2, Withdraw, $50
Jan 2, Refund, $300 < this row has collapsed all Refunds for Jan 2nd into 1 row
Jan 2, Deposit, $200
Jan 1, Deposit, $100

The current SQL is something like:

SELECT transaction_date, transaction_type, transaction_amount
FROM transaction_history
WHERE customer_id = $customer_id
ORDER BY transaction_date DESC

解决方案

give this a try,

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 Demo

这篇关于GROUP BY只有具有特定条件和日期的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！