MySQL SELECT MIN一直有效，但仅在日期之间返回 [英] MySQL SELECT MIN for all time, but only return if BETWEEN dates

问题描述

我当然可以通过使用PHP遍历结果来做到这一点，但只是想知道是否有人愿意发布更好的解决方案.

这种情况是我有一个交易清单.我选择两个日期并运行报告以获取这两个日期之间的交易...很容易.不过，对于其中一个报告部分，我只需要返回这是他们的第一笔交易即可.

这是我查询的地方:

 SELECT *, MIN(bb_transactions.trans_tran_date) AS temp_first_time 
 FROM 
   bb_business      
   RIGHT JOIN bb_transactions ON bb_transactions.trans_store_id = bb_business.store_id 
   LEFT JOIN bb_member ON bb_member.member_id = bb_transactions.trans_member_id 
 WHERE 
   bb_transactions.trans_tran_date BETWEEN '2010-08-01' AND '2010-09-13' 
   AND bb_business.id = '5651' 
 GROUP BY bb_member.member_id 
 ORDER BY bb_member.member_id DESC

这给了我选定日期之间交易的MIN.我真正需要的是整体MIN，如果它介于两个日期之间.这有道理吗?

我基本上需要知道客户是否在报告期内首次购买.

无论如何，用PHP可以解决的问题并不着急.主要是出于我自己的好奇心和学习.

感谢您传播知识！

我必须编辑查询，因为我在其中遗留了一个试行错误.我还尝试过使用从MIN创建的临时列作为两个日期之间的选择器.那返回了一个错误.

解决方案: 这是经过你们的帮助后经过修改的查询:

SELECT * FROM (
  SELECT 
    bb_member.member_id, 
    MIN(bb_transactions.trans_tran_date) AS first_time
  FROM 
    bb_business 
    RIGHT JOIN bb_transactions ON bb_transactions.trans_store_id = bb_business.store_id 
    LEFT JOIN bb_member ON bb_member.member_id = bb_transactions.trans_member_id 
  WHERE bb_business.id = '5651' 
  GROUP BY bb_member.member_id
) AS T 
WHERE T.first_time BETWEEN '2010-08-01' AND '2010-09-13'

解决方案

如果我们按客户进行最少的所有交易，然后检查是否在正确的时期内，我们得到了与...类似的信息./p>

这只会为您提供关于是否在该时间段内首次购买商品的是/否标志.

SELECT CASE COUNT(*) WHEN 0 THEN 'Yes' ELSE 'No' END As [WasFirstTransInThisPeriod?]
FROM (  
        SELECT bb_member.member_id As [member_id], MIN(bb_transactions.trans_tran_date) AS temp_first_time 
        FROM bb_business      
        RIGHT JOIN bb_transactions ON bb_transactions.trans_store_id = bb_business.store_id 
        LEFT JOIN bb_member ON bb_member.member_id = bb_transactions.trans_member_id 
        WHERE bb_business.id = '5651' 
        GROUP BY bb_member.member_id
    ) T
WHERE T.temp_first_time BETWEEN '2010-08-01' AND '2010-09-13'
ORDER BY T.member_id DESC

(这是在T-SQL中，但是希望可以对如何在mySQL中类似地实现这一点有所了解)

西蒙(Simon)

I can certainly do this by iterating through results with PHP, but just wanted to know if someone had a second to post a better solution.

The scenario is that I have a list of transactions. I select two dates and run a report to get the transactions between those two dates...easy. For one of the reporting sections though, I need to only return the transaction if it was their first transaction.

Here was where I got with the query:

 SELECT *, MIN(bb_transactions.trans_tran_date) AS temp_first_time 
 FROM 
   bb_business      
   RIGHT JOIN bb_transactions ON bb_transactions.trans_store_id = bb_business.store_id 
   LEFT JOIN bb_member ON bb_member.member_id = bb_transactions.trans_member_id 
 WHERE 
   bb_transactions.trans_tran_date BETWEEN '2010-08-01' AND '2010-09-13' 
   AND bb_business.id = '5651' 
 GROUP BY bb_member.member_id 
 ORDER BY bb_member.member_id DESC

This gives me the MIN of the transactions between the selected dates. What I really need is the overall MIN if it falls between the two dates. Does that make sense?

I basically need to know if a customers purchased for the first time in the reporting period.

Anyways, no huge rush as I can solve with PHP. Mostly for my own curiosity and learning.

Thanks for spreading the knowledge!

EDIT: I had to edit the query because I had left one of my trial-errors in there. I had also tried to use the temporary column created from MIN as the selector between the two dates. That returned an error.

SOLUTION: Here is the revised query after help from you guys:

SELECT * FROM (
  SELECT 
    bb_member.member_id, 
    MIN(bb_transactions.trans_tran_date) AS first_time
  FROM 
    bb_business 
    RIGHT JOIN bb_transactions ON bb_transactions.trans_store_id = bb_business.store_id 
    LEFT JOIN bb_member ON bb_member.member_id = bb_transactions.trans_member_id 
  WHERE bb_business.id = '5651' 
  GROUP BY bb_member.member_id
) AS T 
WHERE T.first_time BETWEEN '2010-08-01' AND '2010-09-13'

解决方案

If we do a minimum of all transactions by customer, then check to see if that is in the correct period we get something along the lines of...

This will simply give you a yes/no flag as to whether the customer's first purchase was within the period...

SELECT CASE COUNT(*) WHEN 0 THEN 'Yes' ELSE 'No' END As [WasFirstTransInThisPeriod?]
FROM (  
        SELECT bb_member.member_id As [member_id], MIN(bb_transactions.trans_tran_date) AS temp_first_time 
        FROM bb_business      
        RIGHT JOIN bb_transactions ON bb_transactions.trans_store_id = bb_business.store_id 
        LEFT JOIN bb_member ON bb_member.member_id = bb_transactions.trans_member_id 
        WHERE bb_business.id = '5651' 
        GROUP BY bb_member.member_id
    ) T
WHERE T.temp_first_time BETWEEN '2010-08-01' AND '2010-09-13'
ORDER BY T.member_id DESC

(this is in T-SQL, but should hopefully give an idea of how this can be achieved similarly in mySQL)

Simon

这篇关于MySQL SELECT MIN一直有效，但仅在日期之间返回的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！