请教计算一组资料出现次数。分不够将来再补上了，抱歉!
请教一下，若我要计算表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
…
[解决办法]
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 ')
go
if 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.I2
drop table A,B
/*
I1 I2 次数
---------- ---------- -----------
80 82 1
82 84 1
82 90 2
（所影响的行数为 3 行）
*/
[解决办法]
--原始数据：@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
/*
实际结果：
80822
81840
82801
82841
82901
*/
