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
结果
540 48 1810 6 豪华单人房 300 300 600.00 100.00 15 7 3002
541 51 1810 7 豪华双人房 301 301 700.00 100.00 15 8 4001
542 53 1810 7 豪华双人房 302 302 700.00 100.00 15 9 4002
557 10 1810 4 标准单人房 312 312 123 400.00 100.00 15 10 1008,1009,1002
558 11 1810 4 标准单人房 312 312 123 400.00 100.00 15 11 1008,1009,1002
559 4 1810 4 标准单人房 312 312 123 400.00 100.00 15 12 1008,1009,1002
为什么Group By 没有效果,求大神在线等,急..~