--19647 198 19647 UpLoadFiles/Doc/398656853104902.doc 2010-02-01 17:39:08.657 程序员测试题目
--19647 197 19647 UpLoadFiles/Doc/398656762743851.doc 2010-02-01 17:39:08.657 _IT测试题】
如上面语句
aid 这个字段数据重复了
为19647
怎么去重复。
?
并且只有AID重复
其余都不重复。
所以其余的还是需要取出来?
怎么实现?
调试欢乐多
SELECT * FROM xskj_attachments T WHERE aid=(SELECT MAX(aid) FROM xskj_attachments WHERE AID=T.AID)这条语句执行完之后数据还是重复。--194 19645 UpLoadFiles/Doc/3042890985979332.doc 2010-02-01 17:30:42.890 程序员测试题目
195 19646 UpLoadFiles/Doc/380406235050971.doc 2010-02-01 17:38:00.470 _IT测试题
196 19646 UpLoadFiles/Doc/38046832455202.doc 2010-02-01 17:38:00.470 需求资料
197 19647 UpLoadFiles/Doc/398656762743851.doc 2010-02-01 17:39:08.657 _IT测试题
198 19647 UpLoadFiles/Doc/398656853104902.doc 2010-02-01 17:39:08.657 程序员测试题目
-- Author :SQL77(只为思齐老)
-- Date :2010-02-02 08:36:02
-- Version:
-- Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
-- Aug 6 2000 00:57:48
-- Copyright (c) 1988-2000 Microsoft Corporation
-- Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:#TB
if object_id('tempdb.dbo.#TB') is not null drop table #TB
go
create table #TB([A] int,[C2] int,[C3] varchar(35),[C4] datetime,[C5] varchar(14))
insert #TB
select 195,19646,'UpLoadFiles/Doc/380406235050971.doc','2010-02-01 17:38:00.470','_IT测试题' union all
select 196,19646,'UpLoadFiles/Doc/38046832455202.doc','2010-02-01 17:38:00.470','需求资料' union all
select 197,19647,'UpLoadFiles/Doc/398656762743851.doc','2010-02-01 17:39:08.657','_IT测试题' union all
select 198,19647,'UpLoadFiles/Doc/398656853104902.doc','2010-02-01 17:39:08.657','程序员测试题目'
--------------开始查询--------------------------select * from #TB T WHERE A=(SELECT MAX(A) FROM #TB WHERE C2=T.C2)select * from #TB T WHERE A=(SELECT MIN(A) FROM #TB WHERE C2=T.C2)
----------------结果----------------------------
/* (所影响的行数为 4 行)A C2 C3 C4 C5
----------- ----------- ----------------------------------- ------------------------------------------------------ --------------
198 19647 UpLoadFiles/Doc/398656853104902.doc 2010-02-01 17:39:08.657 程序员测试题目
196 19646 UpLoadFiles/Doc/38046832455202.doc 2010-02-01 17:38:00.470 需求资料(所影响的行数为 2 行)A C2 C3 C4 C5
----------- ----------- ----------------------------------- ------------------------------------------------------ --------------
195 19646 UpLoadFiles/Doc/380406235050971.doc 2010-02-01 17:38:00.470 _IT测试题
197 19647 UpLoadFiles/Doc/398656762743851.doc 2010-02-01 17:39:08.657 _IT测试题(所影响的行数为 2 行)
*/
select * from tb t where col=(select max(col) from tb where aid=t.aid)
--2、
select * from tb t where col=(select min(col) from tb where aid=t.aid)
--3、
select * from tb b where not exists (select * from tb where b.aid=aid and b.col>col)
go
create table #TB([A] int,[C2] int,[C3] varchar(35),[C4] datetime,[C5] varchar(14))
insert #TB
select 195,19646,'UpLoadFiles/Doc/380406235050971.doc','2010-02-01 17:38:00.470','_IT测试题' union all
select 196,19646,'UpLoadFiles/Doc/38046832455202.doc','2010-02-01 17:38:00.470','需求资料' union all
select 197,19647,'UpLoadFiles/Doc/398656762743851.doc','2010-02-01 17:39:08.657','_IT测试题' union all
select 198,19647,'UpLoadFiles/Doc/398656853104902.doc','2010-02-01 17:39:08.657','程序员测试题目'
select * from #TB b where not exists (select * from #TB where b.[C2]=[C2] and b.[A]>[A])
A C2 C3 C4 C5
----------- ----------- ----------------------------------- ----------------------- --------------
195 19646 UpLoadFiles/Doc/380406235050971.doc 2010-02-01 17:38:00.470 _IT测试题
197 19647 UpLoadFiles/Doc/398656762743851.doc 2010-02-01 17:39:08.657 _IT测试题(2 行受影响)
一张文章表,
一张附件表
一张文章表里可能会有多个附件,
然后对应到数据库的结果为下面所示select * from xskj_article where [id]=19647--数据为:19647 fdsfdsfs <p>fdsfds</p> Null 2010-02-01 17:39:08.657 31 NULL NULL NULL
这个文章添加了两个附件,
附件表的数据为select * from xskj_attachments where [aid]=19647--197 19647 UpLoadFiles/Doc/398656762743851.doc 2010-02-01 17:39:08.657 _IT测试题
--198 19647 UpLoadFiles/Doc/398656853104902.doc 2010-02-01 17:39:08.657 程序员测试题目
一个是文章表的ID
囧~!
我两个都写了一样对吧
是通过传递aid到另一个页面的
所以不管附件也没关系
要是真要管附件估计更难办了因为你给出来的语句只会显示一个附件
那么写sql的时候
not exists (select * from #TB where b.[C2]=[C2] and b.[A]>[A])--》A就是文章的ID,C2是附建的ID
数据:197 19647 UpLoadFiles/Doc/398656762743851.doc 2010-02-01 17:39:08.657 _IT测试题
198 19647 UpLoadFiles/Doc/398656853104902.doc 2010-02-01 17:39:08.657 程序员测试题目
A就是附建的ID,C2是文章的ID
aid是文章ID
id是附件表的ID
可是出来的数据就是这样的
又写错啦select * from xskj_attachments b where not exists(select * from xskj_attachments where b.aid=aid and b.[id]>[id])-->后面的b.[id]还成[id]
not exists(select * from xskj_attachments where b.aid=aid and b.[id]>b.[id])上面的是你的后面的b.id>b.id是错的
换成b.id>id
就可以啦
你是不是评论博客太多了。
这都 一直搞错。b.id是子查询里面的id
而id是外面那个查询的ID对吧
DECLARE @indextable TABLE(id int identity(1,1),nid int)
set rowcount @endIndex
insert into @indextable(nid) SELECT [ID] FROM [xskj_article] WHERE[Fid]=33 order by [Posttime] DESC,[ID] DESCSELECT A.[ID], A.[Title], A.[Content], A.[Posttime], A.[Image], A.[views],C.*,
att=case when isnull([filename],'') = '' then 'No' else 'Yes' end
FROM [xskj_article] A inner join @indextable t on A.[id]=t.[nid]
left join xskj_attachments C on c.[aid]=a.[id]
WHERE not exists(select * from attachments t where t.aid=C.aid and t.[id]>C.[id]) and A.[Fid]=33 and t.[id] between @startIndex and @endIndex order by a.posttime desc, a.[id] desc看看这么写对不对
放到程序里就出错了。
存储过程没错。
可是在asp.net中运行就报错了
而且是莫名的错误。
我看下
我还在看。好像是语句写错了
是xskj_attachments
但是where 里面写的是attachmennts
sql里面执行是不是你想要的结果啊
CREATE PROCEDURE P_GetPagedPatentDeclarate
(
@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 [ID] FROM [xskj_article] WHERE[Fid]=33 order by [Posttime] DESC,[ID] DESCSELECT A.[ID], A.[Title], A.[Content], A.[Posttime], A.[Image], A.[views],C.*,
att=case when isnull([filename],'') = '' then 'No' else 'Yes' end
FROM [xskj_article] A inner join @indextable t on A.[id]=t.[nid]
left join xskj_attachments C on c.[aid]=a.[id]
WHERE not exists(select * from xskj_attachments t where t.aid=t.aid and t.[id]>C.[id]) and A.[Fid]=33 and t.[id] between @startIndex and @endIndex order by a.posttime desc, a.[id] desc
set nocount off
RETURN
GO
上面是存储过程
asp.net中就一个repeater,一个分页控件aspnetpager
然后用存储过程绑定
后台代码贴下 void bindData()
{
myrepeater.DataSource = SqlHelper.ExecuteReader(SqlStr.xskjConnectString, CommandType.StoredProcedure, "P_GetPagedPatentDeclarate",
new SqlParameter("@startIndex", AspNetPager1.StartRecordIndex),
new SqlParameter("@endIndex", AspNetPager1.EndRecordIndex));
myrepeater.DataBind(); } protected void AspNetPager1_PageChanged(object sender, EventArgs e)
{
bindData();
} if (!IsPostBack)
{
int total = (int)SqlHelper.ExecuteScalar(SqlStr.xskjConnectString, CommandType.StoredProcedure, "P_GetPatentDeclarateNumber");
AspNetPager1.RecordCount = total;
}
我认为是sql没写对
囧。帮偶看看吧,
这语句超过这么多,偶看的就头痛了
简化一下就是这样的语句SELECT A.[ID], A.[Title], A.[Content], A.[Posttime], A.[Image], A.[views],C.*,
att=case when isnull([filename],'') = '' then 'No' else 'Yes' end
FROM [xskj_article] A
left join xskj_attachments C on c.[aid]=a.[id]
WHERE not exists(select * from xskj_attachments t where t.aid=t.aid and t.[id]>C.[id]) and A.[Fid]=33 order by a.posttime desc, a.[id] desc一条数据都没有。这样
应该没错啊、
att=case when isnull([filename],'') = '' then 'No' else 'Yes' end
FROM
(
select * from [xskj_article] where [Fid]=33
)
A
left join
(
select * from xskj_attachments t where t.aid=t.aid and t.[id]>C.[id]
)
C
on c.[aid]=a.[id]
order by a.posttime desc, a.[id] desc
xskj_article里面没有fid=33的数据
可是我没有把那些改过的语句放进去之前
存储过程这个条件fid=33也有的
但前面都有数据出来。
如图
att=case when isnull([filename],'') = '' then 'No' else 'Yes' end
FROM
(
select * from [xskj_article] where [Fid]=33
)
A
left join
(
select * from xskj_attachments t where not exists(select * from xskj_attachments where t.aid=aid and t.[id]>[id])
)
C
on c.[aid]=a.[id]
order by a.posttime desc, a.[id] desc
48楼的
看看
我先撤啦
有事情call 我
可是显示数量不一样
囧~!
asp.net中只会显示最后插入的一条记录
如图就如这张图
目前不是有几条记录吗?
但是我现在新增加的话
就只会显示一条
,而且新增一条,就后面新增的把前面的覆盖了
CREATE PROCEDURE P_GetPagedPatentDeclarate
(
@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 [ID] FROM [xskj_article] WHERE[Fid]=33 order by [Posttime] DESC,[ID] DESCSELECT A.[ID], A.[Title], A.[Content], A.[Posttime], A.[Image], A.[views],C.*,
att=case when isnull([filename],'') = '' then 'No' else 'Yes' end
FROM [xskj_article] A inner join @indextable t on A.[id]=t.[nid]
left join xskj_attachments C on c.[aid]=a.[id]
WHERE not exists(select * from xskj_attachments l where l.aid=l.aid and l.[id]>C.[id]) and A.[Fid]=33 and t.[id] between @startIndex and @endIndex order by a.posttime desc, a.[id] desc
set nocount off
RETURN
GO你就看这个存储过程,
数据传的是参数
aspnetpager的开始记录数和末尾记录数
startindex endindex
就算我现在在查询分析器里执行这个存储过程的话
还是只会显示一条
exec P_GetPagedPatentDeclarate 1,12
CREATE PROCEDURE P_GetPagedPatentDeclarate
(
@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 [ID] FROM [xskj_article] WHERE[Fid]=33 order by [Posttime] DESC,[ID] DESCSELECT A.[ID], A.[Title], A.[Content], A.[Posttime], A.[Image], A.[views],C.*,
att=case when isnull([filename],'') = '' then 'No' else 'Yes' end
FROM [xskj_article] A inner join @indextable t on A.[id]=t.[nid]
left join xskj_attachments C on c.[aid]=a.[id]
WHERE not exists(select * from xskj_attachments l where l.aid=l.aid and l.[id]>C.[id]) and A.[Fid]=33 and t.[id] between @startIndex and @endIndex order by a.posttime desc, a.[id] desc
set nocount off
RETURN
GO
和这个有啥区别?
PS:没关系,我马甲多着
别说两百
嘿嘿。两K估计都行。。
(
@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 [ID] FROM [xskj_article] WHERE[Fid]=33 order by [Posttime] DESC,[ID] DESCSELECT A.[ID], A.[Title], A.[Content], A.[Posttime], A.[Image], A.[views],C.*,
att=case when isnull([filename],'') = '' then 'No' else 'Yes' end
FROM( SELECT * FROM [xskj_article] WHERE [Fid]=33) 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
可是我刚刚那样也没错啊
为什么就是不行