RTCREATE PROCEDURE P_GetPagedDisplayNotice
(
@startIndex INT,
@endIndex INT
)
AS
SET NOCOUNT ON
DECLARE @indextable TABLE(id int identity(1,1),nid int)
set rowcount @endIndex
insert into @indextable(nid) SELECT A.[ID] FROM [xskj_article] A LEFT JOIN [xskj_forums] B ON [Fid]=B.[ID] WHERE[MainType]=40 order by A.[Posttime] DESC, A.[ID] desc SELECT A.*,C.*,att=case when isnull([filename],'')='' then 'No' else 'Yes' end
FROM( select *,forumname=d.[name] from [xskj_article] c left join xskj_forums d on c.fid=d.[id] where d.maintype=40 ) A
inner join @indextable t on A.[id]=t.[nid] left join(select * from xskj_attachments l where not exists(select * from xskj_attachments where l.aid=aid and l.[id]>[id]))
c on c.[aid]=A.[id]
WHERE t.[id] between @startIndex and @endIndex order by a.posttime desc, a.[id] desc
set nocount off
RETURN
GO
会报个错
提示:“多次为A指定了列'ID'”
(
@startIndex INT,
@endIndex INT
)
AS
SET NOCOUNT ON
DECLARE @indextable TABLE(id int identity(1,1),nid int)
set rowcount @endIndex
insert into @indextable(nid) SELECT A.[ID] FROM [xskj_article] A LEFT JOIN [xskj_forums] B ON [Fid]=B.[ID] WHERE[MainType]=40 order by A.[Posttime] DESC, A.[ID] desc SELECT A.*,C.*,att=case when isnull([filename],'')='' then 'No' else 'Yes' end
FROM( select *,forumname=d.[name] from [xskj_article] c left join xskj_forums d on c.fid=d.[id] where d.maintype=40 ) A
inner join @indextable t on A.[id]=t.[nid] left join(select * from xskj_attachments l where not exists(select * from xskj_attachments where l.aid=aid and l.[id]>[id]))
c on c.[aid]=A.[id]
WHERE t.[id] between @startIndex and @endIndex order by a.posttime desc, a.[id] desc
set nocount off
RETURN
GO
会报个错
提示:“多次为A指定了列'ID'”
xskj_forums 这2个table里是不是都有ID栏位?
造成你的 A 会有2个id
这句中不用* 需要具体指定列
测试下
CREATE PROCEDURE P_GetPagedDisplayNotice
(
@startIndex INT,
@endIndex INT
)
AS
SET NOCOUNT ON
DECLARE @indextable TABLE(id int identity(1,1),nid int)
set rowcount @endIndex
insert into @indextable(nid) SELECT A.[ID] FROM [xskj_article] A LEFT JOIN [xskj_forums] B ON [Fid]=B.[ID] WHERE[MainType]=40 order by A.[Posttime] DESC, A.[ID] desc SELECT A.[Title],A.[ID],A.[Posttime],attID=C.[ID],C.[Filename],C.[name],att=case when isnull([filename],'')='' then 'No' else 'Yes' end
FROM( select *,forumname=d.[name] from [xskj_article] c left join xskj_forums d on c.fid=d.[id] where d.maintype=40 ) A
inner join @indextable t on A.[id]=t.[nid] left join(select * from xskj_attachments l where not exists(select * from xskj_attachments where l.aid=aid and l.[id]>[id]))
c on A.[id]=c.[aid]
WHERE t.[id] between @startIndex and @endIndex order by a.posttime desc, a.[id] desc
set nocount off
RETURN
GO换成这样的还是不行?
(
@startIndex INT,
@endIndex INT
)
AS
SET NOCOUNT ON
DECLARE @indextable TABLE(id int identity(1,1),nid int)
set rowcount @endIndex
insert into @indextable(nid) SELECT A.[ID] FROM [xskj_article] A LEFT JOIN [xskj_forums] B ON [Fid]=B.[ID] WHERE[MainType]=40 order by A.[Posttime] DESC, A.[ID] desc SELECT A.[Title],A.[ID],A.[Posttime],attID=C.[ID],C.[Filename],C.[name],att=case when isnull([filename],'')='' then 'No' else 'Yes' end
FROM( select c.*,forumname=d.[name] from [xskj_article] c left join xskj_forums d on c.fid=d.[id] where d.maintype=40 ) A
inner join @indextable t on A.[id]=t.[nid] left join(select * from xskj_attachments l where not exists(select * from xskj_attachments where l.aid=aid and l.[id]>[id]))
c on A.[id]=c.[aid]
WHERE t.[id] between @startIndex and @endIndex order by a.posttime desc, a.[id] desc
set nocount off
RETURN
GO
首先最外面这里改了下
SELECT A.[Title],A.[ID],A.[Posttime],attID=C.[ID],C.[Filename],C.[name],att=case when isnull([filename],'')='' then 'No' else 'Yes' end
还有这里的子查询
select c.*,forumname=d.[name] from [xskj_article] c left join xskj_forums d on c.fid=d.[id] where d.maintype=40
原来只有*
改成c.*