27,579
社区成员
发帖
与我相关
我的任务
分享
create table addressinfo(Id int,Name nvarchar(10),SuperiorId int)
insert into addressinfo select 1,'全国',0
insert into addressinfo select 2,'安徽',1
insert into addressinfo select 3,'福建',1
insert into addressinfo select 4,'甘肃',1
create table firmTypeInfo(id int,name nvarchar(10))
insert into firmTypeInfo select 1,'类型一'
insert into firmTypeInfo select 2,'类型二'
insert into firmTypeInfo select 3,'类型三'
create table firmInfo(Id int,name nvarchar(10),url varchar(20))
insert into firmInfo select 1,'联想','www....'
insert into firmInfo select 1,'三星','www....'
insert into firmInfo select 1,'诺基亚','www....'
create table advertiseInfo(id int,addressId int,firmTypeId int,firmId int)
insert into advertiseinfo select 1,2,1,1
go
select c.地址,c.类型,5-sum(isnull(d.id,0))剩余 from(
select a.id,b.id id1,a.name as 地址,b.name as 类型
from addressinfo a,firmTypeInfo b
)c left join advertiseInfo d on c.id=d.addressId and c.id1=d.firmTypeId
group by c.地址,c.类型
/*
地址 类型 剩余
---------- ---------- -----------
安徽 类型二 5
福建 类型二 5
甘肃 类型二 5
全国 类型二 5
安徽 类型三 5
福建 类型三 5
甘肃 类型三 5
全国 类型三 5
安徽 类型一 4
福建 类型一 5
甘肃 类型一 5
全国 类型一 5
(12 行受影响)
*/
go
drop table addressinfo,firmTypeInfo,firmInfo,advertiseInfo
select
a.name as 地址,
b.name as 类型,
5-count(*) as 剩余广告位
from
addressinfo a ,advertiseinfo b ,firmtypeinfo c
where
a.id=b.addressid
and
c.id=b.firmtypeid
group by
a.name,b.name
select
a.name as 地址,
b.name as 类型,
5-count(*) as 剩余广告位
from addressinfo a inner join advertiseinfo b
on a.id=b.addressid
inner join firmtypeinfo c on c.id=b.firmtypeid
group by a.name,b.name
select a.name as 地址,b.name as 类型,5-count(*) as 剩余广告位
from addressinfo a inner join advertiseinfo on a.id=b.addressid
inner join firmtypeinfo c on c.id=b.firmtypeid
group by a.name,b.name