SELECT a.会员名, MAX(b.消费时间) 消费时间 FROM 会员表 a JOIN 消费记录表 b ON a.会员ID = b.会员ID GROUP BY a.会员名 ORDER BY 消费时间 DESC
select a.*,b.* from 会员表 a left join 消费记录表 b on a.会员ID=b.会员ID left join (select 会员ID,max(消费时间)消费时间 from 消费记录表 group by 会员ID) c on a.会员ID=c.会员ID order by case when b.会员ID is null then 2 else 1 end, c.消费时间 desc, b.消费时间 desc
select * from member m,cashlog c where m.memberId = c.memberId order by c.cashTime desc 最搞人的还是无法实现,总之谢谢大家了。
消费记录表: CREATE TABLE [CashLog] ( [cashId] INT IDENTITY(1,1) PRIMARY KEY, [memberId] INT FOREIGN KEY REFERENCES [Member]([memberId]),--会员ID [pay] MONEY NOT NULL,--收费 [cashTime] DATETIME NOT NULL,--时间 ) GO会员表: CREATE TABLE [Member] ( [memberId] INT IDENTITY(1,1) PRIMARY KEY, [cardId] VARCHAR(20) NOT NULL,--卡号 [[memName] VARCHAR(20) NOT NULL,--姓名 [memMoney] MONEY NOT NULL,--金额 [createTime] DATETIME NOT NULL,--办卡时间 )如此两个表,查询最近消费其实也就是将在消费记录中存在的会员卡号,并以卡号根据消费的时间进行倒序排列。查询出的数据卡号不能重复。
select * from Member m left join CashLog c on m.memberId=c.memberId and not exists( select 1 from CashLog where cashTime>c.cashTime)
Assuming you are using SQL2K5/8/R2 declare @startdatetime datetime, @enddatetime datetime;select @startdatetime='2010-01-01', @enddatetime='2010-05-31';with spendin_pattern(memberid, pay, cashtime, rownumber) as ( select b.memberId, b.pay, b.cashTime, ROW_NUMBER() over (partition by b.[memberId] order by b.cashtime desc) rownumber from member a inner join cashlog b on a.memberid=b.memberid where b.cashTime between @startdatetime and @enddatetime ) select * from spendin_pattern where rownumber=1 order by cashtime desc;
If you are running SQL2K, then you need to use subquery instead of CTE
会员表:Member
消费记录表:CashLog
消费记录中自然保持着多条单个会员信息,需要查询最近消费排序的条件消费时间Time属于CashLog表。
FROM 会员表 a
JOIN 消费记录表 b
ON a.会员ID = b.会员ID
GROUP BY a.会员名
ORDER BY 消费时间 DESC
from 会员表 a
left join 消费记录表 b on a.会员ID=b.会员ID
left join (select 会员ID,max(消费时间)消费时间 from 消费记录表 group by 会员ID) c
on a.会员ID=c.会员ID
order by
case when b.会员ID is null then 2 else 1 end,
c.消费时间 desc,
b.消费时间 desc
最搞人的还是无法实现,总之谢谢大家了。
CREATE TABLE [CashLog]
(
[cashId] INT IDENTITY(1,1) PRIMARY KEY,
[memberId] INT FOREIGN KEY REFERENCES [Member]([memberId]),--会员ID
[pay] MONEY NOT NULL,--收费
[cashTime] DATETIME NOT NULL,--时间
)
GO会员表:
CREATE TABLE [Member]
(
[memberId] INT IDENTITY(1,1) PRIMARY KEY,
[cardId] VARCHAR(20) NOT NULL,--卡号
[[memName] VARCHAR(20) NOT NULL,--姓名
[memMoney] MONEY NOT NULL,--金额
[createTime] DATETIME NOT NULL,--办卡时间
)如此两个表,查询最近消费其实也就是将在消费记录中存在的会员卡号,并以卡号根据消费的时间进行倒序排列。查询出的数据卡号不能重复。
and not exists( select 1 from CashLog where cashTime>c.cashTime)
declare @startdatetime datetime, @enddatetime datetime;select @startdatetime='2010-01-01', @enddatetime='2010-05-31';with spendin_pattern(memberid, pay, cashtime, rownumber) as
(
select
b.memberId, b.pay, b.cashTime, ROW_NUMBER() over (partition by b.[memberId] order by b.cashtime desc) rownumber
from
member a inner join cashlog b on
a.memberid=b.memberid
where
b.cashTime between @startdatetime and @enddatetime
)
select
*
from
spendin_pattern
where
rownumber=1
order by
cashtime desc;
If you are running SQL2K, then you need to use subquery instead of CTE