仅返回最大值小于指定值的行 [英] Return only rows whose max value is less than specified
问题描述
嗨(对我来说),这是一个棘手的问题.我有三张桌子.
Hi this is quite a tricky one (for me). I have three tables.
一个包含手机号码和唯一ID(tbldealermobiles)
One contains mobile numbers and a unique id (tbldealermobiles)
FCS - Mobile number
1234 - 07464648787
1234 - 07565465465
3566 - 07878989989
7899 - 07464646466
7899 - 07565465464
9654 - 07589898998
其中一个包含购买日期,唯一的ID和其他详细信息(历史记录)
One contains purchase dates and a unique id and other details (tblhistory)
FCS - purchase date - purchased
1234 - 22/04/2013 - gloves
1234 - 14/03/2013 - hat
1234 - 01/03/2013 - coat
3566 - 20/04/2013 - gloves
3566 - 19/04/2012 - hat
7899 - 14/03/2013 - shoes
9654 - 24/05/2013 - hat
9654 - 19/04/2013 - shoes
一个包含客户类型和唯一ID及其他详细信息. (tblAllDealers)
One contains customer type and a unique id and other details. (tblAllDealers)
FCS - Cust type - name
1234 - Virtual - Jim
3566 - Outbound - Jon
7899 - Virtual - Jack
9654 - Outbound - Susan
当我想显示购买了超过30天(如果是"Outbound")和购买超过60天(如果是虚拟)的客户时,我的问题就来了.
My problem comes when I want to display customers who have bought more than 30 days ago if they're 'Outbound' and more than 60 days ago if they're virtual.
我只想返回Jon和Jack的所有手机号码,因为其他人是从为他们的客户类型指定的日期起购买的.
I only want to return all the mobile numbers for Jon and Jack because the others have purchased since the dates specified for their customer type.
我正在使用INNER JOIN链接唯一ID(FCS)上的3个表,我正在使用MAX仅返回MAX值小于日期的值,但是我不知道如何添加条件来指定两个不同的日期.
I'm using INNER JOIN to link the 3 tables on the unique id(FCS), I'm using MAX to return only values who's MAX value is less than a date but I have no clue how to add criteria to specify two different dates.
这是我到目前为止的查询-
Here is the query I have so far -
SELECT *
FROM tbldealermobiles
INNER JOIN tblhistory ON tbldealermobiles.FCS = tblhistory.FCS
INNER JOIN tblAllDealers ON tbldealermobiles.FCS = tblAllDealers.FCS
WHERE (tblAllDealers.CustGroup = 'Virtual' AND
tblhistory.PurchaseDate < date('2013-03-22'))
OR
(tblAllDealers.CustGroup = 'Outbound' AND
tblhistory.PurchaseDate < date('2013-04-21'))
GROUP BY tbldealermobiles.mobilenumber
HAVING MAX(tblhistory.PurchaseDate) < date('2013-04-21')
ORDER BY tblhistory.PurchaseDate DESC
问题在于,FCS的购买日期可能总是早于指定的日期,但是我只想返回一个在指定日期之后(具体取决于客户组)没有购买日期的手机号码.
The problem is that the FCS will probably always have a purchase date earlier than the date specified but I only want to return a mobile number that hasn't got a purchase date after the date specified depending on customer group.
预先感谢您的帮助.
感谢您格式化我的代码Dukeling.
thanks for formatting my code Dukeling.
推荐答案
您要将条件从联接中拉到having
子句中.联接只在这些日期之前查看记录,因此您不知道之后是否会发生任何事情.
You want to pull the condition from the join into a having
clause. The join is only looking at records before those dates, so you don't know if anything happens afterwards.
SELECT *
FROM tbldealermobiles
INNER JOIN tblhistory ON tbldealermobiles.FCS = tblhistory.FCS
INNER JOIN tblAllDealers ON tbldealermobiles.FCS = tblAllDealers.FCS
WHERE tblAllDealers.CustGroup in ('Virtual', 'Outbound')
GROUP BY tbldealermobiles.mobilenumber
HAVING MAX(tblhistory.PurchaseDate) <
MAX(case when tblAllDealers.CustGroup = 'Virtual' then date('2013-03-22')
when tblAllDealers.CustGroup = 'Outbound' then date('2013-04-21')
end)
ORDER BY tblhistory.PurchaseDate DESC
这篇关于仅返回最大值小于指定值的行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!