sql 查询数据表后 在统计某一列数据不重复的数量

Question

如题所述

Answer 1

1、创建测试表，

create table test_SalesStatistics (Goods_Name varchar2(200),Order_Quantity varchar2(200),

                      Delivery_Date varchar2(200),Store_Abbreviation varchar2(200),

                      Goodmodel  varchar2(200),Workid varchar2(200),Goodname  varchar2(200) );

2、插入测试数据，

insert into test_SalesStatistics

  select 'name_' || (level / 4),

         level / 4,

         sysdate - level,

         level / 10,

         level / 100,

         level / 8,

         'goods_' || (level / 4)

    from dual

  connect by level < 10000;

3、创建所需临时表，

create table TEST_MID as

SELECT Store_Abbreviation,

       Goodname,

       Goodmodel,

       SUM(Order_Quantity) AS Order_Quantity,

       Workid

  FROM (select Goods_Name,

               Order_Quantity,

               Delivery_Date,

               Store_Abbreviation,

               Goodmodel,

               Workid,

               Goodname        

          from test_SalesStatistics        

         where to_char(Delivery_Date) between '19980810' and '20000810'        

        )

 GROUP BY Store_Abbreviation, Goodmodel, Workid, Goodname

4、统计每一列不重复的数据量，

select count(distinct Store_Abbreviation) Store_Abbreviation,

       count(distinct Goodmodel) Goodmodel,

       count(distinct Workid) Workid ,

       count(distinct Goodname) Goodname

  from TEST_MID t;