交易信息表transInfo 如下(transDate交易时间,cardID卡号,transType交易类型,transMoney交易金额)
transDate cardID transType transMoney
------------------------------------------------------ -------------------- ---------- -----------
2007-11-23 02:24:59.013 1010 3576 1234 5678 支取 900
2007-11-23 02:24:59.013 1010 3576 1212 1134 存入 5000
2007-11-23 02:38:34.903 1010 3576 1212 1134 存入 2000
求:查询交易金额最高的卡号
提示:用子查询跟distinct去掉重复的卡号
transDate cardID transType transMoney
------------------------------------------------------ -------------------- ---------- -----------
2007-11-23 02:24:59.013 1010 3576 1234 5678 支取 900
2007-11-23 02:24:59.013 1010 3576 1212 1134 存入 5000
2007-11-23 02:38:34.903 1010 3576 1212 1134 存入 2000
求:查询交易金额最高的卡号
提示:用子查询跟distinct去掉重复的卡号
select * from transInfo where transMoney =(select max(transMoney ) from transInfo )
select A.cardID, A.Total
from (select cardID, sum(transMoney) as Total from transInfo group by cardID) as A
where A.Total = ( select max(B.Total) from
(select cardID, sum(transMoney) as Total from transInfo group by cardID) as B
)
4楼的答案把transMoney汇总后输入,这样写任何的cardID都显示出来.没能比较哪个最高金额.
效果跟下面的语句一样:select cardID sum(transMoney) as new_transMoney from transInfo group by cardID
3楼的可以返回多条记录。select TOP 1 cardID, sum(transMoney) as new_transMoney
from transInfo
group by cardID
order by sum(transMoney) desc
declare @t table(cardID int,age int,flag nchar(10))
insert into @t select 1,500,'存'
insert into @t select 1,22,'取'
insert into @t select 1,33,'取'
insert into @t select 2,44,'取'
--select ID,(case flag when '存' then age else -age end) as miney from @t
select top 1 cardID,sum(case when flag='存' then age else -age end) as mon from @t group by cardID order by mon desc
很少的情况下,多个卡号可能同时具有最高交易金额。用TOP 1的问题就是遇到这种情况时,获得的结果不完整。
declare @transInfo table(transDate datetime, cardID nvarchar(20), transType nvarchar(5), transMoney int)
insert into @transInfo
select '2007-11-23 02:24:59.013', '1010 3576 1234 5678', '支取', 900
union all select '2007-11-23 02:24:59.013', '1010 3576 1212 1134', '存入', 5000
union all select '2007-11-23 02:38:34.903', '1010 3576 1212 1134', '存入', 2000
select a.cardID from (select cardID, sum(transMoney) as transMoney from @transInfo group by cardID) a
where a.transMoney=(select max(b.transMoney) from (select cardID, sum(transMoney) as transMoney from @transInfo group by cardID) b)
--------------------------------------------------------------------------
(3 row(s) affected)
cardID
--------------------
1010 3576 1212 1134(1 row(s) affected)
改了下
select a.cardID from (select cardID, sum(transMoney) as transMoney from @transInfo group by cardID) a,(select max(c.transMoney) as topal from (select cardID, sum(transMoney) as transMoney from @transInfo group by cardID) c) b
where a.transMoney=b.topal