关于sql表关联有关问题，如有大神告诉小弟我将不胜感激（如果帮小弟我解决后，写下你号码小弟我帮你充10元话费，说到做到）

关于sql表关联问题，如有大神告诉我将不胜感激（如果帮我解决后，写下你号码我帮你充10元话费，说到做到）
本帖最后由 michaeltang123 于 2013-06-02 18:28:10 编辑 现在有两个表，customer和room。customer有id，name，room_id这三个字段。room里有id，roomid，num这三个字段，其中customer里的room_id和room里的id主外键关联。插入数据inser into customer（id，name，room_id） values（1，jack，2），（2，tom，2），（3，lily，4）。insert into room（id，roomid，num）values（2,101,4），（3,102,6）。现在可以看到在表room中id=2中有两条关联数据jack和tom，id=3中有一条关联数据lily。那我怎样对其关联的字段条数进行限制，也就是我如果想在room中id= 2中只能让其在customer中插入4个关联数据怎么做？如果没听明白我说一个顾客租房的例子，上面的表customer就是顾客，room就是房间，顾客表里有id，姓名（name），关联room的id(room_id)。房间表里id，房号(roomid)，床位数(num)。现在当num=4时也就是这个房子只有4个床位其对应的id为2，也就是在customer中room_id=2的顾客不能超过4个，就是要对插入记录进行限制，该怎么实现？（如果大神还能帮我提供思路怎么查出空余的床位的房号更好）  SQL
[解决办法]

use master;
go
if object_id('customer')is not null drop table customer;
if object_id('room')is not null drop table room;
go
create table customer(
id int,
[name] varchar(100),
room_id int
)
create table room(
id int,
roomid int,
num int
)

--其中customer里的room_id和room里的id主外键关联

insert into customer(id,[name],room_id) 
select 1,'jack',2
union all select 2,'tom',2
union all select 3,'lily',4

insert into room(id,roomid,num)
select 2,101,4
union all select 3,102,6

--room中id=2中有两条关联数据jack和tom
--id=3中有一条关联数据lily。
--
--那我怎样对其关联的字段条数进行限制
--也就是我如果想在room中id= 2中只能让其在customer中插入4个关联数据怎么做？

--insert into customer 

;with t as(select 4 id,'mike' [name] ,2 room_id,(select num from room a
where a.id = 2)num)

insert customer(id,[name],room_id)
select id,[name],room_id from t 
where (select t.num - count(1) from customer a
where a.room_id = t.room_id)>0
if(@@rowcount=1)
print '成功'
else
print '客房人数限制'

;with t as(select 5 id,'master' [name] ,2 room_id,(select num from room a
where a.id = 2)num)

insert customer(id,[name],room_id)
select id,[name],room_id from t 
where (select t.num - count(1) from customer a
where a.room_id = t.room_id)>0
if(@@rowcount=1)
print '成功'
else
print '客房人数限制'

;with t as(select 5 id,'ccyou' [name] ,2 room_id,(select num from room a
where a.id = 2)num)

insert customer(id,[name],room_id)
select id,[name],room_id from t 
where (select t.num - count(1) from customer a
where a.room_id = t.room_id)>0
if(@@rowcount=1)
print '成功'
else
print '客房人数限制'

我是采用的存储过程，希望对你有帮助
创建表
--顾客表
create table customer(
id int,
[name] varchar(100), --姓名
room_id int
)
--房屋表
create table room(
id int,
roomid int,   --房号
num int       --床位号 
)

--其中customer里的room_id和room里的id主外键关联
--向表中插入数据
insert into customer values(1,'jack',2)
insert into customer values(2,'tom',2)
insert into customer values(3,'lily',4)

insert into room values(2,101,4)
insert into room values(3,102,6)

创建一个存储过程
create procedure  App_CustomerRoom
(
   @pi_id  int,
   @pi_name varchar(100),
   @pi_room_id  int
)
as
declare @pt_Num int  --该房可以租的房客
declare @pt_count int --已经租的房客
set @pt_Num= (select num from room where id=@pi_room_id)
set @pt_count=(select count(*) from customer where room_id=@pi_room_id)
if(@pt_count>=@pt_Num)
begin
  print '客房已满,不好再租'
end
else
begin
  insert into customer values(@pi_id,@pi_name,@pi_room_id)
   print '租房成功' 
end
--执行存储过程如果可租的房小于已租的
exec App_CustomerRoom  5,'pan1',2
 --执行结果
   -- (1 行受影响)
     --租房成功

--执行存储过程如果可租的房da于已租的
exec App_CustomerRoom  6,'pan2',2
 --执行结果
   -- 客房已满,不好再租

[解决办法]

if object_id('customer')is not null drop table customer;
if object_id('room')is not null drop table room;
go
create table customer(
    id int,
    [name] varchar(100),
    room_id int
)
create table room(
    id int,
    roomid int,
    num int
)


INSERT INTO room 
VALUES
(1,1,3),
(2,2,2)
GO

CREATE TRIGGER trigger_Insert
   ON customer
   AFTER INSERT
AS 
BEGIN
SET NOCOUNT ON;

    IF (SELECT COUNT(*) 
        FROM customer 
        WHERE room_id=(SELECT room_id  FROM inserted))>
        (SELECT num FROM room WHERE roomid=(SELECT room_id  FROM inserted))
    BEGIN
      DELETE dbo.customer 
      FROM dbo.customer,INSERTED
      WHERE dbo.customer.id=inserted.id 
            AND dbo.customer.room_id=inserted.room_id  
            AND dbo.customer.name=inserted.name
      PRINT '客房已满'
    END
   SET NOCOUNT OFF 
END
GO
INSERT INTO dbo.customer
        ( id, name, room_id ) 
 
VALUES  ( 1, -- id - int
          '房客1', -- name - varchar(100)
          1  -- room_id - int
          )
go          
INSERT INTO dbo.customer
        ( id, name, room_id )
VALUES  ( 2, -- id - int
          '房客2', -- name - varchar(100)
          1  -- room_id - int
          )
go          
INSERT INTO dbo.customer
        ( id, name, room_id )
VALUES  ( 3, -- id - int
          '房客3', -- name - varchar(100)
          1  -- room_id - int
          )                    
GO
INSERT INTO dbo.customer
        ( id, name, room_id )
VALUES  ( 4, -- id - int
          '房客4', -- name - varchar(100)
          1  -- room_id - int
          )