查询的疑问

[summer16md]

表里面有三个字段
id name count

要求是 当name的名字相同的话 查询出来count里面就增加1

比如
id name count
1 华硕 1
2 华硕 1
3 华硕 1
4 技嘉 1
5 微星 1

查出来 让华硕的count显示成3 其他不变 请问怎么查？

[AcHerat]

update t
set t.count = (select count(*) from tb where name = t.name)
from tb t

[-晴天]

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

[-晴天]

这样也行,但效率差点:

select a.id,a.name,(select count(*) from tb where name = a.name) from tb a

[快溜]

select id,name,[count]=(select count(1) from tb where name=a.name)
from tb a

[cd731107]

select a.id,a.name,(select count(*) from tb b where b.name = a.name)count 
from tb a

[summer16md]

[Quote=引用 1 楼 acherat 的回复:]

SQL code

update t
set t.count = (select count(*) from tb where name = t.name)
from tb t
[/Quote]

没有显示结果呀

[AcHerat]

1楼是更新了count，查看表就知道了。
这是查询。

select a.id,a.name,(select count(*) from tb where name = a.name) cnt
from tb a

[summer16md]

查出来还是有点问题
他查出来就变成了

id name count
1 华硕 3
2 华硕 3
3 华硕 3
4 技嘉 1
5 微星 1

我想让他显示成
1 华硕 3
2 技嘉 1
3 微星 1

[cd731107]

select id,name,count(*)count
from tb group by name

[cd731107]

这种问题，楼主应该知道的吧

[summer16md]

不知道呀 没遇到过

[AcHerat]

被楼主忽悠了。。。

select id=row_number() over (order by getdate()),name,count(*) as cnt
from tb 
group by name

[summer16md]

[Quote=引用 12 楼 acherat 的回复:]

被楼主忽悠了。。。

SQL code

select id=row_number() over (order by getdate()),name,count(*) as cnt
from tb
group by name
[/Quote]

'row_number' 不是可以识别的 函数名。

[AcHerat]

select id=identity(int,1,1),name,count(*) as cnt
  into #temp
from tb 
group by name

select * from #temp

drop table #temp

[-晴天]

是要这样的结果吗?

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

[cd731107]

select max(id),name,count(*)count
from tb group by name

[summer16md]

[Quote=引用 15 楼 qianjin036a 的回复:]

是要这样的结果吗?
SQL code
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
……
[/Quote]

不是 是要
1 华硕 3
2 技嘉 1
3 微星 1
这样的结果

[AcHerat]

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 行受影响)

[summer16md]

[Quote=引用 18 楼 acherat 的回复:]

SQL code

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 int……
[/Quote]

3Q 等下给你分啊 我出去吃下饭

[ly745455]

MARK