SQL编程(不限定何种数据库，可使用mysql,oracle

, count(distinct sid)

,sum(case when s.cityid=苏州and sid is not null then o. ordercnt end) as suzhoucnt

,sum(case when o.ordercnt >0 then 1 else 0 end) as orderusercnt

,sum(case when s.sid is null then ordercnt else 0 end) as nonameusercnt

,count( distnct case when 0.2017cnt>0 then sid end ) as 2017ordersid

from U u  

left join (

Select

userid

,count(orderid) as ordercnt

, sum(case when date between 20170101 and 20171231 then 1 else 0) as 2017cnt

from O group by userid

) o on u.userid=o.userid

SELECT a.level,a.c1,a.c2,b.c3,c.c4,d.c5,e.c6
from (
    SELECT level,count(distinct userid) as c1,count(DISTINCT sid) as c2
    from U
    group by level
) a join (
    SELECT level,count(orderid) as c3
    from O left join U
    on o.userid = u.userid
    left join S
    on s.sid = u.sid
    where s.cityid = '苏州'
    and u.sid is not null
    group by u.level
) b on a.level = b.level
join (
    SELECT u.level,count(DISTINCT userid) as c4
    from O left join U
    on o.userid = u.userid
    group by level
) c
on b.level = c.level
join (
    SELECT level,count(orderid) as c5
    from O left join U
    on o.userid = u.userid
    where u.sid is null
    group by u.level
) d
on d.level = c.level
join (
    SELECT level,count(DISTINCT sid) as c6
    from O left join U
    on o.userid = u.userid
    where u.sid is not null
    and year(date)='2017'
    group by level
) e
on e.level = d.level