select 字段,count(*) from table group by 字段这样吗。没理解你的意思
declare @table table (col varchar(1)) insert into @table select 'a' union all select 'b' union all select 'b' union all select 'c' union all select 'a' union all select 'b' union all select 'c' union all select 'a' union all select 'a' union all select 'a' union all select 'a'select col,count(*) as [count] from @table group by col /* col count ---- ----------- a 6 b 3 c 2 */
楼主的a,b,c的表述,我开始还误以为是这样的呢 declare @table table (col varchar(6)) insert into @table select 'aaa' union all select 'bb' union all select 'b' union all select 'cc' union all select 'a' union all select 'bbb' union all select 'ccccc' union all select 'aa' union all select 'aaa' union all select 'aa' union all select 'a'select right(col,1) as 列名,sum(len(col)) as 个数 from @table group by right(col,1) /* 列名 个数 ---- ----------- a 12 b 6 c 7 */
name列中数据可能是小王的叔叔,小王的父亲,我家的王,隔壁家的小李,大叔家的小东,村西头的小东。
declare @table table (name varchar(6)) insert into @table select '小王' union all select '小冬' union all select '小李' union all select '小王' union all select '小王' union all select '小王' union all select '小李' union all select '小李' union all select '小李' union all select '小李' union all select '小李'select name,count(name) as 记录数 from @table group by name /* name 记录数 ------ ----------- 小冬 1 小李 6 小王 4 */
select * into # from ( select 'a' as name union all select 'c' union all select 'b' ) Adeclare @table table (col varchar(100)) insert into @table select 'a,b,c' union all select 'b,c' union all select 'a,b' union all select 'c' select name ,cnt =(select count(1) from @table where col like '%'+A.name +'%') from # A==================name cnt ---- ----------- a 2 c 3 b 3(3 行受影响)
declare @table table (name nvarchar(6)) insert into @table select '我家的小王' union all select '小冬的爸爸' union all select '小李的叔叔' union all select '小王的叔叔' union all select '小王' union all select '小王的妈妈' union all select '小李的狗' union all select '小李的书包' union all select '小李的汗水' union all select '小李的泪水' union all select '小李的新年'select '小王' as name,count(name) as 记录数 from @table where charindex('小王',name)>0 union all select '小冬',count(name) as 记录数 from @table where charindex('小冬',name)>0 union all select '小李',count(name) as 记录数 from @table where charindex('小李',name)>0 /* name 记录数 ------ ----------- 小冬 1 小李 6 小王 4 */
declare @name table (name varchar(10)) insert into @name select 'a' union select 'b' union select 'c' declare @table table (name varchar(100)) insert into @table select 'a,b,c' union all select 'b,c' union all select 'a,b' union all select 'c' select a.name,count(*) from @name a,@table b where CHARINDEX(a.name,b.name)>0 group by a.name
declare @table table (name nvarchar(6)) insert into @table select '我家的小王' union all select '小冬的爸爸' union all select '小李的叔叔' union all select '小王的叔叔' union all select '小王' union all select '小王的妈妈' union all select '小李的狗' union all select '小李的书包' union all select '小李的汗水' union all select '小李的泪水' union all select '小李的新年'select '小王' as name,count(name) as 记录数 from @table where charindex('小王',name)>0 union all select '小冬',count(name) as 记录数 from @table where charindex('小冬',name)>0 union all select '小李',count(name) as 记录数 from @table where charindex('小李',name)>0 union all select '总和',count(1) as 记录数 from @table ========name 记录数 ---- ----------- 小王 4 小冬 1 小李 6 总和 11(4 行受影响)
select count(*) from tb 不要group by 就是所有的了.
declare @table table (name nvarchar(16)) insert into @table select N'我家的小王' union all select N'小冬的爸爸' union all select N'小李的叔叔' union all select N'小王的叔叔' union all select N'小王' union all select N'小王的妈妈' union all select N'小李的狗' union all select N'小李的书包' union all select N'小李的汗水' union all select N'小李的泪水' union all select N'小李的新年' SELECT SUM(CASE WHEN charindex(N'小王',name)>0 THEN 1 ELSE 0 END)+ SUM(CASE WHEN charindex(N'小冬',name)>0 THEN 1 ELSE 0 END)+ SUM(CASE WHEN charindex(N'小李',name)>0 THEN 1 ELSE 0 END) FROM @table /* ----------- 11(1 個資料列受到影響)*/
group by 字段这样吗。没理解你的意思
declare @table table (col varchar(1))
insert into @table
select 'a' union all
select 'b' union all
select 'b' union all
select 'c' union all
select 'a' union all
select 'b' union all
select 'c' union all
select 'a' union all
select 'a' union all
select 'a' union all
select 'a'select col,count(*) as [count] from @table group by col
/*
col count
---- -----------
a 6
b 3
c 2
*/
declare @table table (col varchar(6))
insert into @table
select 'aaa' union all
select 'bb' union all
select 'b' union all
select 'cc' union all
select 'a' union all
select 'bbb' union all
select 'ccccc' union all
select 'aa' union all
select 'aaa' union all
select 'aa' union all
select 'a'select right(col,1) as 列名,sum(len(col)) as 个数
from @table group by right(col,1)
/*
列名 个数
---- -----------
a 12
b 6
c 7
*/
declare @table table (name varchar(6))
insert into @table
select '小王' union all
select '小冬' union all
select '小李' union all
select '小王' union all
select '小王' union all
select '小王' union all
select '小李' union all
select '小李' union all
select '小李' union all
select '小李' union all
select '小李'select name,count(name) as 记录数 from @table group by name
/*
name 记录数
------ -----------
小冬 1
小李 6
小王 4
*/
select 'a' as name union all
select 'c' union all
select 'b' ) Adeclare @table table (col varchar(100))
insert into @table
select 'a,b,c' union all
select 'b,c' union all
select 'a,b' union all
select 'c' select name ,cnt =(select count(1) from @table where col like '%'+A.name +'%') from # A==================name cnt
---- -----------
a 2
c 3
b 3(3 行受影响)
declare @table table (name nvarchar(6))
insert into @table
select '我家的小王' union all
select '小冬的爸爸' union all
select '小李的叔叔' union all
select '小王的叔叔' union all
select '小王' union all
select '小王的妈妈' union all
select '小李的狗' union all
select '小李的书包' union all
select '小李的汗水' union all
select '小李的泪水' union all
select '小李的新年'select '小王' as name,count(name) as 记录数 from @table where charindex('小王',name)>0 union all
select '小冬',count(name) as 记录数 from @table where charindex('小冬',name)>0 union all
select '小李',count(name) as 记录数 from @table where charindex('小李',name)>0
/*
name 记录数
------ -----------
小冬 1
小李 6
小王 4
*/
insert into @name
select 'a' union
select 'b' union
select 'c' declare @table table (name varchar(100))
insert into @table
select 'a,b,c' union all
select 'b,c' union all
select 'a,b' union all
select 'c'
select a.name,count(*) from @name a,@table b
where CHARINDEX(a.name,b.name)>0
group by a.name
insert into @table
select '我家的小王' union all
select '小冬的爸爸' union all
select '小李的叔叔' union all
select '小王的叔叔' union all
select '小王' union all
select '小王的妈妈' union all
select '小李的狗' union all
select '小李的书包' union all
select '小李的汗水' union all
select '小李的泪水' union all
select '小李的新年'select '小王' as name,count(name) as 记录数 from @table where charindex('小王',name)>0 union all
select '小冬',count(name) as 记录数 from @table where charindex('小冬',name)>0 union all
select '小李',count(name) as 记录数 from @table where charindex('小李',name)>0 union all
select '总和',count(1) as 记录数 from @table ========name 记录数
---- -----------
小王 4
小冬 1
小李 6
总和 11(4 行受影响)
不要group by 就是所有的了.
insert into @table
select N'我家的小王' union all
select N'小冬的爸爸' union all
select N'小李的叔叔' union all
select N'小王的叔叔' union all
select N'小王' union all
select N'小王的妈妈' union all
select N'小李的狗' union all
select N'小李的书包' union all
select N'小李的汗水' union all
select N'小李的泪水' union all
select N'小李的新年'
SELECT SUM(CASE WHEN charindex(N'小王',name)>0 THEN 1 ELSE 0 END)+
SUM(CASE WHEN charindex(N'小冬',name)>0 THEN 1 ELSE 0 END)+
SUM(CASE WHEN charindex(N'小李',name)>0 THEN 1 ELSE 0 END)
FROM @table
/*
-----------
11(1 個資料列受到影響)*/