请教高手，查询关联商品销售的两个sql语句写法？

Question

销售表xs_table，有4个字段分别如下（含示例数据，交易单号+商品编码为主键）： 门店类别 交易单号 商品编码 销售数量
------------------------------
商业便利 20080101 0001    1.00
商业便利 20080101 0002    1.00
商业便利 20080101 0003    2.00
商业便利 20080102 0001    2.00
商业便利 20080102 0004    2.00
社区便利 20080103 0005    3.00
社区便利 20080103 0001    2.00
其它便利 20080104 0001    1.00
... 商品表sp_table，有3个字段分别如下（与xs_table表的商品编码为主外键关系） 商品编码 商品名称          商品类别 
------------------------------------ 
0001    娃哈哈矿泉水      饮料 
0002    雨润火腿肠        食品 
0003    伊利纯牛奶        奶制品 
0004    康师傅纯净水      饮料 
0005    蒙牛纯牛奶        奶制品 
... 1、想查询出各"门店类别"分组下，与商品编码为0001的商品在相同"交易单号"上出现次数最多的那个商品 2、想查询出各"门店类别"分组下，与"饮料"商品类别在相同"交易单号"上出现次数最多的前3个商品类别 精简了一下

Answer 1

declare @sale table (门店类别 char(8),交易单号 char(8),商品编码 char(4),销售数量 int,顾客类别 char(4))
insert into @sale
select '商业便利',20080101,'0001',   1.00    ,'金类' union 
select '商业便利',20080101,'0002',   1.00    ,'金类' union
select '商业便利',20080101,'0003',   2.00    ,'金类' union
select '商业便利',20080102,'0001',   2.00    ,'银类' union
select '商业便利',20080102,'0004',   2.00    ,'银类' union
select '社区便利',20080103,'0005',   3.00    ,'银类' union
select '社区便利',20080103,'0001',   2.00    ,'银类' union
select '其它便利',20080104,'0001',   1.00    ,'铜类' declare @mer table (商品编码 char(4),商品名称 char(12),商品类别 char(6))
insert into @mer
select '0001',    '娃哈哈矿泉水',      '饮料'   union 
select '0002',    '雨润火腿肠',        '食品'   union
select '0003',    '伊利纯牛奶',        '奶制品' union
select '0004',    '康师傅纯净水',      '饮料'   union
select '0005',    '蒙牛纯牛奶',        '奶制品' declare @ct char(4),@p char(4),@mt char(6)
set @ct='金类' 
set @p='0001'
set @mt='饮料'-- 问题一：指定顾客类别如为"金类"，想查询出各"门店类别"分组下，与商品编码为0001的商品在相同"交易单号"上出现次数最多的那个商品
-- SQL2000
if object_id('tempdb..#tmp') is not null drop table #tmp
select b.门店类别,b.商品编码 as 关联商品,count(b.商品编码) as 出现次数 into #tmp
from @sale a
inner join @sale b on a.交易单号=b.交易单号 
where a.顾客类别=@ct and a.商品编码=@p and b.商品编码<>@p
group by b.门店类别,b.商品编码select 门店类别,@p as 指定商品,关联商品
from #tmp a
where not exists(select 1 from #tmp where 门店类别=a.门店类别 and 出现次数>a.出现次数)--SQL2005
select 门店类别,@p as 指定商品,关联商品
from (
      select *,rank() over(partition by 门店类别 order by 出现次数) as 排序
      from (
            select b.门店类别,b.商品编码 as 关联商品,count(b.商品编码) as 出现次数
            from @sale a
            inner join @sale b on a.交易单号=b.交易单号 
            where a.顾客类别=@ct and a.商品编码=@p and b.商品编码<>@p
            group by b.门店类别,b.商品编码
           ) c
     ) d
where 排序=1
-- 问题二：指定顾客类别如为"金类"，想查询出各"门店类别"分组下，与"饮料"商品类别在相同"交易单号"上出现次数最多的前3个商品类别 
-- SQL2000
if object_id('tempdb..#tmp2') is not null drop table #tmp2
select c.门店类别,d.商品类别 as 关联类别,count(d.商品类别) as 出现次数 into #tmp2
from @sale a
inner join @mer b on a.商品编码=b.商品编码
inner join @sale c on a.交易单号=c.交易单号
inner join @mer d on c.商品编码=d.商品编码 
where a.顾客类别=@ct and b.商品类别=@mt and d.商品类别<>@mt
group by c.门店类别,d.商品类别select 门店类别,@mt as 指定类别,关联类别
from #tmp2 a
where 出现次数 in (select top 3 出现次数 from #tmp where 门店类别=a.门店类别 order by a.出现次数)-- SQL2005
select 门店类别,@mt as 指定类别,关联类别 
from (
      select *,rank() over(partition by 门店类别 order by 出现次数) as 排序
      from (
            select c.门店类别,d.商品类别 as 关联类别,count(d.商品类别) as 出现次数
            from @sale a
            inner join @mer b on a.商品编码=b.商品编码
            inner join @sale c on a.交易单号=c.交易单号
            inner join @mer d on c.商品编码=d.商品编码 
            where a.顾客类别=@ct and b.商品类别=@mt and d.商品类别<>@mt
            group by c.门店类别,d.商品类别
           ) e
     ) f
where 排序<=3

Answer 2

看看sdxiong 能否实现 ::)