有个表:
编号 名称 内容 日期
1 A char1,char2.... 2006-11-14
2 A char2,char5.... 2006-11-14
1 B char1,char2.... 2006-11-14
3 C char3,char4.... 2006-11-14内容中字符数量不定,都是用逗号隔开。
要求按编号,名称,日期来统计内容中字符出现的次数。(就是char1,char2等每天出现的次数)
编号 名称 内容 日期
1 A char1,char2.... 2006-11-14
2 A char2,char5.... 2006-11-14
1 B char1,char2.... 2006-11-14
3 C char3,char4.... 2006-11-14内容中字符数量不定,都是用逗号隔开。
要求按编号,名称,日期来统计内容中字符出现的次数。(就是char1,char2等每天出现的次数)
insert into test select 1,'A','char1,char2','2006-11-14'
insert into test select 2,'A','char2,char5','2006-11-14'
insert into test select 1,'B','char1,char2','2006-11-14'
insert into test select 3,'C','char3,char4','2006-11-14'
goset rowcount 8000
select identity(int,1,1) as num into #1 from sysobjects a,syscolumns b
set rowcount 0select
a.*,b.num
into #2
from
test a,
#1 b
where
substring(','+a.content+',',b.num,1)=','select
t.id,t.code,t.date,t.[char],count(*) as [num]
from
(select
a.id,a.code,a.date,substring(a.content,a.num,min(b.num)-2) as [char]
from
#2 a,#2 b
where
a.id=b.id and a.code=b.code and a.date=b.date
and
a.num<b.num
group by
a.id,a.code,a.content,a.date,a.num) t
group by
t.id,t.code,t.date,t.[char]
go
/*
id code date char num
---- ------ ------------------------- ------- -----
1 A 2006-11-14 00:00:00.000 char1 1
1 A 2006-11-14 00:00:00.000 char2 1
1 B 2006-11-14 00:00:00.000 char1 1
1 B 2006-11-14 00:00:00.000 char2 1
2 A 2006-11-14 00:00:00.000 char2 1
2 A 2006-11-14 00:00:00.000 char5 1
3 C 2006-11-14 00:00:00.000 char3 1
3 C 2006-11-14 00:00:00.000 char4 1
*/drop table test,#1,#2
go
select 编号,名称,日期,內容,count(*) from tablename
group by 编号,名称,日期,內容编号 名称 内容 日期
1 A char1 2006-11-14
1 A char2 2006-11-14
...
2 A char2 2006-11-14
2 A char5 2006-11-14
...
1 B char1 2006-11-14
1 B char2 2006-11-14
...
3 C char3 2006-11-14
3 C char4 2006-11-14
...
字符串 编号 名称 日期 合计
char1 1 A 2006-11-14 20
char2 1 A 2006-11-14 22
char1 2 A 2006-11-14 23
char3 1 B 2006-11-14 33
分别按编号、名称和日期来分组,进行模糊查询就行了。
每条记录的“内容”中,不会出现相同的字串吧? 比如CHAR1,CHAR2,CHAR1……
分别按编号、名称和日期来分组,进行模糊查询就行了。
====
不会
insert into test select 1,'A','char1,char2','2006-11-14'
insert into test select 2,'A','char2,char5','2006-11-14'
insert into test select 1,'B','char1,char2','2006-11-14'
insert into test select 3,'C','char3,char4','2006-11-14'
goset rowcount 8000
select identity(int,1,1) as num into #1 from sysobjects a,syscolumns b
set rowcount 0select
a.*,b.num
into #2
from
test a,
#1 b
where
substring(','+a.content+',',b.num,1)=','select
字符串=t.[char],
编号=t.id,
名称=t.code,
日期=t.date,
合计=count(*)
from
(select
a.id,a.code,a.date,substring(a.content,a.num,min(b.num)-2) as [char]
from
#2 a,#2 b
where
a.id=b.id and a.code=b.code and a.date=b.date
and
a.num<b.num
group by
a.id,a.code,a.content,a.date,a.num) t
group by
t.id,t.code,t.date,t.[char]
go
/*
字符串 编号 名称 日期 合计
------- ---- ------ ------------------------- -----
char1 1 A 2006-11-14 00:00:00.000 1
char2 1 A 2006-11-14 00:00:00.000 1
char1 1 B 2006-11-14 00:00:00.000 1
char2 1 B 2006-11-14 00:00:00.000 1
char2 2 A 2006-11-14 00:00:00.000 1
char5 2 A 2006-11-14 00:00:00.000 1
char3 3 C 2006-11-14 00:00:00.000 1
char4 3 C 2006-11-14 00:00:00.000 1
*/drop table test,#1,#2
go
也可能是 char1,char2,..........char100
也可能是 char1,char2,..........char100
----------------------------------------------------------------------------------
字符是SQL语句从数据中自动获取的,我并没有在SQL语句中写成固定值,只不过引用楼主的示例数据罢了。
编号 varchar(10),
名称 varchar(10),
内容 varchar(100),
日期 datetime
)insert into tb(编号,名称,内容,日期) values('1','A','char1,char2','2006-11-14')
insert into tb(编号,名称,内容,日期) values('2','A','char2,char5','2006-11-14')
insert into tb(编号,名称,内容,日期) values('1','B','char1,char2','2006-11-14')
insert into tb(编号,名称,内容,日期) values('3','C','char3,char4','2006-11-14')
insert into tb(编号,名称,内容,日期) values('1','A','char1,char2','2006-11-15')
insert into tb(编号,名称,内容,日期) values('2','A','char2,char5','2006-11-15')
insert into tb(编号,名称,内容,日期) values('1','B','char1,char2','2006-11-15')
insert into tb(编号,名称,内容,日期) values('3','C','char3,char4','2006-11-16')
go--建立辅助的临时表
select id=identity(int,1,1) , * into test1 from tbselect top 8000 id = identity(int,1,1) into test2 from syscolumns a, syscolumns b select
A.id,
字符串 = substring(A.内容, B.id, charindex(',' , A.内容 + ',' , B.ID) - B.ID)
into test3
from test1 A, test2 B
where substring(',' + a.内容, B.id, 1) = ','
order by 1 , 2
GOselect a.字符串 , b.编号 , b.名称 , convert(varchar(10),b.日期,120) as 日期 , count(*) as 合计
from test3 a,test1 b
where a.id = b.id
group by 字符串 , 编号 , 名称 , convert(varchar(10),日期,120)
order by 字符串 , 编号 , 名称 , convert(varchar(10),日期,120)drop table tb
drop table test1
drop table test2
drop table test3字符串 编号 名称 日期 合计
------ ---- ---- ---------- ----
char1 1 A 2006-11-14 1
char1 1 A 2006-11-15 1
char1 1 B 2006-11-14 1
char1 1 B 2006-11-15 1
char2 1 A 2006-11-14 1
char2 1 A 2006-11-15 1
char2 1 B 2006-11-14 1
char2 1 B 2006-11-15 1
char2 2 A 2006-11-14 1
char2 2 A 2006-11-15 1
char3 3 C 2006-11-14 1
char3 3 C 2006-11-16 1
char4 3 C 2006-11-14 1
char4 3 C 2006-11-16 1
char5 2 A 2006-11-14 1
char5 2 A 2006-11-15 1(所影响的行数为 16 行)