首頁(yè) 猿問(wèn) 求一條SQL謝謝

求一條SQL謝謝

SQL Server

慕哥9229398 2018-12-07 02:02:02

表結(jié)構(gòu)如下：?? TB_City???????????? TB_MobileCityID CityName?? MobileID? MobileName? 1????? 北京????????? 1???????? Iphone? 2????? 上海????????? 2???????? Android? 3????? 武漢 ???? TB_MainID CityID MobileID1?? 北京?? Iphone2?? 北京?? Android3?? 上海?? Iphone4?? 上海?? Iphone 要求結(jié)果如下：CityID? MobileID? Count北京?? Iphone??????? 1北京?? Android?????? 1上海?? Iphone??????? 2上海?? Android?????? 0武漢?? Iphone??????? 0武漢?? Android?????? 0

查看完整描述

2 回答

侃侃爾雅

TA貢獻(xiàn)1801條經(jīng)驗(yàn) 獲得超16個(gè)贊

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

反對(duì) 回復(fù) 2019-01-07

ITMISS

TA貢獻(xiàn)1871條經(jīng)驗(yàn) 獲得超8個(gè)贊

用的Oracle，前面對(duì)應(yīng)你的幾個(gè)表可以無(wú)視。

with city as(
select '北京' ct from dual
union
select '上海' ct from dual
union
select '武漢' ct from dual
),--對(duì)應(yīng)你的tb_city表
mobile as(
select 'Iphone' mb from dual
union
select 'Android' mb from dual
),--對(duì)應(yīng)你的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
)--對(duì)應(yīng)你的tb_main表
--下面是你要的sql語(yǔ)句:
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