34,588
社区成员
发帖
与我相关
我的任务
分享
SELECT h.ID,h.RoomID,h.HotelID,h.RoomTypeID,h.RoomTypeName,o.ID,OccupancyID,
o.TeamName,t.DRoom_Price,t.TRoom_Price,t.TRoom_OverTime,ROW_NUMBER() over(order by Occupancyid) as rowno,
RoomNum = STUFF((SELECT ',' + CAST(RoomNum AS NVARCHAR(MAX))
FROM StayHistory WHERE (Occupancyid = h.Occupancyid)
FOR XML PATH ('')),1,1,'')
from StayHistory h,Occupancy o,RoomType t
where h.OccupancyID=o.ID And h.RoomTypeID=t.ID And h.IsCheckOut=0
group by o.ID,h.ID,h.RoomID,h.HotelID,h.RoomTypeID,h.RoomTypeName,OccupancyID,
o.TeamName,t.DRoom_Price,t.TRoom_Price,t.TRoom_OverTime
SELECT o.id,Occupancyid,o.CustomerName,o.CustomerType,o.TeamName,o.CustomerTelphone,o.DiscountName,
o.DiscountOfStandards,o.LeaveTime,o.Deposit,s.StayTime,ROW_NUMBER() over(order by Occupancyid) as rowno,
RoomNum = STUFF((SELECT ',' + CAST(RoomNum AS NVARCHAR(MAX))
FROM StayHistory WHERE (Occupancyid = s.Occupancyid)
FOR XML PATH ('')),1,1,'')
FROM Occupancy o,StayHistory s
WHERE o.id=s.Occupancyid and o.TeamName<>'' and s.IsCheckOut=0
group by o.id,Occupancyid,o.CustomerName,o.CustomerType,o.TeamName,o.CustomerTelphone,o.DiscountName,
o.DiscountOfStandards,o.LeaveTime,o.Deposit,s.StayTime