一个贪心的算法,该算法查找满足最高出席人数的席位,而且在满足这些出席人数的情况下,成本又最低。(不保证找到房间的解决方案) DECLARE @roomid AS VARCHAR(10), @seats AS INT, @eventid AS INT, @attendees AS INT;DECLARE @Events TABLE(eventid INT, attendees INT); DECLARE @Result TABLE(roomid VARCHAR(10), eventid INT);-- Step 1: Descending DECLARE CRoomsDesc CURSOR FAST_FORWARD FOR SELECT roomid, seats FROM dbo.Rooms ORDER BY seats DESC, roomid DESC; DECLARE CEventsDesc CURSOR FAST_FORWARD FOR SELECT eventid, attendees FROM dbo.Events ORDER BY attendees DESC, eventid DESC;OPEN CRoomsDesc; OPEN CEventsDesc;FETCH NEXT FROM CRoomsDesc INTO @roomid, @seats; WHILE @@FETCH_STATUS = 0 BEGIN FETCH NEXT FROM CEventsDesc INTO @eventid, @attendees; WHILE @@FETCH_STATUS = 0 AND @seats < @attendees FETCH NEXT FROM CEventsDesc INTO @eventid, @attendees; IF @@FETCH_STATUS = 0 INSERT INTO @Events(eventid, attendees) VALUES(@eventid, @attendees); ELSE BREAK; FETCH NEXT FROM CRoomsDesc INTO @roomid, @seats; ENDCLOSE CRoomsDesc; CLOSE CEventsDesc;DEALLOCATE CRoomsDesc; DEALLOCATE CEventsDesc;-- Step 2: Ascending DECLARE CRooms CURSOR FAST_FORWARD FOR SELECT roomid, seats FROM Rooms ORDER BY seats, roomid; DECLARE CEvents CURSOR FAST_FORWARD FOR SELECT eventid, attendees FROM @Events ORDER BY attendees, eventid;OPEN CRooms; OPEN CEvents;FETCH NEXT FROM CEvents INTO @eventid, @attendees; WHILE @@FETCH_STATUS = 0 BEGIN FETCH NEXT FROM CRooms INTO @roomid, @seats; WHILE @@FETCH_STATUS = 0 AND @seats < @attendees FETCH NEXT FROM CRooms INTO @roomid, @seats; IF @@FETCH_STATUS = 0 INSERT INTO @Result(roomid, eventid) VALUES(@roomid, @eventid); ELSE BEGIN RAISERROR('Not enough rooms for events.', 16, 1); BREAK; END FETCH NEXT FROM CEvents INTO @eventid, @attendees; ENDCLOSE CRooms; CLOSE CEvents;DEALLOCATE CRooms; DEALLOCATE CEvents;SELECT roomid, eventid FROM @Result; GO
select 列名1-n from 表名1-N WHERE 条件 order by 1号会议室,2号会议室,3号会议室,日期
DECLARE
@roomid AS VARCHAR(10), @seats AS INT,
@eventid AS INT, @attendees AS INT;DECLARE @Events TABLE(eventid INT, attendees INT);
DECLARE @Result TABLE(roomid VARCHAR(10), eventid INT);-- Step 1: Descending
DECLARE CRoomsDesc CURSOR FAST_FORWARD FOR
SELECT roomid, seats FROM dbo.Rooms
ORDER BY seats DESC, roomid DESC;
DECLARE CEventsDesc CURSOR FAST_FORWARD FOR
SELECT eventid, attendees FROM dbo.Events
ORDER BY attendees DESC, eventid DESC;OPEN CRoomsDesc;
OPEN CEventsDesc;FETCH NEXT FROM CRoomsDesc INTO @roomid, @seats;
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM CEventsDesc INTO @eventid, @attendees; WHILE @@FETCH_STATUS = 0 AND @seats < @attendees
FETCH NEXT FROM CEventsDesc INTO @eventid, @attendees; IF @@FETCH_STATUS = 0
INSERT INTO @Events(eventid, attendees)
VALUES(@eventid, @attendees);
ELSE
BREAK; FETCH NEXT FROM CRoomsDesc INTO @roomid, @seats;
ENDCLOSE CRoomsDesc;
CLOSE CEventsDesc;DEALLOCATE CRoomsDesc;
DEALLOCATE CEventsDesc;-- Step 2: Ascending
DECLARE CRooms CURSOR FAST_FORWARD FOR
SELECT roomid, seats FROM Rooms
ORDER BY seats, roomid;
DECLARE CEvents CURSOR FAST_FORWARD FOR
SELECT eventid, attendees FROM @Events
ORDER BY attendees, eventid;OPEN CRooms;
OPEN CEvents;FETCH NEXT FROM CEvents INTO @eventid, @attendees;
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM CRooms INTO @roomid, @seats; WHILE @@FETCH_STATUS = 0 AND @seats < @attendees
FETCH NEXT FROM CRooms INTO @roomid, @seats; IF @@FETCH_STATUS = 0
INSERT INTO @Result(roomid, eventid) VALUES(@roomid, @eventid);
ELSE
BEGIN
RAISERROR('Not enough rooms for events.', 16, 1);
BREAK;
END FETCH NEXT FROM CEvents INTO @eventid, @attendees;
ENDCLOSE CRooms;
CLOSE CEvents;DEALLOCATE CRooms;
DEALLOCATE CEvents;SELECT roomid, eventid FROM @Result;
GO
FROM MASTER..SPT_VALUES
WHERE TYPE='P'
AND NUMBER BETWEEN 0 AND DATEDIFF(DD,CONVERT(DATETIME,CONVERT(VARCHAR(8),GETDATE(),120)+'1'),DATEADD(DAY,-1,CONVERT(CHAR(8),DATEADD(MONTH,1,GETDATE()),120)+'1'))
/*
FDATE
-----------------------
2009-05-01 00:00:00.000
2009-05-02 00:00:00.000
2009-05-03 00:00:00.000
2009-05-04 00:00:00.000
2009-05-05 00:00:00.000
2009-05-06 00:00:00.000
2009-05-07 00:00:00.000
2009-05-08 00:00:00.000
2009-05-09 00:00:00.000
2009-05-10 00:00:00.000
2009-05-11 00:00:00.000
2009-05-12 00:00:00.000
2009-05-13 00:00:00.000
2009-05-14 00:00:00.000
2009-05-15 00:00:00.000
2009-05-16 00:00:00.000
2009-05-17 00:00:00.000
2009-05-18 00:00:00.000
2009-05-19 00:00:00.000
2009-05-20 00:00:00.000
2009-05-21 00:00:00.000
2009-05-22 00:00:00.000
2009-05-23 00:00:00.000
2009-05-24 00:00:00.000
2009-05-25 00:00:00.000
2009-05-26 00:00:00.000
2009-05-27 00:00:00.000
2009-05-28 00:00:00.000
2009-05-29 00:00:00.000
2009-05-30 00:00:00.000
2009-05-31 00:00:00.000(31 行受影响)
*/
我想就是这么简单.