db_room db_rent_detail db_rent db_clients
－－－－ －－－－－－－－ －－－－－ －－－－－－－－
room_id rent_detail_id rent_id c_id
room_name room_id c_id c_name
room_area rent_id rent_money
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
上面是表结构，想得到的现实结构为
room_id room_area c_name rent_money
010001 111.55 公司一 20000.00
010002 89.33 公司二 20000.00
010003 145.32 null null
010004 68.22 公司四 20000.00
010005 101.22 公司五 20000.00
010006 101.22 null null
010007 101.22 公司七 20000.00
010008 101.22 公司八 20000.00
010009 101.22 公司九 20000.00
010010 101.22 null null
010011 101.22 公司十一 20000.00
010012 101.22 公司十二 20000.00
010013 101.22 null null
010014 101.22 公司十四 20000.00
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
再详细说明一下意思：
需要列出所有房间和该房间的面积，同时如果这个房间已经出租出去，就显示承租该房间的公司名称和租金，如果没有出租就显示为空
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
我觉得这个问题应该用到子语句查询和cube，不知道这个想法对不对，另外我对SQL语句不熟悉，希望哪个高手能写出能够实现上面功能的语句。。。。
6 个解决方案
#1
select a.room_id,a.room_area,d.c_name,c.rent_money
from db_room a
left join db_rent_detail b
on a.room_id=b.room_id
inner join db_rent c
on b.rent_id=c.rent_id
inner join db_clients d
on c.c_id=d.c_id
from db_room a
left join db_rent_detail b
on a.room_id=b.room_id
inner join db_rent c
on b.rent_id=c.rent_id
inner join db_clients d
on c.c_id=d.c_id
#2
select a.room_id,a.room_area
,d.c_name
,c.rent_money
from db_room a
left join db_rent_detail b on a.room_id=b.room_id
left join db_rent c on b.rent_id=c.rent_id
left join db_clients d on c.c_id=d.c_id
,d.c_name
,c.rent_money
from db_room a
left join db_rent_detail b on a.room_id=b.room_id
left join db_rent c on b.rent_id=c.rent_id
left join db_clients d on c.c_id=d.c_id
#3
select db_room .room_id,db_room.room_area, db_clients.c_name ,db_rent.rent_money
from db_room , db_rent_detail , db_rent ,db_clients where db_room.room_id = db_rent_detail.room.id
and db_rent_detail.rent_id=db_rent.rent_id and db_rent.c_id=db_clients.cid
最好用關聯把，等下我試寫個你看看
from db_room , db_rent_detail , db_rent ,db_clients where db_room.room_id = db_rent_detail.room.id
and db_rent_detail.rent_id=db_rent.rent_id and db_rent.c_id=db_clients.cid
最好用關聯把，等下我試寫個你看看
#4
select
a.room_id ,
a.room_area ,
d.c_name ,
c.rent_money
from
db_room a
inner join
db_rent_detail b
on
a.room_id = b.room_id
left join
db_rent c
on
b.rent_id = c.rent_id
inner join
db_clients d
on
c.c_id = c_id
a.room_id ,
a.room_area ,
d.c_name ,
c.rent_money
from
db_room a
inner join
db_rent_detail b
on
a.room_id = b.room_id
left join
db_rent c
on
b.rent_id = c.rent_id
inner join
db_clients d
on
c.c_id = c_id
#5
多谢三位。。。
zjcxc(邹建)的符合要求
xluzhong(打麻将一缺三，咋办？) 只能显示已经出租的
talantlee(團結就是力量) 的也一样。。。
多谢了，现在揭帖
zjcxc(邹建)的符合要求
xluzhong(打麻将一缺三，咋办？) 只能显示已经出租的
talantlee(團結就是力量) 的也一样。。。
多谢了，现在揭帖
#6
select room_id ,room_area,c_name,(case when '出租' then ''
when '出租' then ''
when '出租' then ''
end) as
from db_room a,db_clients b,db_rent c,db_rent_detail d
where a.room_id=b.room and b.rent_id=c.rent_id and c.c_id=d.c_id
同时如果这个房间已经出租出去，
就显示承租该房间的公司名称和租金，
如果没有出租就显示为空
这个完全可以用CASE语句来实现
参考：
select a.Cname as Tcomname,b.Cname as TGoodname,D.nQuanty,c.cNote
, (case when c.iEvaluate='-1' then '差评'
when c.iEvaluate='1' then '好评'
when c.ievaluate='0' then '一般' end) as ievaluate
from Tcompany a,Tgoods b,Appraise c,orders D
where b.ID=D.nProductID and D.Id=c.order_id and a.Id=b.NsaID
and b.NsaID=@TcomID
when '出租' then ''
when '出租' then ''
end) as
from db_room a,db_clients b,db_rent c,db_rent_detail d
where a.room_id=b.room and b.rent_id=c.rent_id and c.c_id=d.c_id
同时如果这个房间已经出租出去，
就显示承租该房间的公司名称和租金，
如果没有出租就显示为空
这个完全可以用CASE语句来实现
参考：
select a.Cname as Tcomname,b.Cname as TGoodname,D.nQuanty,c.cNote
, (case when c.iEvaluate='-1' then '差评'
when c.iEvaluate='1' then '好评'
when c.ievaluate='0' then '一般' end) as ievaluate
from Tcompany a,Tgoods b,Appraise c,orders D
where b.ID=D.nProductID and D.Id=c.order_id and a.Id=b.NsaID
and b.NsaID=@TcomID
#1
select a.room_id,a.room_area,d.c_name,c.rent_money
from db_room a
left join db_rent_detail b
on a.room_id=b.room_id
inner join db_rent c
on b.rent_id=c.rent_id
inner join db_clients d
on c.c_id=d.c_id
from db_room a
left join db_rent_detail b
on a.room_id=b.room_id
inner join db_rent c
on b.rent_id=c.rent_id
inner join db_clients d
on c.c_id=d.c_id
#2
select a.room_id,a.room_area
,d.c_name
,c.rent_money
from db_room a
left join db_rent_detail b on a.room_id=b.room_id
left join db_rent c on b.rent_id=c.rent_id
left join db_clients d on c.c_id=d.c_id
,d.c_name
,c.rent_money
from db_room a
left join db_rent_detail b on a.room_id=b.room_id
left join db_rent c on b.rent_id=c.rent_id
left join db_clients d on c.c_id=d.c_id
#3
select db_room .room_id,db_room.room_area, db_clients.c_name ,db_rent.rent_money
from db_room , db_rent_detail , db_rent ,db_clients where db_room.room_id = db_rent_detail.room.id
and db_rent_detail.rent_id=db_rent.rent_id and db_rent.c_id=db_clients.cid
最好用關聯把，等下我試寫個你看看
from db_room , db_rent_detail , db_rent ,db_clients where db_room.room_id = db_rent_detail.room.id
and db_rent_detail.rent_id=db_rent.rent_id and db_rent.c_id=db_clients.cid
最好用關聯把，等下我試寫個你看看
#4
select
a.room_id ,
a.room_area ,
d.c_name ,
c.rent_money
from
db_room a
inner join
db_rent_detail b
on
a.room_id = b.room_id
left join
db_rent c
on
b.rent_id = c.rent_id
inner join
db_clients d
on
c.c_id = c_id
a.room_id ,
a.room_area ,
d.c_name ,
c.rent_money
from
db_room a
inner join
db_rent_detail b
on
a.room_id = b.room_id
left join
db_rent c
on
b.rent_id = c.rent_id
inner join
db_clients d
on
c.c_id = c_id
#5
多谢三位。。。
zjcxc(邹建)的符合要求
xluzhong(打麻将一缺三，咋办？) 只能显示已经出租的
talantlee(團結就是力量) 的也一样。。。
多谢了，现在揭帖
zjcxc(邹建)的符合要求
xluzhong(打麻将一缺三，咋办？) 只能显示已经出租的
talantlee(團結就是力量) 的也一样。。。
多谢了，现在揭帖
#6
select room_id ,room_area,c_name,(case when '出租' then ''
when '出租' then ''
when '出租' then ''
end) as
from db_room a,db_clients b,db_rent c,db_rent_detail d
where a.room_id=b.room and b.rent_id=c.rent_id and c.c_id=d.c_id
同时如果这个房间已经出租出去，
就显示承租该房间的公司名称和租金，
如果没有出租就显示为空
这个完全可以用CASE语句来实现
参考：
select a.Cname as Tcomname,b.Cname as TGoodname,D.nQuanty,c.cNote
, (case when c.iEvaluate='-1' then '差评'
when c.iEvaluate='1' then '好评'
when c.ievaluate='0' then '一般' end) as ievaluate
from Tcompany a,Tgoods b,Appraise c,orders D
where b.ID=D.nProductID and D.Id=c.order_id and a.Id=b.NsaID
and b.NsaID=@TcomID
when '出租' then ''
when '出租' then ''
end) as
from db_room a,db_clients b,db_rent c,db_rent_detail d
where a.room_id=b.room and b.rent_id=c.rent_id and c.c_id=d.c_id
同时如果这个房间已经出租出去，
就显示承租该房间的公司名称和租金，
如果没有出租就显示为空
这个完全可以用CASE语句来实现
参考：
select a.Cname as Tcomname,b.Cname as TGoodname,D.nQuanty,c.cNote
, (case when c.iEvaluate='-1' then '差评'
when c.iEvaluate='1' then '好评'
when c.ievaluate='0' then '一般' end) as ievaluate
from Tcompany a,Tgoods b,Appraise c,orders D
where b.ID=D.nProductID and D.Id=c.order_id and a.Id=b.NsaID
and b.NsaID=@TcomID