select id,count(id) as cn from tb group by id order by count(id) desc
select count(1) as 条数,* from tb order by 次数 desc
select 条数=(select count(1) from tb),* from tb order by 次数 desc
select id,count(1) as [count] from tb group by id order by 2
select b.* from tb b left join (select id,count(id) as cn from tb group by id)c on b.id = c.id order by c.id desc
select b.* from tb b left join (select id,count(id) as rn from tb group by id)c on b.id = c.id order by c.rn desc--上边打错!
;with cte as ( select *,rn=ROW_NUMBER() over(partition by id order by [time] desc) from tb -- id是用户的id ,time是消费的时间 ) select * from cte order by id,rn desc
select id,count(id) as cn
from tb
group by id
order by count(id) desc
select count(1) as 条数,* from tb order by 次数 desc
select 条数=(select count(1) from tb),* from tb order by 次数 desc
select id,count(1) as [count] from tb group by id
order by 2
select b.*
from tb b left join (select id,count(id) as cn from tb group by id)c
on b.id = c.id
order by c.id desc
select b.*
from tb b left join (select id,count(id) as rn from tb group by id)c
on b.id = c.id
order by c.rn desc--上边打错!
;with cte as
(
select *,rn=ROW_NUMBER() over(partition by id order by [time] desc) from tb -- id是用户的id ,time是消费的时间
)
select * from cte order by id,rn desc
列 姓名,性别,身份证号码,消费日期,消费金额
数据 张三,男,230101 2010.10.2 560
李四,女,230102 2010.9.30 1250.5
张三,男,230101 2010.12.5 3600
想要显示的是
姓名,性别,身份证号码,总计消费,次数
张三 男 230101 4160 2
李四 女 230102 1250.5 1
/*
表名 hu_ykks
列 姓名,性别,身份证号码,消费日期,消费金额
数据 张三,男,230101 2010.10.2 560
李四,女,230102 2010.9.30 1250.5
张三,男,230101 2010.12.5 3600
想要显示的是
姓名,性别,身份证号码,总计消费,次数
张三 男 230101 4160 2
李四 女 230102 1250.5 1
*/
select 姓名,性别,身份证号码,sum(消费金额)总计消费,count(*)次数
from hu_ykks
group by 姓名,性别,身份证号码