导航
  • 主页
  • 基础类
  • 应用实例
  • 新技术前沿

求统计的SQL语句

lizhengqin 2007-12-04 03:13:31
Table (有3个字段:a,b,c 都是字符型)

a b c
11,2,4,5 22 11
123,112 11 12
11 12,14 23,11
.............

我想统计某个字符在某列中出现的次数

例如:11 在 a出现2次,在b出现1次,在c出现2次

把统计的字符定义一个变量,要统计哪一列定义一个变量

环境:Sql server 2000

Sql 语句如何写?
...全文
127 点赞 收藏 17
写回复
17 条回复
切换为时间正序
请发表友善的回复…
发表回复
bluedreams85 2007-12-05
mark
回复
microns 2007-12-04
高手!顶一个
回复
fqbnet2050 2007-12-04
都牛人也,我学习了
回复
rouqu 2007-12-04
接几分
回复
wangxuelid 2007-12-04
xue xi
回复
luyesql 2007-12-04
学习
回复
kk19840210 2007-12-04

select count(1) from table where charindex(','+'70'+',',','+convert(varchar(1000),a)+',')> 0


回复
lizhengqin 2007-12-04
各位对不起,找到原因 了,因为我的程序中某些列不是字符型 ,是整型,我把它改成字符型就可以了
回复
dawugui 2007-12-04
Table (有3个字段:a,b,c 都是字符型)

如果你的表是你上面所说的情况,就不会报错.
回复
lizhengqin 2007-12-04
各位老大,我没有全部测试,测试部分,发现BUG

select count(1) from table where charindex(','+'70'+',',','+a+',')>0

如果a 列中的数据没有逗号会出错
a
11
12
14
44
232
11
70
服务器: 消息 245,级别 16,状态 1,行 3
将 varchar 值 ',' 转换为数据类型为 int 的列时发生语法错误。

如果a 列中任意一条记录有用逗号隔开,则不会报错

哪位老大能否改一下
回复
fymxb 2007-12-04
武林高手!
回复
kk19840210 2007-12-04

declare @a varchar(10)---查询字符
declare @b varchar(10)---查询字段
declare @sql varchar(1000)

select @a='11'
select @b='b'
select @sql='select count(1) from table1 where charindex('',''+'''+@a+'''+'','','',''+'''+@b+'''+'','')>0'
exec @sql


回复
fa_ge 2007-12-04

create table t
(a varchar(100), b varchar(100), c varchar(10))
insert into t

select '11,2,4,5' , '22' , '11' union all
select '123,112' , '11' , '12' union all
select '11' , '12,14' , '23,11'


declare @str varchar(100),@field varchar(10),@s varchar(100)
select @str='12'
select @field='c'
select @s=''
select @s=@s+'select count(1)as '''+@field+''' from t where charindex('''+@str+''','+@field+')>0 '
--print @s

exec(@s)

/*
c
-----------
1

*/

select
distinct
(select count(1) from t where charindex(','+'11'+',',','+a+',')>0)as a,
(select count(1) from t where charindex(','+'11'+',',','+b+',')>0)as b,
(select count(1) from t where charindex(','+'11'+',',','+c+',')>0)as c

from t

/*
a b c
----------- ----------- -----------
2 1 2

(所影响的行数为 1 行)
*/

回复
dawugui 2007-12-04
create table tb(a varchar(20),b  varchar(20),c  varchar(20))
insert into tb values('11,2,4,5','22' ,'11')
insert into tb values('123,112' ,'11' ,'12')
insert into tb values('11' ,'12,14','23,11')
go

select id=identity(int , 1 , 1) , * into tmp1 from tb
SELECT TOP 8000 id = identity(int,1,1) INTO tmp2 FROM syscolumns a, syscolumns b


select isnull(isnull(t1.a,t2.b),t3.c) val , isnull(t1.acnt,0) acnt , isnull(t2.bcnt,0) bcnt , isnull(t3.ccnt,0) ccnt from
(select a , acnt = count(*) from (SELECT A.ID, a = SUBSTRING(A.a, B.ID, CHARINDEX(',', A.a + ',', B.ID) - B.ID) FROM tmp1 a, tmp2 b WHERE SUBSTRING(',' + a.a, B.id, 1) = ',') t group by a) t1 full join
(select b , bcnt = count(*) from (SELECT A.ID, b = SUBSTRING(A.b, B.ID, CHARINDEX(',', A.b + ',', B.ID) - B.ID) FROM tmp1 a, tmp2 b WHERE SUBSTRING(',' + a.b, B.id, 1) = ',') t group by b) t2 on t1.a = t2.b full join
(select c , ccnt = count(*) from (SELECT A.ID, c = SUBSTRING(A.c, B.ID, CHARINDEX(',', A.c + ',', B.ID) - B.ID) FROM tmp1 a, tmp2 b WHERE SUBSTRING(',' + a.c, B.id, 1) = ',') t group by c) t3 on t1.a = t3.c
order by val

GO

drop table tb, tmp1,tmp2

/*
val acnt bcnt ccnt
-------------------- ----------- ----------- -----------
11 2 1 2
112 1 0 0
12 0 0 1
12 0 1 0
123 1 0 0
14 0 1 0
2 1 0 0
22 0 1 0
23 0 0 1
4 1 0 0
5 1 0 0

(所影响的行数为 11 行)
*/
回复
dawugui 2007-12-04
create table tb(a varchar(20),b  varchar(20),c  varchar(20))
insert into tb values('11,2,4,5','22' ,'11')
insert into tb values('123,112' ,'11' ,'12')
insert into tb values('11' ,'12,14','23,11')
go

select
(select count(*) from tb where charindex(',11,' , ',' + a + ',') > 0) a,
(select count(*) from tb where charindex(',11,' , ',' + b + ',') > 0) b,
(select count(*) from tb where charindex(',11,' , ',' + c + ',') > 0) c

drop table tb

/*
a b c
----------- ----------- -----------
2 1 2

(所影响的行数为 1 行)
*/
回复
kk19840210 2007-12-04


declare @a varchar(10)
declare @b varchar(10)
declare @sql varchar(1000)

select @a='11'
select @b='b'
select @sql='select count(1) from table1 where charindex('''+@a+''','''+@b+''')>0'
exec @sql
回复
fa_ge 2007-12-04

create table t
(a varchar(100), b varchar(100), c varchar(10))
insert into t

select '11,2,4,5' , '22' , '11' union all
select '123,112' , '11' , '12' union all
select '11' , '12,14' , '23,11'



select
distinct
(select count(1) from t where charindex(','+'11'+',',','+a+',')>0),
(select count(1) from t where charindex(','+'11'+',',','+b+',')>0),
(select count(1) from t where charindex(','+'11'+',',','+c+',')>0)

from t

/*

----------- ----------- -----------
2 1 2

(所影响的行数为 1 行)
*/
回复
发动态
发帖子
MS-SQL Server
创建于2007-09-28

3.2w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
社区公告
暂无公告