create table transInfo --交易信息表
(
transDate datetime ,--交易时间
cardID char(19) , --交易卡号
transType char(4) , --交易类型
transMoney money --交易金额
)要求查询“累计交易金额最高的卡号”,不要用排序,临时表。用子查询如何实现?
感谢。
(
transDate datetime ,--交易时间
cardID char(19) , --交易卡号
transType char(4) , --交易类型
transMoney money --交易金额
)要求查询“累计交易金额最高的卡号”,不要用排序,临时表。用子查询如何实现?
感谢。
解决方案 »
- 防止sql注入的方法有哪些??
- 2008下的错误,请帮忙看一下:
- 怎样对ntext数据类型distinct?
- smalldatetime和datetime的问题~~~有点傻
- SQL SEVER 2000 备份还原问题
- SQL导出Excel表问题,高手快来帮忙!急!分不够再加,保证加!!
- ~~~~~3个不同地方的数据库设置了复写,经常隔几个月会有记录莫名的丢失,请高手给点宝贵意见
- 关于#临时表的使用问题
- 不使用'主键',怎样设置一个值不重复的字段?
- 由于数据移动,未能继续以 NOLOCK 方式扫描?? 怎么解决??
- 请教怎样通过一个SQL语句查询出每个学校每个班级男女各多少人
- 一个很简单的SQL
*
from
(
select
cardid,sum(transMoney) as transMoney
from
tb
group by
cardid
)t
where
transMoney=(select max(transMoney) from (
select
cardid,sum(transMoney) as transMoney
from
tb
group by
cardid
)t
)
select cardid
from (select cardid,sum(transmoney) transmoney from tb group by cardid) a
where transmonye = (select max(transmoney) from (select cardid,sum(transmoney) transmoney from tb group by cardid) b)
SELECT SUM(transMoney) AS total,cardID FROM transInfo
GROUP BY cardID)a
WHERE a.total=(select MAX(total) FROM (SELECT SUM(transMoney) AS total,cardID FROM transInfo
GROUP BY cardID)
(
transDate datetime ,--交易时间
cardID char(19) , --交易卡号
transType char(4) , --交易类型
transMoney money --交易金额
)
INSERT #transInfo
SELECT GETDATE(), '001', 'Test', 10.0 UNION ALL
SELECT GETDATE(), '001', 'Test', 11.0 UNION ALL
SELECT GETDATE(), '002', 'Test', 80.0 UNION ALL
SELECT GETDATE(), '003', 'Test', 7.0
go
--SQL:
SELECT TOP(1) WITH TIES * FROM
(SELECT DISTINCT cardID FROM #transInfo) a
CROSS APPLY
(SELECT totalMoney = SUM(transMoney) FROM #transInfo WHERE cardID = a.cardID) b
ORDER BY totalMoney DESC
/*
cardID totalMoney
------------------- ---------------------
002 80.00
*/
(
transDate datetime ,--交易时间
cardID char(19) , --交易卡号
transType char(4) , --交易类型
transMoney money --交易金额
);with Sum_Money
AS
(
Select cardID,sum(transMoney) as Money
from #transInfo
group by cardID
)
Select a.cardID
From Sum_Money a
Where not exists(Select * from Sum_Money b where a.Money<b.Money)
create table #transInfo --交易信息表
(
transDate datetime ,--交易时间
cardID char(19) , --交易卡号
transType char(4) , --交易类型
transMoney money --交易金额
)
INSERT #transInfo
SELECT GETDATE(), '001', 'Test', 10.0 UNION ALL
SELECT GETDATE(), '001', 'Test', 11.0 UNION ALL
SELECT GETDATE(), '002', 'Test', 80.0 UNION ALL
SELECT GETDATE(), '003', 'Test', 7.0
go--SQL:
select cardid
from #transinfo
group by cardid
having sum(transMoney) =
(select max(transmoney) from
(select cardid,sum(transMoney) as transMoney from #transinfo group by cardid) a)
select top 1 cardid 交易卡号,
sum(transmoney) over (partition by cardid) 累计交易金额
from tb
order by sum(transmoney) over (partition by cardid) desc