regInfo 注册表
MemberId regDate
10001 2010-5-1
10002 2010-5-2
10003 2010-5-3
10004 2010-5-4
10005 2010-5-5
UserRecord 充值记录表
MemberId money dates
10001 5.00 2010-5-5
10002 10.00 2010-5-5
10001 15.00 2010-5-6
10003 5.00 2010-5-6
10001 10.00 2010-5-7
10004 20.00 2010-5-7
查询当月 注册的会员充值10元以上的记录。只保留第一条充值金额大于10的记录 要求显示结果
10001 15.00 2010-5-6
10002 10.00 2010-5-5
10004 20.00 2010-5-7
这个怎么写啊
MemberId regDate
10001 2010-5-1
10002 2010-5-2
10003 2010-5-3
10004 2010-5-4
10005 2010-5-5
UserRecord 充值记录表
MemberId money dates
10001 5.00 2010-5-5
10002 10.00 2010-5-5
10001 15.00 2010-5-6
10003 5.00 2010-5-6
10001 10.00 2010-5-7
10004 20.00 2010-5-7
查询当月 注册的会员充值10元以上的记录。只保留第一条充值金额大于10的记录 要求显示结果
10001 15.00 2010-5-6
10002 10.00 2010-5-5
10004 20.00 2010-5-7
这个怎么写啊
select b.MemberId,max(A.money),min(dates) as FirstDay as MonthMoney from UserRecord a inner join regInfo b on a.MemberId=b.MemberIdwhere month(a.dates)=month(getdate()) group by a.MemberId
having max(A.money)>10
insert into regInfo values('10001', '2010-5-1')
insert into regInfo values('10002', '2010-5-2')
insert into regInfo values('10003', '2010-5-3')
insert into regInfo values('10004', '2010-5-4')
insert into regInfo values('10005', '2010-5-5')
create table UserRecord(MemberId varchar(10),money decimal(18,2),dates datetime)
insert into UserRecord values('10001', 5.00 ,'2010-5-5')
insert into UserRecord values('10002', 10.00 ,'2010-5-5')
insert into UserRecord values('10001', 15.00 ,'2010-5-6')
insert into UserRecord values('10003', 5.00 ,'2010-5-6')
insert into UserRecord values('10001', 10.00 ,'2010-5-7')
insert into UserRecord values('10004', 20.00 ,'2010-5-7')
goselect t.* from UserRecord t , regInfo n where t.MemberId = n.MemberId and datediff(mm,n.regDate,getdate()) = 0 and t.money >= 10 and t.money = (select top 1 money from UserRecord where money >= 10 and MemberId = t.MemberId)drop table regInfo,UserRecord/*
MemberId money dates
---------- -------------------- ------------------------------------------------------
10001 15.00 2010-05-06 00:00:00.000
10002 10.00 2010-05-05 00:00:00.000
10004 20.00 2010-05-07 00:00:00.000(所影响的行数为 3 行)
*/
where money > 10 and datediff(m,dates,getdate())=0
and not exists(select 1 from UserRecord where MemberId =t.MemberId and money <t.money )
group by MemberId
试试
insert into regInfo values('10001', '2010-5-1')
insert into regInfo values('10002', '2010-5-2')
insert into regInfo values('10003', '2010-5-3')
insert into regInfo values('10004', '2010-5-4')
insert into regInfo values('10005', '2010-5-5')
create table UserRecord(MemberId varchar(10),money decimal(18,2),dates datetime)
insert into UserRecord values('10001', 5.00 ,'2010-5-5')
insert into UserRecord values('10002', 10.00 ,'2010-5-5')
insert into UserRecord values('10001', 15.00 ,'2010-5-6')
insert into UserRecord values('10003', 5.00 ,'2010-5-6')
insert into UserRecord values('10001', 10.00 ,'2010-5-7')
insert into UserRecord values('10004', 20.00 ,'2010-5-7')
goSELECT b.MemberId, MAX(A.money) AS FirstDay, MIN(dates) AS MonthMoney
FROM UserRecord a
INNER JOIN regInfo b ON a.MemberId=b.MemberId
WHERE MONTH(a.dates)=MONTH(GETDATE())
GROUP BY b.MemberId
HAVING MAX(A.money)>=10/*
MemberId FirstDay MonthMoney
---------- -------------------- ------------------------------------------------------
10001 15.00 2010-05-05 00:00:00.000
10002 10.00 2010-05-05 00:00:00.000
10004 20.00 2010-05-07 00:00:00.000(所影响的行数为 3 行)
*/
if object_id('[regInfo]') is not null drop table [regInfo]
go
create table [regInfo] (MemberId int,regDate datetime)
insert into [regInfo]
select 10001,'2010-5-1' union all
select 10002,'2010-5-2' union all
select 10003,'2010-5-3' union all
select 10004,'2010-5-4' union all
select 10005,'2010-5-5'--> 测试数据: [UserRecord]
if object_id('[UserRecord]') is not null drop table [UserRecord]
go
create table [UserRecord] (MemberId int,[money] numeric(4,2),dates datetime)
insert into [UserRecord]
select 10001,5.00,'2010-5-5' union all
select 10002,10.00,'2010-5-5' union all
select 10001,15.00,'2010-5-6' union all
select 10003,5.00,'2010-5-6' union all
select 10001,10.00,'2010-5-7' union all
select 10004,20.00,'2010-5-7'--select * from [regInfo]
--select * from [UserRecord]select b.*,a.[money],a.dates into #t from UserRecord a ,regInfo b where a.MemberId =b.MemberId and datediff(mm,regdate,getdate())=0select * from #t t where (select count(*) from #t where MemberId=t.MemberId and [money]>t.[money] and [money]>=10)<1 and [money]>=10
MemberId regDate money dates
----------- ----------------------- --------------------------------------- -----------------------
10002 2010-05-02 00:00:00.000 10.00 2010-05-05 00:00:00.000
10001 2010-05-01 00:00:00.000 15.00 2010-05-06 00:00:00.000
10004 2010-05-04 00:00:00.000 20.00 2010-05-07 00:00:00.000(3 行受影响)
drop table #t
select t.* from UserRecord t
where money >= 10 and datediff(m,dates,getdate())=0
and dates = (select top 1 dates from UserRecord
where MemberId =t.MemberId and money >= 10
order by dates)
好像推荐的帖都会被转向到一个叫163jsp的网址?
WITH T as
(select *,ROW_NUMBER() OVER (ORDER BY memberid) AS rn from tb
where datepart(m,dates)=datepart(m,getdate()) and datepart(y,dates)=datepart(y,getdate()) and money>10 ORDER BY memberid)
SELECT memberid,money,dates FROM T WHERE NOT EXISTS(SELECT 1 FROM t b WHERE memberid=T.memberid AND rn<T.rn)