--建立测试环境
Create table RS_Room
(RoomCode Varchar(10),
EMoney Int)Create table RS_Bed
(BedCode Varchar(10),
RoomCode Varchar(10),
EmpCode Varchar(10),
Flag Bit)
GO
--插入数据
Insert RS_Room Values('001', 600)
Insert RS_Room Values('002', 500)Insert RS_Bed Values('001-01', '001', 'Em001', 1)
Insert RS_Bed Values('001-02', '001', 'Em002', 1)
Insert RS_Bed Values('001-03', '001', 'Em003', 1)
Insert RS_Bed Values('001-04', '001', Null , 0)
Insert RS_Bed Values('002-01', '002', Null , 0)
Insert RS_Bed Values('002-02', '002', 'Em004', 1)
Insert RS_Bed Values('002-03', '002', Null , 0)
Insert RS_Bed Values('002-04', '002', Null , 0)
GO
--测试
Select
EmpCode,
(Select EMoney from RS_Room Where RoomCode=A.RoomCode)/(Select Count(*) from RS_Bed Where RoomCode=A.RoomCode And Flag=1) As 住宿费用
from RS_Bed A Where (EmpCode Is Not Null)
Order By RoomCode
--删除测试环境
Drop table RS_Room,RS_Bed
--结果
/*
RoomCode 住宿费用
Em001 200
Em002 200
Em003 200
Em004 500
*/
Create table RS_Room
(RoomCode Varchar(10),
EMoney Int)Create table RS_Bed
(BedCode Varchar(10),
RoomCode Varchar(10),
EmpCode Varchar(10),
Flag Bit)
GO
--插入数据
Insert RS_Room Values('001', 600)
Insert RS_Room Values('002', 500)Insert RS_Bed Values('001-01', '001', 'Em001', 1)
Insert RS_Bed Values('001-02', '001', 'Em002', 1)
Insert RS_Bed Values('001-03', '001', 'Em003', 1)
Insert RS_Bed Values('001-04', '001', Null , 0)
Insert RS_Bed Values('002-01', '002', Null , 0)
Insert RS_Bed Values('002-02', '002', 'Em004', 1)
Insert RS_Bed Values('002-03', '002', Null , 0)
Insert RS_Bed Values('002-04', '002', Null , 0)
GO
--测试
Select
EmpCode,
(Select EMoney from RS_Room Where RoomCode=A.RoomCode)/(Select Count(*) from RS_Bed Where RoomCode=A.RoomCode And Flag=1) As 住宿费用
from RS_Bed A Where (EmpCode Is Not Null)
Order By RoomCode
--删除测试环境
Drop table RS_Room,RS_Bed
--结果
/*
RoomCode 住宿费用
Em001 200
Em002 200
Em003 200
Em004 500
*/
EmpCode,
(Select EMoney from RS_Room Where RoomCode=A.RoomCode)/(Select Count(*) from RS_Bed Where RoomCode=A.RoomCode And Flag=1) As 住宿费用
from RS_Bed A Where (EmpCode Is Not Null) And EmpCode='Em001'