请教计算一组资料出现次数。分不够将来再补上了，抱歉!

Question

请教一下，若我要计算表A中两个字符之组合在表B中不同天出现的次数(条件：1.同一ID出现一次以上只计一次。  2.不同天出现才计算)，要如何做比较好？
表A:
I1   I2
80   82
81   84
82   84
82   90
82   80
…表B:
A1  06  6 2005   80
A1  06  6 2005   82
A1  09 23 2005   82
A1        09 23 2005   90
A1  06  7 2005         80
A1        06 13 2005   82
A1  06 13 2005  84
A2  01 17 2004   3
A2  01 17 2004   82
A2  01 17 2004   90
A2  01 17 2004        80
A2  12  7 2004   82
…结果：
I1    I2     次数
80   82      2
81   84      0
82   84      1
82   90      1
82   80      1
…

Answer 1

对了，因为ID有的很多笔，最多的有二三佰笔，所以若将表B由行转列可能不适合，先在此说明一下，谢谢!

Answer 2

create table A(I1 varchar(10),  I2 varchar(10))
insert into A values('80',   '82')
insert into A values('81',   '84')
insert into A values('82',   '84')
insert into A values('82',   '90')
insert into A values('82',   '80')
create table B(I1 varchar(30),  I2 varchar(10))
insert into B values('A1 06  6 2005',  '80')
insert into B values('A1 06  6 2005',  '82')
insert into B values('A1 09 23 2005',  '82')
insert into B values('A1 09 23 2005',  '90')
insert into B values('A1 06  7 2005',  '80')
insert into B values('A1 06 13 2005',  '82')
insert into B values('A1 06 13 2005',  '84')
insert into B values('A2 01 17 2004',  '3')
insert into B values('A2 01 17 2004',  '82')
insert into B values('A2 01 17 2004',  '90')
insert into B values('A2 01 17 2004',  '80')
insert into B values('A2 12  7 2004',  '82')
goif object_id('pubs..f_hb') is not null
   drop function f_hb
go--创建一个合并的函数
create function f_hb(@I1 varchar(30))
returns varchar(8000)
as
begin
  declare @str varchar(8000)
  set @str = ''
  select @str = @str + ',' + cast(I2 as varchar) from B where I1 = @I1 
  set @str = right(@str , len(@str) - 1)
  return(@str)
End
go--调用自定义函数得到结果：select a.* , count(*) 次数 from A ,
(select distinct I1 ,dbo.f_hb(I1) as I2 from B) t
where charindex(a.I1 + ',' + a.I2 , t.I2) > 0
group by a.I1,a.I2drop table A,B/*
I1         I2         次数          
---------- ---------- ----------- 
80         82         1
82         84         1
82         90         2
（所影响的行数为 3 行）
*/

Answer 3

create table A(I1 varchar(10),  I2 varchar(10))
insert into A values('80',   '82')
insert into A values('81',   '84')
insert into A values('82',   '84')
insert into A values('82',   '90')
insert into A values('82',   '80')
create table B(I1 varchar(30),  I2 varchar(10))
insert into B values('A1 06  6 2005',  '80')
insert into B values('A1 06  6 2005',  '82')
insert into B values('A1 09 23 2005',  '82')
insert into B values('A1 09 23 2005',  '90')
insert into B values('A1 06  7 2005',  '80')
insert into B values('A1 06 13 2005',  '82')
insert into B values('A1 06 13 2005',  '84')
insert into B values('A2 01 17 2004',  '3')
insert into B values('A2 01 17 2004',  '82')
insert into B values('A2 01 17 2004',  '90')
insert into B values('A2 01 17 2004',  '80')
insert into B values('A2 12  7 2004',  '82')
goif object_id('pubs..f_hb') is not null
   drop function f_hb
go--创建一个合并的函数
create function f_hb(@I1 varchar(30))
returns varchar(8000)
as
begin
  declare @str varchar(8000)
  set @str = ''
  select @str = @str + ',' + cast(I2 as varchar) from B where I1 = @I1 
  set @str = right(@str , len(@str) - 1)
  return(@str)
End
go--调用自定义函数得到结果：
select a.* , isnull(m.次数 , 0) 次数 from a
left join
(
  select a.* , count(*) 次数 from A ,
  (select distinct I1 ,dbo.f_hb(I1) as I2 from B) t
  where charindex(a.I1 + ',' + a.I2 , t.I2) > 0
  group by a.I1,a.I2
) m
on a.i1 = m.i1 and a.i2 = m.i2drop table A,B
drop function f_hb
/*
I1         I2         次数          
---------- ---------- ----------- 
80         82         1
81         84         0
82         84         1
82         90         2
82         80         0
（所影响的行数为 5 行）
*/

Answer 4

抱歉!
表B的字段有三个分别为：
ID      DATE        I
A1    06  6 2005     80
A1    06  6 2005     82
A1    09 23 2005      82
A1    09 23 2005      90
A1    06  7 2005      80
A1    06 13 2005      82
A1    06 13 2005      84
A2    01 17 2004      3
A2    01 17 2004      82
A2    01 17 2004      90
A2    01 17 2004      80
A2    12  7 2004      82
…

