34,571
社区成员
发帖
与我相关
我的任务
分享
1).统计A表中每个月注册用户数
select convert(varchar(10),regdate,120) 年月 , count(*) 注册用户数 from A group by convert(varchar(10),regdate,120)
2).统计A表中有姓名相同的用户数
select count(*) 姓名相同的用户数 from
(
select name from a group by name having count(*) > 1
) t
3).如果表A中有姓名相同的用户,把相同的查出,写入表C中
insert into c(name) select name from a group by name having count(*) > 1
4).A中ID有多个相同的数据,A中姓名相同的ID只保留注册时间最大的数据
delete a from a t where regdate not in (select max(regdate) from a where name = t.name)
--1)
select convert(varchar(7),regdate,120)[日期],count(1)[数量] from A group by convert(varchar(7),regdate,120)
--)2
select name,count(1)[数量] from A group by name
--3)
如果c表的id为自增的话:
insert into c select name from a group by name having count(1)>1
--4)
select * from A t where not exists(select 1 from A where name=a.name and regdate>a.regdate)
4
select *
from tb a
where not exists(select 1 from tb
where id=a.id and name=a.name and regdate>a.regdate)
1
select convert(char(7),regdate,120) regdate,count(*) cnt
from tb
group by convert(char(7),regdate,120)
2
select name,count(*) cnt
from tb
group by name
3
insert into c
select name
from tb a
where exists(select 1 from tb where a.name=name and a.id>id)