有一张表名jiancha,有id,jc列,id是主键(自动增长的),jc的内容是AA,AB,AA,AB,AC
想要统计AA,AB,AC出现的次数,用以下语句统计不了,请教大虾有什么好方法可以统计出来?多谢!select count(*) as number from table where jc='AA'
想要统计AA,AB,AC出现的次数,用以下语句统计不了,请教大虾有什么好方法可以统计出来?多谢!select count(*) as number from table where jc='AA'
from jiancha
group by jc
;
from table
group by jc
不会的话
如果会出现多个,就要写函数了select sum(case
when charindex('AA,AB,AC ',jc)>0 then 1 else 0
end) from table
select jc,count(*) from [table] group by jc
DROP TABLE #t
GOCREATE TABLE #t(id INT identity(1,1),jc NVARCHAR(10))
INSERT INTO #t(jc)
SELECT 'AA' UNION ALL
SELECT 'AA' UNION ALL
SELECT 'AA' UNION ALL
SELECT 'BB' UNION ALL
SELECT 'CC' UNION ALL
SELECT 'BB' UNION ALL
SELECT 'CC' SELECT jc,COUNT(jc)AS NUM FROM #t GROUP BY jc
jc NUM
---------- -----------
AA 3
BB 2
CC 2(3 row(s) affected)
GO
create table test(id int identity,jc varchar(2));
insert into test(jc) values('AA');
insert into test(jc) values('AB');
insert into test(jc) values('AC');
insert into test(jc) values('AA');
insert into test(jc) values('AB');
insert into test(jc) values('AB');
insert into test(jc) values('AC');
insert into test(jc) values('AA');
select jc,count(*) count from test
group by jc
结果
jc count
AA 3
AB 3
AC 2
SELECT COUNT(jc)AS NUM FROM #t WHERE jc Like '%AA%' ?????
--描述 查找expression1 在expression2 中的出现次数
--返回值 出现次数
use pubs
if exists(select * from sysobjects where name = 'charnum' and xtype = 'fn')
drop function charnum
go
CREATE FUNCTION charnum
(@desstr varchar(8000),
@srcstr varchar(8000)
)
RETURNS int
AS
BEGIN
declare @n int ,@i int ,@p int,@deslen int
select @n = 0,@i=0,@p=0,
@deslen = (select len(@desstr))
while 1=1
begin select @p = charindex(@desstr,@srcstr,@i) if @p>0
begin
select @n=@n+1
select @i = @p + @deslen
end else
begin
return @n
end
end
return @n
ENDgoselect sum(pubs.dbo.charnum('AA,AB,AC',JC)) FROM TABLE
select count(*) count from test
where jc like '%AA%'