22,206
社区成员
发帖
与我相关
我的任务
分享
create table tb(id int,name nvarchar(10),[count] int)
insert into tb select 1,'华硕',1
insert into tb select 2,'华硕',1
insert into tb select 3,'华硕',1
insert into tb select 4,'技嘉',1
insert into tb select 5,'微星',1
go
select id=identity(int,1,1),name,count(*) as cnt
into #temp
from tb
group by name
select * from #temp
drop table #temp
drop table tb
/************
id name cnt
----------- ---------- -----------
1 华硕 3
2 技嘉 1
3 微星 1
(3 行受影响)
select max(id),name,count(*)count
from tb group by name
create table tb(id int,name nvarchar(10),[count] int)
insert into tb select 1,'华硕',1
insert into tb select 2,'华硕',1
insert into tb select 3,'华硕',1
insert into tb select 4,'技嘉',1
insert into tb select 5,'微星',1
go
select a.id,a.name,b.count from tb a inner join(
select name,count(*) count from tb group by name
)b on a.name=b.name
/*
id name count
----------- ---------- -----------
1 华硕 3
2 华硕 3
3 华硕 3
4 技嘉 1
5 微星 1
(5 行受影响)
*/
select a.id,a.name,(select count(*) from tb where name = a.name)ct from tb a
/*id name ct
----------- ---------- -----------
1 华硕 3
2 华硕 3
3 华硕 3
4 技嘉 1
5 微星 1
(5 行受影响)
*/
go
drop table tb
select id=identity(int,1,1),name,count(*) as cnt
into #temp
from tb
group by name
select * from #temp
drop table #temp
select id=row_number() over (order by getdate()),name,count(*) as cnt
from tb
group by name
select id,name,count(*)count
from tb group by name
select a.id,a.name,(select count(*) from tb where name = a.name) cnt
from tb a
select a.id,a.name,(select count(*) from tb b where b.name = a.name)count
from tb a
select id,name,[count]=(select count(1) from tb where name=a.name)
from tb a
select a.id,a.name,(select count(*) from tb where name = a.name) from tb a
select a.id,a.name,b.count from tb a inner join(
select name,count(*) count from tb group by name
)b on a.name=b.name
update t
set t.count = (select count(*) from tb where name = t.name)
from tb t