SQL 收藏

 group by  , having

实例

我们拥有下面这个 "Orders" 表：                      第16317名

O_Id	OrderDate	OrderPrice	Customer
1	2008/12/29	1000	Bush
2	2008/11/23	1600	Carter
3	2008/10/05	700	Bush
4	2008/09/28	300	Bush
5	2008/08/06	2000	Adams
6	2008/07/21	100	Carter

现在，我们希望查找订单总金额少于 2000 的客户。

我们使用如下 SQL 语句：

SELECT Customer,SUM(OrderPrice) FROM Orders
GROUP BY Customer
HAVING SUM(OrderPrice)<2000

结果集类似：

Customer	SUM(OrderPrice)
Carter	1700

 

--------------------------------------------------------------------------------------------------------------------------------------------------

('NXT', 'NXTSTK'）为大项

--地区定购大小项的用户明细  
select count(*) from (
select distinct mobile, t.sub_time
  from adc_test.t_subscribe_cmpp t
 where area_id = '745'
   and service_id in ('NXT', 'NXTSTK')
   and sub_status = 1
   and (select 1
          from adc_test.t_subscribe_cmpp
         where mobile = t.mobile
           and service_id <> 'NXT'
           and service_id <> 'NXTSTK'
           and sub_status = 1
           and rownum = 1) = 1
           )
          
--只定大项没定小项的用户明细          
select count(*) from (
select distinct mobile, t.sub_time
  from adc_test.t_subscribe_cmpp t
 where area_id = '745'
   and service_id in ('NXT', 'NXTSTK')
   and sub_status = 1
   and (select 1
          from adc_test.t_subscribe_cmpp
         where mobile = t.mobile
           and service_id <> 'NXT'
           and service_id <> 'NXTSTK'
           and sub_status = 1
           and rownum = 1) is null
           )
          
          
--只定小项没定大项的用户      
select count(*) from (
select distinct mobile, t.sub_time
  from adc_test.t_subscribe_cmpp t
 where area_id = '745'
   and service_id <> 'NXT'
   and service_id <> 'NXTSTK'
   and sub_status = 1
   and (select 1
          from adc_test.t_subscribe_cmpp
         where mobile = t.mobile
           and service_id in ('NXT', 'NXTSTK')
           and sub_status = 1
           and rownum = 1) is null
           )

 

 

--在号码中查询没有定制NXT业务
Select * From tbl_boss_nxt t Where
 (Select 1 From adc_test.t_subscribe_cmpp Where

mobile=t.mobile2 and rownum = 1 And   area_id = '735' And

sub_time>=to_date('2010-7-13 00:00','YYYY-MM-DD HH24:MI:SS') 

and sub_time<=to_date('2010-7-19 00:00','YYYY-MM-DD

HH24:MI:SS')) Is Null

--查询ASSESSDB表中已经定了大小项的
 select mobile from ASSESSDB Where mobile In (  
select distinct mobile
  from adc_test.t_subscribe_cmpp t
 where area_id = '745'
   and service_id in ('NXT', 'NXTSTK')
   and sub_status = 1   
   and (select 1
          from adc_test.t_subscribe_cmpp
         where mobile = t.mobile
           and service_id <> 'NXT'
           and service_id <> 'NXTSTK'
           and sub_status = 1
           and rownum = 1) = 1
           )