仅返回最大值小于指定值的行

本文介绍了仅返回最大值小于指定值的行的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

嗨(对我来说)，这是一个棘手的问题.我有三张桌子.

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