SQL统计语句技巧

select t.*, t.rowid from ts_orders t;
select * from ts_cust;

 

-- 查询 type=0 ,1 的订单数
select
count (o.oid) as 总订单数 ,
count (o1.oid) as type1_订单总数 ,
count (o2.oid) as type0_订单总数

from ts_orders o
left join ts_orders o1 on o1.oid =o.oid and o1.type = 1
left join ts_orders o2 on o2.oid =o.oid and o2.type = 0

where 1=1

;

-- 统计 下单人数 （一个人有可能下多个订单，但也只算1个）

select
count ( distinct o.cid) as 总下单人数 ,
count ( distinct o1.cid) as type1_下单人数 ,
count ( distinct o2.cid) as type0_下单人数

from ts_orders o
left join ts_orders o1 on o1.oid =o.oid and o1.type = 1
left join ts_orders o2 on o2.oid =o.oid and o2.type = 0

where 1=1
;

-- 统计每个用户不同类型的订单数量
select
--c.cid as 客户编号,
 c.name as 客户名称,
count(o.oid) as 订单总数 ,
count(o1.oid) as type1_订单总数,
count(o2.oid) as type0_订单总数

from  ts_cust c
left join ts_orders o on c.cid =o.cid
left join ts_orders o1 on c.cid =o1.cid and o.oid =o1.oid and o1.type =1
left join ts_orders o2 on c.cid =o2.cid and o.oid =o2.oid and o2.type =0

where 1=1
group by c.name
order by 订单总数 desc
;