if object_id('tempdb..##tmpA') is not null
drop table ##tmpA
Create table ##tmpA (
Transaction_id varchar(10),Product_id varchar(10))
insert into ##tmpA
select '1','A'
union all
select '1','B'
union all
select '2','A'
union all
select '2','B'
union all
select '2','C'
union all
select '3','A'
union all
select '3','B'
union all
select '3','D'
union all
select '4','B'
union all
select '4','C'
select * from ##tmpA
--***以上内容直接复制就是表的结构了***
--查询最长出现的是哪2个产品组合
--结果应该为
--产品组合、交易单数
AB 3
BC 2
AC 1
AD 1
------解决方案--------------------
没看明白哪里来的AD 1？？？
AC 1？？？
------解决方案--------------------
- SQL code
if object_id('tempdb..##tmpA') is not null
drop table ##tmpA
Create table ##tmpA (
Transaction_id varchar(10),Product_id varchar(10))
insert into ##tmpA
select '1','A'
union all
select '1','B'
union all
select '2','A'
union all
select '2','B'
union all
select '2','C'
union all
select '3','A'
union all
select '3','B'
union all
select '3','D'
union all
select '4','B'
union all
select '4','C'
select a.Product_id + b.Product_id as 产品组合,count(1) as 交易单数
from ##tmpA a, ##tmpA b
where a.Transaction_id = b.Transaction_id
and a.Product_id <b.Product_id
group by a.Product_id + b.Product_id
order by count(1) desc
--结果
产品组合 交易单数
-------------------- -----------
AB 3
BC 2
BD 1
AC 1
AD 1
(5 行受影响)------解决方案--------------------
好没有看懂，结果就出来了
------解决方案--------------------
学习海爷！
------解决方案--------------------
xiao F也是热心专家，听他得没错得
------解决方案--------------------
SQL2005
- SQL code
;with t as ( select rn=row_number()over(order by Transaction_id),* from ##tmpA)select a.Product_id+b.Product_id Product_id,count(1) as cntfrom t a join t b on a.rn<b.rn and a.Transaction_id=b.Transaction_idgroup by a.Product_id+b.Product_idorder by 2 desc/*Product_id cnt-------------------- -----------AB 3BC 2BD 1AC 1AD 1(5 行受影响)*/