sql查询语句（组合语句中避免重复）

有如下两张表：
--订单表
create table basket(order_id number,       --订单号
                    item varchar2(20),     --商品名称
                    amount number);        --商品数量
--商品种类表
create table item(id number,               --商品编号
                  name varchar(20));       --商品名称

需求是：求任意2种商品同时出现在一个订单中的次数

item表和basket表数据

SQL> select * from item;
 
        ID NAME
---------- ----------
         1 牛奶
         4 西瓜
         3 芒果
         2 面包
 
SQL> select * from basket;
 
  ORDER_ID ITEM           AMOUNT
---------- ---------- ----------
         1 牛奶                1
         1 面包                3
         2 面包                5
         2 牛奶                3
         1 芒果                4
         2 芒果                5
         3 牛奶                5
         3 面包                2
         4 芒果                1
         4 西瓜                6
         4 牛奶                6
 
11 rows selected
 
SQL>

以上两个表中id排序都是无序的

 从商品表中的个数可以看出总共有4*（4-1）=12次全排 但是’牛奶+面包’与’面包+牛奶’是相同，需要去掉这样商品就是组合关系 应该4*（4-1）/2 =6次 

6种模式分别为： 牛奶+面包  1（编号）

                                     牛奶+西瓜   2

                                     牛奶+芒果   3

                                     面包+西瓜   4

                                     面包+芒果   5

                                     西瓜+芒果   6

现在分析订单表

 

订单1的组合有： 牛奶+面包 1  （编号）

                                     牛奶+芒果 3

                                     面包+芒果 5 

 

订单2的组合有： 牛奶+面包 1（编号）

                                     牛奶+芒果 3

                                     面包+芒果 5 

 

订单3的组合有：牛奶+面包 1

 

订单4的组合有：牛奶+西瓜 2

                                     牛奶+芒果 3

                                     西瓜+芒果 6

牛奶+面包  1：总共有1,1，1（3个）

牛奶+西瓜   2：总共有2 （1个）

牛奶+芒果   3：总共有3,3,3（3个）

面包+西瓜   4：总共有  （0个）

面包+芒果   5：总共有5,5 （2个）

西瓜+芒果   6：总共有6（1个）

 

按序排列：

牛奶+面包  3

牛奶+芒果  3

面包+芒果  2

西瓜+牛奶  1（也是 牛奶+西瓜）

西瓜+芒果  1

西瓜+面包  0

sql语句：

分别查询item 表和basket表得出2个商品的组合关系（切记不是排列关系）然后再结合这两个查询就可以得出结果了

select *

  from (select a.combination, nvl(b.amount, '0') amount

          from (select a || '+' || b combination

                  from (select a.name a, b.name b

                          from item a, item b

                         where a.name != b.name

                         order by b.name, a.name)

                 where a > b) a

          left join (select a || '+' || b combination, count(*) amount

                      from (select a.order_id, a.item a, b.item b

                              from basket a

                              join basket b

                                on a.item != b.item

                               and a.order_id = b.order_id

                             where a.item > b.item)

                     group by a, b) b

            on a.combination = b.combination)

 order by amount desc;

查询结果如下：

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/27013009/viewspace-1245188/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/27013009/viewspace-1245188/