select * from t a join
(select [name],max(shoppingtime)as shoppingtime from t where [name] not in
(select distinct [name] from t where shoppingtime>='20050601' and shoppingtime<getdate())and shopping<'20050601' group by [name])b
on a.[name]=b.[name] and a.shoppingtime=b.shoppingtime
(select [name],max(shoppingtime)as shoppingtime from t where [name] not in
(select distinct [name] from t where shoppingtime>='20050601' and shoppingtime<getdate())and shopping<'20050601' group by [name])b
on a.[name]=b.[name] and a.shoppingtime=b.shoppingtime
Select * from TableName A
Inner Join (Select 姓名,Max(购买日期) As 购买日期 from TableName Group By 姓名) B
On A.姓名=B.姓名 And A.购买日期=B.购买日期
Where A.姓名 Not In (Select Distinct 姓名 from TableName Where DateDiff(dd,购买日期,'2005-06-01')<0 And DateDiff(dd,GetDate(),购买日期)<0 )
参考表如下
id name number date1 a 10 2005-1-12 c 20 2005-1-23 b 15 2005-1-54 a 30 2005-1-65 b 5 2005-1-76 b 10 2005-1-87 c 10 2005-1-10查询2005-1-9以后没有购买过的人和最近一次购买数量和时间的情况结果为4 a 30 2005-1-66 b 10 2005-1-8如果要查询的是2005-1-7以来没有过买过的那么结果变为4 a 30 2005-1-6就是这样的如何实现
insert into test
select 1,'a',10,'2005-1-1' union all
select 2,'c',20,'2005-1-2' union all
select 3,'b',15,'2005-1-5' union all
select 4,'a',30,'2005-1-6' union all
select 5,'b',5,'2005-1-7' union all
select 6,'b',10,'2005-1-8' union all
select 7,'c',10,'2005-1-10'
go
create proc p
@date datetime
as
select a.* from test a
inner join (
select name,max(date) as date
from test
group by name
having max(date)<@date
)t
on a.name=t.name
where not exists(select 1 from test where name=a.name and id>a.id)
goexec p '2005-1-9'
exec p '2005-1-7'drop proc p
drop table test
name,
(select top 1 number from table where name = a.name order by date desc) as zhsl,
(select top 1 date from table where name = a.name order by date desc) as zhrq
from table a
where date < @date
因为你开始没有数据,我不知道你ID的情况。现在可以简写下了。
Where A.name Not In (Select Distinct name from test Where DateDiff(dd,[date],'2005-01-09')<0)
And ID =(Select Max(ID) from test Where name=A.name)
Order By id
Select * from test A
Where A.name Not In (Select Distinct name from test Where DateDiff(dd,[date],'2005-01-07')<0)
And ID =(Select Max(ID) from test Where name=A.name)
Order By id
select distinct a.C1,a.C2
from t_1 a,t_2 b
where a.C1 = b.P1 and a.C2 = b.P2
select c.* from (select max(id) as 'id',姓名 from 表 a group by 姓名) b,表 c where b.id = c.id and c.购买日期 < (指定的日期)