举个特殊的例子:
假设有4个教室,每个教室都能坐30人,编号分别为1,2,3,4
以下单位和人数:
A:35人
B:22人
C:7人
D:8人
F:25人
E:23人
如果顺序排列的话:
1号教室:A(30人) 2号教室:A(5人)
2号教室:B(22人)
2号教室:C(3人) 3号教室:C(4人)
3号教室:D(8人)
3号教室:E(18人) 4号教室:E(5人)
4号教室:F(25人)
这个排法中,将A、C、E各出现了2次,也就是被拆分了2次。 那我需要的结果为:
1号教室:A(30人) 2号教室:A(5人)
2号教室:F(25人) 3号教室:B(22人)
3号教室:D(8人) 4号教室:C(7人)
4号教室:E(23人)
而在这个排法中,C和E都没有被拆分,只有A拆分了2次,而A的拆分是必须的。
例子举得比较特殊,但希望能够说明我的需求。
假设有4个教室,每个教室都能坐30人,编号分别为1,2,3,4
以下单位和人数:
A:35人
B:22人
C:7人
D:8人
F:25人
E:23人
如果顺序排列的话:
1号教室:A(30人) 2号教室:A(5人)
2号教室:B(22人)
2号教室:C(3人) 3号教室:C(4人)
3号教室:D(8人)
3号教室:E(18人) 4号教室:E(5人)
4号教室:F(25人)
这个排法中,将A、C、E各出现了2次,也就是被拆分了2次。 那我需要的结果为:
1号教室:A(30人) 2号教室:A(5人)
2号教室:F(25人) 3号教室:B(22人)
3号教室:D(8人) 4号教室:C(7人)
4号教室:E(23人)
而在这个排法中,C和E都没有被拆分,只有A拆分了2次,而A的拆分是必须的。
例子举得比较特殊,但希望能够说明我的需求。
你动作可赶快呀!my god!!!!!不知道数据库能不能搞定。
create table jiaoshi (jid int identity (1,1) ,jsmc varchar(10) ,capacity int )
create table person (pid int identity (1,1) ,dwmc varchar(10) ,personmc varchar(10) )
insert into person
select '税务局','李部如'
Union all
select '税务局','马部如'
Union all
select '税务局','刘部如'
Union all
select '税务局','牛部如'
Union all
select '税务局','猴部如'
Union all
select '税务局','朱部如'
Union all
select '药监局','苟大棚'
Union all
select '税务局','许部如'
Union all
select '药监局','牛大棚'
Union all
select '药监局','朱大棚'
Union all
select '税务局','朱部儒'
Union all
select '税务局','牛部刊'
Union all
select '工商局','任不世'
Union all
select '工商局','苟不利'
insert into jiaoshi
select 'a',5
union all
select 'b',4
union all
select 'c',3
union all
select 'd',4
或者你如果有sqlserver2005技术内幕:T-SQL程序设计,里面有个会议排人数的,和你这个一模一样。
谢谢了!
出版社,作者,出版日期
谢谢!
我把这个会议安排座位的示例给你贴出来:---------------------------------------------------------------------
-- Matching Problems
----------------------------------------------------------------------- Listing 3-7: Code that Creates and Populates the Events and Rooms Tables
USE tempdb;
GO
IF OBJECT_ID('dbo.Events') IS NOT NULL
DROP TABLE dbo.Events;
GO
IF OBJECT_ID('dbo.Rooms') IS NOT NULL
DROP TABLE dbo.Rooms;
GOCREATE TABLE dbo.Rooms
(
roomid VARCHAR(10) NOT NULL PRIMARY KEY,
seats INT NOT NULL
);INSERT INTO dbo.Rooms(roomid, seats) VALUES('C001', 2000);
INSERT INTO dbo.Rooms(roomid, seats) VALUES('B101', 1500);
INSERT INTO dbo.Rooms(roomid, seats) VALUES('B102', 100);
INSERT INTO dbo.Rooms(roomid, seats) VALUES('R103', 40);
INSERT INTO dbo.Rooms(roomid, seats) VALUES('R104', 40);
INSERT INTO dbo.Rooms(roomid, seats) VALUES('B201', 1000);
INSERT INTO dbo.Rooms(roomid, seats) VALUES('R202', 100);
INSERT INTO dbo.Rooms(roomid, seats) VALUES('R203', 50);
INSERT INTO dbo.Rooms(roomid, seats) VALUES('B301', 600);
INSERT INTO dbo.Rooms(roomid, seats) VALUES('R302', 55);
INSERT INTO dbo.Rooms(roomid, seats) VALUES('R303', 55);CREATE TABLE dbo.Events
(
eventid INT NOT NULL PRIMARY KEY,
eventdesc VARCHAR(25) NOT NULL,
attendees INT NOT NULL
);INSERT INTO dbo.Events(eventid, eventdesc, attendees)
VALUES(1, 'Adv T-SQL Seminar', 203);
INSERT INTO dbo.Events(eventid, eventdesc, attendees)
VALUES(2, 'Logic Seminar', 48);
INSERT INTO dbo.Events(eventid, eventdesc, attendees)
VALUES(3, 'DBA Seminar', 212);
INSERT INTO dbo.Events(eventid, eventdesc, attendees)
VALUES(4, 'XML Seminar', 98);
INSERT INTO dbo.Events(eventid, eventdesc, attendees)
VALUES(5, 'Security Seminar', 892);
INSERT INTO dbo.Events(eventid, eventdesc, attendees)
VALUES(6, 'Modeling Seminar', 48);
GOCREATE INDEX idx_att_eid_edesc
ON dbo.Events(attendees, eventid, eventdesc);
CREATE INDEX idx_seats_rid
ON dbo.Rooms(seats, roomid);
GO-- Listing 3-8: Cursor Code for Matching Problem (guaranteed solution)
DECLARE
@roomid AS VARCHAR(10), @seats AS INT,
@eventid AS INT, @attendees AS INT;DECLARE @Result TABLE(roomid VARCHAR(10), eventid INT);DECLARE CRooms CURSOR FAST_FORWARD FOR
SELECT roomid, seats FROM dbo.Rooms
ORDER BY seats, roomid;
DECLARE CEvents CURSOR FAST_FORWARD FOR
SELECT eventid, attendees FROM dbo.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-- First remove rooms where seats > 600
DELETE FROM dbo.Rooms WHERE seats > 600;
GO
R203 2
R302 6
B102 4
B301 1
B201 3
B101 5对这个结果看得不太明白。另外,关于那本书,这是我查到的,有三个版本:1.本-甘; 科拉尔; 萨卡
2.德兰妮; 聂伟; 方磊
3.Itzik; Ben-Gan; 赵立东其中第1本应该是最近才出的,不知道你说的是哪一本?
我的需求是要排到下一间room去的。
create table jiaoshi (jid int identity(1,1),jrenshu int)
create table danwei(did int identity(1,1),dname varchar(10),drenshu int)
insert into jiaoshi values(30)
insert into jiaoshi values(10)
insert into jiaoshi values(20)
insert into jiaoshi values(30)
insert into jiaoshi values(10)
insert into jiaoshi values(10)
insert into jiaoshi values(30)
insert into jiaoshi values(20)
insert into jiaoshi values(10)
insert into jiaoshi values(20)
insert into jiaoshi values(15)
insert into jiaoshi values(20)
insert into jiaoshi values(10)
insert into jiaoshi values(30)insert into danwei values('1001',34)
insert into danwei values('1002',14)
insert into danwei values('1003',23)
insert into danwei values('1004',13)
insert into danwei values('1005',7)
insert into danwei values('1006',27)
insert into danwei values('1007',18)
insert into danwei values('1008',12)
insert into danwei values('1009',22)
insert into danwei values('1010',33)
ALTER proc matchjiaoshi as
create table #dresult(JID INT,DNAME VARCHAR(10),RENSHU INT)
declare @dname varchar(10),@drenshu int,@minjid int,@jrenshu int
declare sss cursor fast_forward for select dname,drenshu from danwei order by dname
open sss
fetch next from sss into @dname,@drenshu
while @@fetch_status=0
begin
if @minjid is null
select @minjid=min(jid) from jiaoshi
while @drenshu>0
begin
if isnull(@jrenshu,0)<=0
select @jrenshu=jrenshu from jiaoshi where jid=@minjid
if @drenshu<=@jrenshu
begin
insert into #dresult select @MINJID,@DNAME,@DRENSHU
set @jrenshu=@jrenshu-@drenshu
SET @DRENSHU=0
end
else
begin
insert into #dresult select @minjid,@dname,@jrenshu
set @drenshu=@drenshu-@jrenshu
set @minjid=@minjid+1
select @jrenshu=jrenshu from jiaoshi where jid=@minjid
end
end fetch next from sss into @dname,@drenshu
end
select * from #dresult
drop table #dresult
不过如果将数据改成我上面兴的特殊例子,最后的结果不是我需要的。
假设有4个教室,每个教室都能坐30人,编号分别为1,2,3,4
以下单位和人数:
A:35人
B:22人
C:7人
D:8人
F:25人
E:23人 改成这个数据后,结果不正确。