如何写这个sql语句呢？

[yanger601]

我现在有这样的表

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_PORTALME_REFERENCE_PORTALME]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[PortalMenu] DROP CONSTRAINT FK_PORTALME_REFERENCE_PORTALME
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_PORTALME_REFERENCE_PORTALPA]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[PortalMenu] DROP CONSTRAINT FK_PORTALME_REFERENCE_PORTALPA
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_PORTALSI_REFERENCE_PORTALPA]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[PortalSite] DROP CONSTRAINT FK_PORTALSI_REFERENCE_PORTALPA
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_USERPROF_REFERENCE_PORTALPA]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[UserProfile] DROP CONSTRAINT FK_USERPROF_REFERENCE_PORTALPA
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[PortalMenu]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[PortalMenu]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[PortalPage]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[PortalPage]
GO

CREATE TABLE [dbo].[PortalMenu]－－栏目表 (
[Id] [bigint] NOT NULL ,
[PortalSite] [bigint] NULL ,
[Title] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Description] [nvarchar] (500) COLLATE Chinese_PRC_CI_AS NULL ,
[Page] [bigint] NULL ,－－页面
[ParentPortalMenu] [bigint] NULL ,－－父级栏目
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[PortalPage] (－－页面
[Id] [bigint] NOT NULL ,
[PortalRegion] [bigint] NULL ,
[Title] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
我现在有一个需求就是想选择一个栏目，删除该栏目已经它的子栏目和栏目相关的页面。
请问这样的sql怎么写呀！谢谢

[yanger601]

怎么没有人回答呢？是我说不清楚我的问题，还是大家不愿意回答呢？请高手指点，谢谢

[gahade]

ParentPortalMenu和哪个栏位进行关联?PortalSite还是ID?

Page和页面表中的哪个栏位进行关联?

[gahade]

页面表是不是栏目表的从表?

[gahade]

没人?
给你贴了个函数,用递归取下级的,应该可能实现你的需要,栏位不对自己改吧.
--递归取下级
create function f_getchild(@id bigint)
returns @t table(no bigint)
as
begin
declare @t_temp table(id int identity(1,1),child bigint)
insert into @t(no)
select PortalSite from PortalMenu where ParentPortalMenu = @id

insert into @t_temp(child)
select PortalSite from PortalMenu where ParentPortalMenu = @id

declare @child_temp bigint,@max_id int,@min_id int
select @max_id = max(id),@min_id = min(id) from @t_temp
while @min_id <= @max_id
begin
select @child_temp = child from @t_temp where id = @min_id
insert into @t(no)
select * from dbo.f_getchild(@child_temp)
select @min_id = @min_id + 1
end
return
end
--调用
select no from dbo.f_getchild('01')

函数会得到某一栏目下的所有子栏目,这样你删除这个表时关联这个函数,再关联页面表就可以全部删除了!

[yanger601]

有人！谢谢你关注我的问题。前两天放假，所有没有上来看看！

一个栏目可以包含几个子栏目，一个栏目都可以有一个页面。
ParentPortalMenu和ID直接关联，就是引用自己。
Page字段是引用portalPage的Id。
再次感谢