表1:id name
1 yang
2 ying
3 zhilian
4 haha
5 ying
6 zhilian
7 zhilian要求查询结果:name count
yang 1
ying 2
zhilian 3
haha 1要实现这样的结果 请问sql语句该怎么实现
1 yang
2 ying
3 zhilian
4 haha
5 ying
6 zhilian
7 zhilian要求查询结果:name count
yang 1
ying 2
zhilian 3
haha 1要实现这样的结果 请问sql语句该怎么实现
select name ,count(*)
from tb
group by name
from tb
group by name
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-10-30 09:59:25
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[name] varchar(7))
insert [tb]
select 1,'yang' union all
select 2,'ying' union all
select 3,'zhilian' union all
select 4,'haha' union all
select 5,'ying' union all
select 6,'zhilian' union all
select 7,'zhilian'
--------------开始查询--------------------------
select name,count(1) as [count] from tb group by name
----------------结果----------------------------
/* name count
------- -----------
haha 1
yang 1
ying 2
zhilian 3(4 行受影响)*/
select name ,min(id) from 表1 group by name
--> Author : wufeng4552
--> Date : 2009-10-30 09:59:09
declare @TB table (id int,name nvarchar(14))
insert into @TB
select 1,'yang' union all
select 2,'ying' union all
select 3,'zhilian' union all
select 4,'haha' union all
select 5,'ying' union all
select 6,'zhilian' union all
select 7,'zhilian'select name,count(1) from @tb group by name
/*
name
-------------- -----------
haha 1
yang 1
ying 2
zhilian 3(4 個資料列受到影響)
*/
drop table tb
go
create table tb(id int , name varchar(50))
insert into tb
select 1 , 'yang' union all
select 2 , 'ying' union all
select 3 , 'zhilian' union all
select 4 ,'haha' union all
select 5 , 'ying' union all
select 6 , 'zhilian' union all
select 7 , 'zhilian' select count(*) as count,name from tb group by name结果是:
1 haha
1 yang
2 ying
3 zhilian
create table [tb]([id] int,[name] varchar(7))
insert [tb]
select 1,'yang' union all
select 2,'ying' union all
select 3,'zhilian' union all
select 4,'haha' union all
select 5,'ying' union all
select 6,'zhilian' union all
select 7,'zhilian'select distinct [name], cnt=(select count(name) from [tb] where name = A.name ) from [tb] A
==================
name cnt
------- -----------
haha 1
yang 1
ying 2
zhilian 3(4 行受影响)