表结构如下:
TB_City TB_Mobile
CityID CityName MobileID MobileName
1 北京 1 Iphone
2 上海 2 Android
3 武汉 TB_Main
ID CityID MobileID
1 北京 Iphone
2 北京 Android
3 上海 Iphone
4 上海 Iphone结果如下:
CityID MobileID Count
北京 Iphone 1
北京 Android 1
上海 Iphone 2
上海 Android 0
武汉 Iphone 0
武汉 Android 0
sql
TB_City TB_Mobile
CityID CityName MobileID MobileName
1 北京 1 Iphone
2 上海 2 Android
3 武汉 TB_Main
ID CityID MobileID
1 北京 Iphone
2 北京 Android
3 上海 Iphone
4 上海 Iphone结果如下:
CityID MobileID Count
北京 Iphone 1
北京 Android 1
上海 Iphone 2
上海 Android 0
武汉 Iphone 0
武汉 Android 0
sql
with TB_City(CityID, CityName)as(
select 1, '北京' union all
select 2, '上海' union all
select 3, '武汉' )
,TB_Mobile(MobileID, MobileName)as(
select 1, 'Iphone' union all
select 2, 'Android')
,TB_Main(ID, CityID, MobileID)as(
select 1, '北京', 'Iphone' union all
select 2, '北京', 'Android' union all
select 3, '上海', 'Iphone' union all
select 4, '上海', 'Iphone')
select CityName,MobileName,COUNT(id) from TB_City join TB_Mobile
on 1=1 left join TB_Main on TB_Main.CityID=TB_City.cityname and TB_Mobile.MobileName=TB_Main.MobileID
group by CityName,MobileName order by 1,2 desc
,m.MobileName as MobileID
,count(ma.ID) as Count
from TB_City c cross join TB_Mobile m
left join TB_Main ma
on ma.CityID=c.CityName and ma.MobileID=m.MobileName
group by c.CityName
,m.MobileName
GO
-->生成表TB_Cityif object_id('TB_City') is not null
drop table TB_City
Go
Create table TB_City([CityID] smallint,[CityName] nvarchar(2))
Insert into TB_City
Select 1,N'北京'
Union all Select 2,N'上海'
Union all Select 3,N'武汉'
-->生成表TB_Mobileif object_id('TB_Mobile') is not null
drop table TB_Mobile
Go
Create table TB_Mobile([MobileID] smallint,[MobileName] nvarchar(7))
Insert into TB_Mobile
Select 1,N'Iphone'
Union all Select 2,N'Android'
-->生成表TB_Mainif object_id('TB_Main') is not null
drop table TB_Main
Go
Create table TB_Main([ID] smallint,[CityID] nvarchar(2),[MobileID] nvarchar(7))
Insert into TB_Main
Select 1,N'北京',N'Iphone'
Union all Select 2,N'北京',N'Android'
Union all Select 3,N'上海',N'Iphone'
Union all Select 4,N'上海',N'Iphone'
Select
b.CityName
,c.MobileName
,COUNT(a.ID)
From TB_Main As a
Right Join( TB_City As b
Full Join TB_Mobile As c On 1=1
) On a.CityID=b.CityName And a.MobileID=c.MobileName
GROUP BY
b.CityName
,c.MobileName
ORDER BY
MIN(b.CityID),MIN(c.MobileID)/*
CityName MobileName
-------- ---------- -----------
北京 Iphone 1
北京 Android 1
上海 Iphone 2
上海 Android 0
武汉 Iphone 0
武汉 Android 0
*/
on 1=1 left join TB_Main on TB_Main.CityID=TB_City.cityname and TB_Mobile.MobileName=TB_Main.MobileID
group by CityName,MobileName order by CityName ,MobileName desc