现有表LineInfo
id userid(信息发布者编号) startcity(起点城市) endcity(目的城市) ……(其余字段略)
1 1 上海 北京2 1 上海 沈阳3 1 上海 北京4 2 湖南 湖北5 2 湖南 河南 6 2 湖南 河南7 3 山东 黑龙江8 3 山东 黑龙江
查询要求:将相同的人发布的相同的记录过滤为一条,然后汇总所有人所发的记录,确保同一个人所发布的记录都不相同
id userid(信息发布者编号) startcity(起点城市) endcity(目的城市) ……(其余字段略)
1 1 上海 北京2 1 上海 沈阳3 1 上海 北京4 2 湖南 湖北5 2 湖南 河南 6 2 湖南 河南7 3 山东 黑龙江8 3 山东 黑龙江
查询要求:将相同的人发布的相同的记录过滤为一条,然后汇总所有人所发的记录,确保同一个人所发布的记录都不相同
*,
cnt = (SELECT COUNT(*) FROM tb
WHERE A.userid = userid
AND startcity = A.startcity
AND endcity = A.endcity)
FROM tb AS A
WHERE NOT EXISTS(SELECT * FROM tb
WHERE userid = A.userid
AND startcity = A.startcity
AND endcity = A.endcity
AND id < A.id)
userid as '信息发布者编号',
startcity as '起点城市,
endcity as '目的城市',
……(其余字段略)
from tb_name
id userid(信息发布者编号) startcity(起点城市) endcity(目的城市)
1 1 上海 北京 2 1 上海 沈阳 4 2 湖南 湖北 5 2 湖南 河南 7 3 山东 黑龙江
from LineInfo
要ID干嘛
select t.* from LineInfo t where id = (select max(id) from LineInfo where userid = t.userid and startcity = t.startcity and endcity = t.endcity)
select t.* from LineInfo t where not exists (select min(id) from LineInfo where userid = t.userid and startcity = t.startcity and endcity = t.endcity and id < t.id)
select t.* from LineInfo t where not exists (select min(id) from LineInfo where userid = t.userid and startcity = t.startcity and endcity = t.endcity and id > t.id)
where not exists(select 1 from lineinfo
where userid=a.userid and startcity =a.startcity and endcity=a.endcity
and id>a.id)
http://topic.csdn.net/u/20080123/18/9731d130-0d4b-4c11-8d89-f2c3ca331f0c.html
select t.* from LineInfo t where id = (select max(id) from LineInfo where userid = t.userid and startcity = t.startcity and endcity = t.endcity)
select t.* from LineInfo t where not exists (select 1 from LineInfo where userid = t.userid and startcity = t.startcity and endcity = t.endcity and id < t.id)
select t.* from LineInfo t where not exists (select 1 from LineInfo where userid = t.userid and startcity = t.startcity and endcity = t.endcity and id > t.id)
(
---------页面搜索参数及分页参数定义--------------
----每页显示条数
@PageSize int,
----要显示的页码
@PageIndex int,
----标题
@Title nvarchar(500),
----启运城市
@sendCity nvarchar(200),
----到达城市
@EndCity nvarchar(200),
----运输类型
@SendType nvarchar(100),
----公司名称
@CompanyName nvarchar(100),
----用户所在地
@UserCity nvarchar(200)
)
asBEGIN
set nocount on
----要读取的数据总数
declare @AllCount int
----要显示的页数
declare @pageCount int
----当前页所显示数据的最大索引
declare @PageLowerBound int
----当前页所显示数据的最小索引
declare @PageUpperBound int
----每页显示条数
declare @Actual int declare @biaoti nvarchar(200)
declare @qiyunCity nvarchar(200)
declare @mudiCity nvarchar(200)
declare @yunshuleixing nvarchar(200)
declare @gongsimingcheng nvarchar(300)
declare @suozaidi nvarchar(200)
declare @sql nvarchar(1000)
declare @sql1 nvarchar(1000)
declare @sql2 nvarchar(1000)
declare @sql3 nvarchar(1000)
--------将WHERE条件存入变量,当条件为空时,减少WHERE条件判断,优化搜索速度
----------设置标题判断内容------------
if @title!=''
set @biaoti =' and title like ''%'+@title+'%'' '
else
set @biaoti=''
----------设置启运城市判断内容------------
if @sendCity!=''
set @qiyunCity = ' and (sendtitle like ''%'+@sendCity+'%'' or sendparent like ''%'+@sendCity+'%'' or sendpro like ''%'+@sendCity+'%'') '
else
set @qiyuncity = ''
----------设置到达城市判断内容------------
if @EndCity!=''
set @mudiCity =' and arrivecity like ''%'+@EndCity+'%'' '
else
set @mudiCity =''
----------设置运输类型判断内容------------
if (@SendType!=''and @SendType!='%')
set @yunshuleixing =' and sendtypeid like '''+@SendType+''' '
else
set @yunshuleixing =''
----------设置公司名称判断内容------------
if (@CompanyName!='%' and @CompanyName!='%%')
set @gongsimingcheng = ' and companyname like '''+@CompanyName+''' '
else
set @gongsimingcheng = ''
----------设置用户所在地判断内容------------
if (@UserCity!=''and @UserCity!='%' )
set @suozaidi = 'and (companycity like '''+@usercity+''' or companyproid like '''+@usercity+''') '
else
set @suozaidi =''
-------------------------------------------------------------
----------------------------------------获取记录总数-------------------------------
-------------------------------------------------------------------------------
---------主页及陆运主页显示数据条数自定且无需分页,若分页,则参数@pageSize为15-------------
IF(@pageSize<>15)
BEGIN
-----以@pageSize为参数,决定要显示的数目
set @sql='select top '+str(@pageSize)+' id,userid, title,sendtitle,sendparent,sendpro,arrivecity,typetitle,cartype,carnum,carlength,carload,companyname,starttime
from RoadCarView where 1=1 '+@gongsimingcheng+@suozaidi+' and id in(select id from roadcardistinct r where id=(select max(id)
from roadcardistinct where USERID=r.USERID )) order by starttime desc '-- order by sort desc, starttime desc--(此排序为视图中排序)
exec(@sql)
END
------------------二级页面,固定每页分为15条,只有当@pageSize值为15时才会执行以下分页代码-------------------------------
ELSE
BEGIN------------------------------------------
--------建临时表-----
create table #roadCarselectpage
(id int identity(1,1),nid int)
-------将按条件搜索后的数据按sort字段及starttime字段倒序排序之后将ID存入临时表nid字段,临时表id自增,
set @sql3 ='
insert into #roadCarselectpage
(nid )
select
rd.id
from RoadCarView rd where 1=1 '+@biaoti+@qiyuncity+@mudicity+@yunshuleixing+@gongsimingcheng+@suozaidi+''--加入条件判断,变量内容为‘and ....’或‘(空)’
exec(@sql3)-------------------将T-SQL转换为字符串,动态加载WHERE条件----------
----------------rd.status <>99 and ustatus <>99,此条件已在视图中
----------------获取数据结果集总数----------------
set @sql1 ='
select @AllCount1=count(rd.Id) from #roadCarselectpage rd 'exec sp_executesql @sql1, N'@AllCount1 int output', @AllCount output
--select * from #roadCarselectpage------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--取得分页总数
---计算总页码,当前页码,当前页最大索引及最小索引
set @pageCount=(@AllCount+@pageSize-1)/@PageSize
/*当前页大于总页数 取最后一页*/
if(@PageIndex>@PageCount)
set @PageIndex=@PageCount;
set @PageLowerBound=(@PageIndex-1)*@PageSize;
set @PageUpperBound=@PageLowerBound+@PageSize;
set rowcount @PageUpperBound --确定返回的数量 ----------------------------------------------------------------------------------------------------------------------------------------------------------
--insert into @indextable(nid) select nid from @tmpTable group by nid order by nid desc
------获取当前页显示条数
--select @Actual=count(nid) from #roadCarselectpage t where t.id<=@PageUpperbound and T.id>@PageLowerBound
--获取当页所显示的数据条数
set @Actual=15
if(@PageIndex=@pageCount)
set @Actual=@AllCount%@PageSize---最终查询,输出用户所需结果
set @sql2= '
select companycity, rd.id,rd.title,rd.typetitle,sort,cartype,sourcetype,arrivecity,starttime,sendtitle,sendparent,sendpro,typetitle,carnum,carlength,carload,loginname,userid,ucode, companyname,contact,
@Actual1 as Actual,@PageCount1 as PageCount, @AllCount1 as DataCount
from #roadCarselectpage t,RoadCarView rd where rd.id=t.nid and
t.id<='+str(@PageUpperbound)+' and t.id>'+str(@PageLowerBound)+' '+@biaoti+@qiyuncity+@mudicity+@yunshuleixing+@gongsimingcheng+@suozaidi+
' 'execute sp_executesql @sql2,
N'@Actual1 int,@PageCount1 int,@AllCount1 int',
@Actual1 = @Actual,@PageCount1 = @PageCount,@AllCount1 = @AllCount
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
END
set nocount off;
END
GO