求一条SQL谢谢

Question

表结构如下：   TB_City             TB_MobileCityID CityName   MobileID  MobileName  1      北京          1         Iphone  2      上海          2         Android  3      武汉      TB_MainID CityID MobileID1   北京   Iphone2   北京   Android3   上海   Iphone4   上海   Iphone 要求结果如下：CityID  MobileID  Count北京   Iphone        1北京   Android       1上海   Iphone        2上海   Android       0武汉   Iphone        0武汉   Android       0

Answer 1

select B.CityName,C.MobileName,COUNT(A.ID) as COUNT
from TB_Main A
right join TB_City B ON A.CityID=B.CityID
inner join TB_Mobile C ON A.MobileID=C.MobileID
Group by B.CityName,C.MobileName

Answer 2

用的Oracle，前面对应你的几个表可以无视。

with city as(
select '北京' ct from dual
union
select '上海' ct from dual
union
select '武汉' ct from dual
),--对应你的tb_city表
mobile as(
select 'Iphone' mb from dual
union
select 'Android' mb from dual
),--对应你的tb_mobile表
tmain as(
select '北京' ct,'Iphone' mb from dual
union all
select '北京' ct,'Android' mb from dual
union all
select '上海' ct,'Iphone' mb from dual
union all
select '上海' ct,'Iphone' mb from dual
)--对应你的tb_main表
--下面是你要的sql语句:
select ct,mb,sum(c) from(
select ct,mb,0 c from city join mobile on 1=1
union all
select ct,mb,count(1) c from tmain group by ct,mb
) t group by ct,mb
order by ct,mb