日期:2014-05-17 浏览次数:20757 次
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
USE test
GO
-->生成表TB_City
if 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'武汉'