Answer 5

--原始数据：@A
declare @A table(I1 int,I2 int)
insert @A
select 80,82  union all
select 81,84  union all
select 82,84  union all
select 82,90  union all
select 82,80 
--原始数据：@B
declare @B table(ID varchar(2),DATE datetime,I int)
insert @B
select 'A1','06 6 2005',80  union all
select 'A1','06 6 2005',82  union all
select 'A1','09 23 2005',82  union all
select 'A1','09 23 2005',90  union all
select 'A1','06 7 2005',80  union all
select 'A1','06 13 2005',82  union all
select 'A1','06 13 2005',84  union all
select 'A2','01 17 2004',3  union all
select 'A2','01 17 2004',82  union all
select 'A2','01 17 2004',90  union all
select 'A2','01 17 2004',80  union all
select 'A2','12 7 2004',82 /*
要求结果：
80 82 2
81 84 0
82 84 1
82 90 1
82 80 1
*/select a.*,count(*) from @A a join @B b on a.I1=b.I join @B c on a.I2=c.I
where b.DATE=c.DATE
group by a.I1,a.I2
/*
实际结果：
80 82 2
82 80 2
82 84 1
82 90 2
*/--结果有出入，可能是我没理解好题目要求，麻烦楼主看看。

Answer 6

create table A(I1 varchar(10),  I2 varchar(10))
insert into A values('80',   '82')
insert into A values('81',   '84')
insert into A values('82',   '84')
insert into A values('82',   '90')
insert into A values('82',   '80')
create table B(id varchar(5),dt varchar(12),I2 varchar(2))
insert into B values('A1','06 6 2005',  '80')
insert into B values('A1', '06 6 2005',  '82')
insert into B values('A1', '09 23 2005',  '82')
insert into B values('A1', '09 23 2005',  '90')
insert into B values('A1', '06 7 2005',  '80')
insert into B values('A1', '06 13 2005',  '82')
insert into B values('A1', '06 13 2005',  '84')
insert into B values('A2', '01 17 2004',  '3')
insert into B values('A2', '01 17 2004',  '82')
insert into B values('A2', '01 17 2004',  '90')
insert into B values('A2', '01 17 2004',  '80')
insert into B values('A2', '12 7 2004',  '82')-- select distinct * from a,b where a.i1=b.i or a.i2=b.i
-- order by date
-- 
-- drop table a
-- drop table b
if object_id('pubs..f_hb') is not null
   drop function f_hb
go--创建一个合并的函数
create function f_hb(@dt varchar(30))
returns varchar(8000)
as
begin
  declare @str varchar(8000)
  set @str = ''
  select @str = @str + ',' + cast(I2 as varchar) from B where dt = @dt 
  set @str = right(@str , len(@str) - 1)
  return(@str)
Endgo
--调用自定义函数得到结果：
select a.* , isnull(m.次数 , 0) 次数 from a
left join
(
  select a.* , count(*) 次数 from A ,
  (select distinct dt ,dbo.f_hb(dt) as I2 from B) t
  where charindex(a.i1 + ',' + a.I2 , t.I2) > 0
  group by a.i1,a.I2
) m
on a.i1 = m.i1 and a.i2 = m.i2drop table A,B
drop function f_hb

Answer 7

hlbhann() ( ) 信誉：100 
抱歉!
表B的字段有三个分别为：做法一样.函数中多加个参数即可.create table A(I1 varchar(10),  I2 varchar(10))
insert into A values('80',   '82')
insert into A values('81',   '84')
insert into A values('82',   '84')
insert into A values('82',   '90')
insert into A values('82',   '80')
create table B(I1 varchar(10),  I2 varchar(10) , I3 varchar(10))
insert into B values('A1', '06  6 2005',  '80')
insert into B values('A1', '06  6 2005',  '82')
insert into B values('A1', '09 23 2005',  '82')
insert into B values('A1', '09 23 2005',  '90')
insert into B values('A1', '06  7 2005',  '80')
insert into B values('A1', '06 13 2005',  '82')
insert into B values('A1', '06 13 2005',  '84')
insert into B values('A2', '01 17 2004',  '3')
insert into B values('A2', '01 17 2004',  '82')
insert into B values('A2', '01 17 2004',  '90')
insert into B values('A2', '01 17 2004',  '80')
insert into B values('A2', '12  7 2004',  '82')
goif object_id('pubs..f_hb') is not null
   drop function f_hb
go--创建一个合并的函数
create function f_hb(@I1 varchar(10) , @I2 varchar(10))
returns varchar(8000)
as
begin
  declare @str varchar(8000)
  set @str = ''
  select @str = @str + ',' + cast(I3 as varchar) from B where I1 = @I1 and I2 = @I2
  set @str = right(@str , len(@str) - 1)
  return(@str)
