Microsoft SQL Server 2000数据库有一个名为XFJ的表,部分数据如下
卡号 终端号 消费金额 余额 消费时间 消费类型
"12259 21 100 100 2009-11-28 11:14:51.000 消费"
"12259 21 -100 200 2009-11-28 11:14:56.000 充值"
"12259 22 100 100 2009-11-28 11:15:00.000 消费"
"12259 22 -100 200 2009-11-28 11:15:04.000 充值"
"12261 22 400 10 2009-11-28 21:17:57.000 消费"
"12222 22 400 0 2009-11-28 21:22:57.000 消费"因为卡号和消费记录数据较多,现在想查询每张卡的余额(也就是表记录中最后消费时间的余额数取出来),余额为0的不显示。以上数据应返回如下结果
"12259 22 -100 200 2009-11-28 11:15:04.000 充值"
"12261 22 400 10 2009-11-28 21:17:57.000 消费"
麻烦高手帮忙写一个查询语句,谢谢!!
卡号 终端号 消费金额 余额 消费时间 消费类型
"12259 21 100 100 2009-11-28 11:14:51.000 消费"
"12259 21 -100 200 2009-11-28 11:14:56.000 充值"
"12259 22 100 100 2009-11-28 11:15:00.000 消费"
"12259 22 -100 200 2009-11-28 11:15:04.000 充值"
"12261 22 400 10 2009-11-28 21:17:57.000 消费"
"12222 22 400 0 2009-11-28 21:22:57.000 消费"因为卡号和消费记录数据较多,现在想查询每张卡的余额(也就是表记录中最后消费时间的余额数取出来),余额为0的不显示。以上数据应返回如下结果
"12259 22 -100 200 2009-11-28 11:15:04.000 充值"
"12261 22 400 10 2009-11-28 21:17:57.000 消费"
麻烦高手帮忙写一个查询语句,谢谢!!
CREATE TABLE tb(
card varchar(40),TemicalNumber int,Fval float,FRval float,FTime datetime,FType varchar(40))
-->查询
SELECT * FROM tb a,
(SELECT card,[Ftime]=MAX(Ftime) FROM tb GROUP BY card) b
WHERE a.card=b.card and a.Ftime=b.Ftime
-->在网吧做的,再改一下
SELECT a.* FROM tb a,
(SELECT card,[Ftime]=MAX(Ftime) FROM tb GROUP BY card) b
WHERE a.card=b.card and a.Ftime=b.Ftime
--> 测试数据:[TB]
if object_id('[TB]') is not null drop table [TB]
create table [TB]([卡号] int,[终端号] int,[消费金额] int,[余额] int,[消费时间] datetime,[消费类型] varchar(4))
insert [TB]
select 12259,21,100,100,'2009-11-28 11:14:51.000','消费' union all
select 12259,21,-100,200,'2009-11-28 11:14:56.000','充值' union all
select 12259,22,100,100,'2009-11-28 11:15:00.000','消费' union all
select 12259,22,-100,200,'2009-11-28 11:15:04.000','充值' union all
select 12261,22,400,10,'2009-11-28 21:17:57.000','消费' union all
select 12222,22,400,0,'2009-11-28 21:22:57.000','消费'select * from [TB] t where not exists(select 1 from TB where [卡号]=t.[卡号] and [消费时间]>t.[消费时间])
/*
卡号 终端号 消费金额 余额 消费时间 消费类型
----------- ----------- ----------- ----------- ----------------------- ----
12259 22 -100 200 2009-11-28 11:15:04.000 充值
12261 22 400 10 2009-11-28 21:17:57.000 消费
12222 22 400 0 2009-11-28 21:22:57.000 消费(3 行受影响)*/
drop table TB
where not exits
(select 1 from tb where t.card=card and t.time<time)
insert tb select 12259,21,-100,200,'2009-11-28 11:14:56.000','充值'
insert tb select 12259,22,100,100,'2009-11-28 11:15:00.000','消费'
insert tb select 12259,22,-100,200,'2009-11-28 11:15:04.000','充值'
insert tb select 12261,22,400,10,'2009-11-28 21:17:57.000','消费'
insert tb select 12222,22,400,0,'2009-11-28 21:22:57.000','消费'select * from tb a
where 余额>0 and not exists(select 1 from tb where a.消费时间>消费时间 and a.卡号=卡号 )
卡号 终端号 消费金额 余额 消费时间 消费类型
----------- ----------- ----------- ----------- ----------------------- ----------
12259 21 100 100 2009-11-28 11:14:51.000 消费
12261 22 400 10 2009-11-28 21:17:57.000 消费(2 行受影响)
*
from
[TB] t
where
消费时间=(select max(消费时间) from TB where [卡号]=t.[卡号])
颠倒了select * from tb a
where 余额>0 and not exists(select 1 from tb where a.消费时间<消费时间 and a.卡号=卡号 ) 卡号 终端号 消费金额 余额 消费时间 消费类型
----------- ----------- ----------- ----------- ----------------------- ----------
12259 22 -100 200 2009-11-28 11:15:04.000 充值
12261 22 400 10 2009-11-28 21:17:57.000 消费(2 行受影响)
create table [TB]([卡号] int,[终端号] int,[消费金额] int,[余额] int,[消费时间] datetime,[消费类型] varchar(4))
insert [TB]
select 12259,21,100,100,'2009-11-28 11:14:51.000','消费' union all
select 12259,21,-100,200,'2009-11-28 11:14:56.000','充值' union all
select 12259,22,100,100,'2009-11-28 11:15:00.000','消费' union all
select 12259,22,-100,200,'2009-11-28 11:15:04.000','充值' union all
select 12261,22,400,10,'2009-11-28 21:17:57.000','消费' union all
select 12222,22,400,0,'2009-11-28 21:22:57.000','消费'1)QuerySELECT * FROM
(
SELECT * ,RANK = RANK () OVER(PARTITION BY 卡号 ORDER BY 消费时间 DESC)FROM TB
WHERE 余额 > 0
)B
WHERE B.RANK = 1 2)Result卡号 终端号 消费金额 余额 消费时间 消费类型 RANK
----------- ----------- ----------- ----------- ----------------------- ---- --------------------
12259 22 -100 200 2009-11-28 11:15:04.000 充值 1
12261 22 400 10 2009-11-28 21:17:57.000 消费 1(2 row(s) affected)
where 消费时间=(select max(消费时间) from tb where 卡号=t.卡号 ) and 余额>0
12259 22 -100 200 2009-11-28 11:15:04.000 充值
12261 22 400 10 2009-11-28 21:17:57.000 消费