End
go--调用自定义函数得到结果：
select a.* , isnull(m.次数 , 0) 次数 from a
left join
(
  select a.* , count(*) 次数 from A ,
  (select distinct I1,I2 ,dbo.f_hb(I1,I2) as I3 from B) t
  where charindex(a.I1 + ',' + a.I2 , t.I3) > 0
  group by a.I1,a.I2
) m
on a.i1 = m.i1 and a.i2 = m.i2drop table A,B
drop function f_hb
/*
I1         I2         次数          
---------- ---------- ----------- 
80         82         1
81         84         0
82         84         1
82         90         2
82         80         0
（所影响的行数为 5 行）
*/

Answer 8

TO:Limpire(昨夜小楼):
实际结果：
80 82 2  (对)
82 80 2  (不对，A2的此组合是出现在同一天不算)
82 84 1  (对)
82 90 2  (不对，A2的此组合是出现在同一天不算)
谢谢您!

Answer 9

TO:Limpire(昨夜小楼):
您可能没看清楚我的说明，两个组合是出现在不同日期的才算，以小楼您上面的写法是同一天的才算。
而若两组合出现一前一后不同日期是不是就可以照表A顺序做出来呢？

Answer 10

--原始数据：@A
declare @A table(I1 int,I2 int)
insert @A
select 80,82  union all
select 81,84  union all
select 82,84  union all
select 82,90  union all
select 82,80 
--原始数据：@B
declare @B table(ID varchar(2),DATE datetime,I int)
insert @B
select 'A1','06 6 2005',80  union all
select 'A1','06 6 2005',82  union all
select 'A1','09 23 2005',82  union all
select 'A1','09 23 2005',90  union all
select 'A1','06 7 2005',80  union all
select 'A1','06 13 2005',82  union all
select 'A1','06 13 2005',84  union all
select 'A2','01 17 2004',3  union all
select 'A2','01 17 2004',82  union all
select 'A2','01 17 2004',90  union all
select 'A2','01 17 2004',80  union all
select 'A2','12 7 2004',82 /*
要求结果：
80 82 2
81 84 0
82 84 1
82 90 1
82 80 1
*/select a.*,sum(case when b.ID is null then 0 else 1 end) from
@A a left join
(select a.ID,I1=a.I,I2=b.I from @B a join @B b on a.ID=b.ID and a.DATE<b.DATE and a.I<>b.I group by a.ID,a.I,b.I) b
on a.I1=b.I1 and a.I2=b.I2
group by a.I1,a.I2
order by a.I1,a.I2/*
实际结果：
80 82 2
81 84 0
82 80 1
82 84 1
82 90 1
*/

Answer 11

TO:Limpire(昨夜小楼):
您真是太厉害了!!! 
我先研究看看，有问题再请教您!
感谢您的帮忙!^^

Answer 12

TO:Limpire(昨夜小楼):
请问表A改成三个字段的话，求在表B出现的次数，条件如之前的，要如何改语法呢？我想了半天还是改不成功。

Answer 13

--tryselect a.*,sum(case when b.ID is null then 0 else 1 end) from
@A a left join(
select a.ID,I1=a.I,I2=b.I,I3=c.I
from @B a
join @B b
on a.ID=b.ID and a.DATE<b.DATE
join @B c
on b.ID=c.ID and b.DATE<c.DATE
group by a.ID,a.I,b.I,c.I
) bon a.I1=b.I1 and a.I2=b.I2,a.I3=b.I3
group by a.I1,a.I2,a.I3
order by a.I1,a.I2,a.I3

Answer 14

TO:Limpire(昨夜小楼):
救命!!!
上面的语法可以查到我要的了，可是当我要查的字段组合越来越长时，相对的JOIN就会更多，我的表B是约一万多笔的资料量，目前我查到四个长度，速度已慢到快不行，超过一小时了，真是千呼万唤…还是不出来^^
小楼快想想有什么好办法救我呀!!!

Answer 15

ID             DATE                   I 
A1         06    6   2005            80 
A1         06    6   2005            82 
A1         09   23   2005            82 
A1         09   23   2005            90 
A1         06    7   2005            80 
A1         06   13   2005            82 
A1         06   13   2005            84 
A2         01   17   2004            3 
A2         01   17   2004            82 
A2         01   17   2004            90 
A2         01   17   2004            80 
A2         12    7   2004            82 
…
若将表B以下表的方式表达，是不是可以让效能更快些？
(同一ID的资料全放在一个字段里，以时间排序)ID                       I 
A1       (80 ,82)(82,90)(80)(82,84)
A2       (3,82,90,80)(82)…
…结果如下: 
I1       I2      次数
80       82      2
81       84      0
82       84      1
82       90      1
82       80      1
…

Answer 16

但是用join的方法会占非常大的存贮器及耗时，小楼及各位大哥可否帮我看看若用19楼我提出的方法要如何写出语法，乌龟的结果不正确，感谢您！