企业表产品表关键字订购表
一个企业可以发布多个产品,
企业可以向我们订购对应产品的关键字====================================================
企业表 companyid companyName
1 A
2 B
3 C
4 D产品表
prodid companyid prodName prodImg prodOtherInfo
1 1 XX产品 a.img 其他信息
2 1 RR产品 b.img 其他信息
3 33 QQ产品 u.img 其他信息
4 2 yy产品 p.img 其他信息订购排序表
orderid AdsKey order companyid
1 xx 1 1
2 xx 2 1
3 产品 1 3
==========================================================现在当客户搜索xx产品的时候。
SQL要先判断是否xx关键字有被订购,有的话数据集中上面部分先查出含有被订购xx对应企业的产品信息。下面部分再查没有在订购表中被订阅
但产品表中标题符合 like '%xx%'的产品信息。
有个要求是查出来的最后结果集要求每家企业只能显示一条产品好难啊。
想了快一个礼拜。试了非常多方案。没有灵感。现把查询大大简化成上面主要逻辑,期待各位给些帮助或方案~~~~
一个企业可以发布多个产品,
企业可以向我们订购对应产品的关键字====================================================
企业表 companyid companyName
1 A
2 B
3 C
4 D产品表
prodid companyid prodName prodImg prodOtherInfo
1 1 XX产品 a.img 其他信息
2 1 RR产品 b.img 其他信息
3 33 QQ产品 u.img 其他信息
4 2 yy产品 p.img 其他信息订购排序表
orderid AdsKey order companyid
1 xx 1 1
2 xx 2 1
3 产品 1 3
==========================================================现在当客户搜索xx产品的时候。
SQL要先判断是否xx关键字有被订购,有的话数据集中上面部分先查出含有被订购xx对应企业的产品信息。下面部分再查没有在订购表中被订阅
但产品表中标题符合 like '%xx%'的产品信息。
有个要求是查出来的最后结果集要求每家企业只能显示一条产品好难啊。
想了快一个礼拜。试了非常多方案。没有灵感。现把查询大大简化成上面主要逻辑,期待各位给些帮助或方案~~~~
解决方案 »
- select distinct 疑问
- 从哪里查看安装的sql server2005 是开发版 还是企业版 ?
- 【求SQL】关于应用case when then else的
- 求一条SQL语句, 一个根据一个表中的某个字段来更新另一个表
- 请问 win2003 sql2000内存使用率的问题
- 高分sql sever 2000 课件(数据库管理或维护方面也可以)。给出下载地址也给分。
- 明天明天测试同能,急需一存储过程!!
- 显示列的问题~
- SQL2008 Management Studio 不能筛选。。。。无法为该请求检索数据。 (Microsoft.SqlServer.Management.Sdk.Sfc)
- 问个SQL语句
- 關於SQL追蹤的問題
- 字母的大小写区分
谁要是觉得解决了本帖也可回复
http://topic.csdn.net/u/20080627/11/5b24077f-f82e-487d-9b39-4a3c064add96.html
本人将给双份分
另外还有一个帖子。也是100分。不想那么浪费,是关于服务器防火墙选择问题。大家也可以知道的回复下
http://topic.csdn.net/u/20080702/10/ca021a67-bd5d-4869-a313-aa0157cfafa0.html
妈的,最近老是在花学本交流东西。大家对分好像都没什么感觉了。汗,CSDN人气有点下降
-- 这样?
-->生成测试数据
declare @企业表 table([companyid] int,[companyName] nvarchar(1))
Insert @企业表
select 1,N'A' union all
select 2,N'B' union all
select 3,N'C' union all
select 4,N'D'
--Select * from @企业表
declare @产品表 table([prodid] int,[companyid] int,[prodName] nvarchar(4),[prodImg] nvarchar(5),[prodOtherInfo] nvarchar(4))
Insert @产品表
select 1,1,N'XX产品',N'a.img',N'其他信息' union all
select 2,1,N'RR产品',N'b.img',N'其他信息' union all
select 3,33,N'QQ产品',N'u.img',N'其他信息' union all
select 4,2,N'yy产品',N'p.img',N'其他信息'
--Select * from @产品表
declare @订购排序表 table([orderid] int,[AdsKey] nvarchar(2),[order] int,[companyid] int)
Insert @订购排序表
select 1,N'xx',1,1 union all
select 2,N'xx',2,1 union all
select 3,N'产品',1,3
--Select * from @订购排序表SELECT distinct [companyName], isnull(d.[AdsKey],p.[prodName]) as '产品' FROM @企业表 E
left join @订购排序表 D on d.[companyid] = e.[companyid]
left join @产品表 P on p.[companyid] = e.[companyid]
where charindex(isnull(d.[AdsKey],p.[prodName]) ,'xx')>0
/*
companyName 产品
----------- ----
A xx
*/
但产品表中标题符合 like '%xx%'的产品信息。
有个要求是查出来的最后结果集要求每家企业只能显示一条产品好难啊。是不是被订购了就只在上面部分显示这一条,下面部分不显示?
当搜索产品的时候,如果我输入框输入 一家企业的名称,那么检索出来的是该企业的所有产品信息,此时产品才可以重复。
http://search.china.alibaba.com/search/offer_search.htm?keywords=%B6%AB%DD%B8%CA%D0%C6%F4%B5%CF%CE%E5%BD%F0%D6%C6%C6%B7当搜索产品的时候,如果我输入框输入的是产品关键字,那么检索出来的是先按照关键字订购表的订购顺序排列。然后再排产品表中符合产品名称 like '%关键字%'.但要求检索出来的最后结果一个企业只能显示一条。
http://search.china.alibaba.com/search/offer_search.htm?keywords=%BE%E2%C6%AC
2楼的行不行啊?
查出的结果是:
必须不在那个关键字订购表内(<>关键字)但符合产品标题like '%关键字%'
同时一个企业只能显示一个
2.我输入一个企业名称进行查询
查出的结果是全部该企业发布的产品信息。
注意:1 2 查询我不清楚到底输入的是哪类型的查询方式如果可以写出这两个的结果集。我问题应该就可以很快解决了
Insert @企业表
select 1,N'A' union all
select 2,N'B' union all
select 3,N'C' union all
select 4,N'D'
--Select * from @企业表
declare @产品表 table([prodid] int,[companyid] int,[prodName] nvarchar(4),[prodImg] nvarchar(5),[prodOtherInfo] nvarchar(4))
Insert @产品表
select 1,1,N'XX产品',N'a.img',N'其他信息' union all
select 2,1,N'RR产品',N'b.img',N'其他信息' union all
select 3,3,N'QQ产品',N'u.img',N'其他信息' union all
select 4,2,N'yy产品',N'p.img',N'其他信息'
--Select * from @产品表
declare @订购排序表 table([orderid] int,[AdsKey] nvarchar(2),[order] int,[companyid] int)
Insert @订购排序表
select 1,N'xx',1,1 union all
select 2,N'xx',2,1 union all
select 3,N'产品',1,3
--Select * from @订购排序表--订购表查找
SELECT [companyName],p.prodName as '产品' FROM @企业表 e
join @产品表 P on p.[companyid] = e.[companyid]
where exists (select 1 from @订购排序表 m where charindex([AdsKey],'xx')>0 and companyid=e.companyid)
and not exists (select 1 from @产品表 where companyid=e.companyid and prodid>p.prodid)
union all
--处理除这个之外的
SELECT [companyName],prodName as '产品' FROM @企业表 e
join @产品表 P on p.[companyid] = e.[companyid]
where charindex([prodName],'xx')>0 and
not exists (select 1 from @订购排序表 m where charindex([AdsKey],'xx')>0 and companyid=e.companyid)
and not exists (select 1 from @产品表 where companyid=e.companyid and prodid>p.prodid)A RR产品
declare @企业表 table([companyid] int,[companyName] nvarchar(1))
Insert @企业表
select 1,N'A' union all
select 2,N'B' union all
select 3,N'C' union all
select 4,N'D'
--Select * from @企业表
declare @产品表 table([prodid] int,[companyid] int,[prodName] nvarchar(4),[prodImg] nvarchar(5),[prodOtherInfo] nvarchar(4))
Insert @产品表
select 1,1,N'XX产品',N'a.img',N'其他信息' union all
select 2,1,N'RR产品',N'b.img',N'其他信息' union all
select 3,3,N'QQ产品',N'u.img',N'其他信息' union all
select 4,2,N'yy产品',N'p.img',N'其他信息'
--Select * from @产品表
declare @订购排序表 table([orderid] int,[AdsKey] nvarchar(2),[order] int,[companyid] int)
Insert @订购排序表
select 1,N'xx',1,1 union all
select 2,N'xx',2,1 union all
select 3,N'产品',1,3
--Select * from @订购排序表--订购表查找
SELECT [companyName],p.prodName as '产品' FROM @企业表 e
join @产品表 P on p.[companyid] = e.[companyid]
where exists (select 1 from @订购排序表 m where charindex([AdsKey],'xx')>0 and companyid=e.companyid)
and not exists (select 1 from @产品表 where companyid=e.companyid and prodid>p.prodid)
union all
--处理除这个之外的
SELECT [companyName],prodName as '产品' FROM @企业表 e
join @产品表 P on p.[companyid] = e.[companyid]
where charindex([prodName],'xx')>0 and
not exists (select 1 from @订购排序表 m where charindex([AdsKey],'xx')>0 and companyid=e.companyid)
and not exists (select 1 from @产品表 where companyid=e.companyid and prodid>p.prodid)
A RR产品
==============
非常感谢。你的SQL代码我正在分析,有什么更好的写法。可以继续贴出来。这个稍后我会测试下是否符合
用个IF语句就解决了,
[code=SQL]IF EXISTS (SELECT * FROM 订单排序表)
BEGIN
SELECT * FROM
END
ELSE
BREAK;
[code]
2,在 符合1 的记录中,首先显示出现在订购表的中记录
3,如果多个含xx的记录属于同一家公司,那么每个公司只显示一条含xx的商品记录
是否这样?
select o.orderid,p.prodid,p.prodName, p.prodImg, p.prodOtherInfo, c.companyName
from 产品表 p
inner join 订购排序表 o on o.companyid=p.companyid
inner join 企业表 c on c.companyid=p.companyid
where o.AdsKey='XX'
and p.prodName like '%XX%'
union all
select 'A' orderid,p.prodid,p.prodName, p.prodImg, p.prodOtherInfo, c.companyName
from 产品表 p
inner join 企业表 c on c.companyid=p.companyid
where prodName like '%XX%'
and not exists(select * from 订购排序表 o where o.AdsKey='XX' and o.companyid=p.companyid)
order by orderid,prodid楼主先不要考虑一家企业只有一条记录,全部符合条件的找出来,然后在程式里实现将已经存在的企业的记录跳过,这样可以吗?或者sql语句用存储过程来返回所要的结果,这样比用一条语句来实现要容易些哦,个人遇见,希望能有帮助
union all下面部分的 'A' orderid是用来排序时用的,不知道这样字母和数字能不能这样union all
表太多了。数据量多也才可以很清楚看出结果来。如果各位有兴趣研究的话。我可以提供相应的数据库
我的QQ:475457934,加的时候能注下Csdn 排序下。我好更快联系你们。谢谢大家支持.
/*------------------------------------------------------------------------
1.搜查的名称符合企业名称,则按普通排序列出全部该企业的产品信息(可重复)
2.搜寻的名称符合关键字订购,则上部按关键字查询前30条,下部按普通查询
3.搜索的名称不符合关键字订购,则上部为Null,下部为普通查询
注:本模块为站点复杂模块算法。请注意建立好对应索引和缓存策略*------------------------------------------------------------------------*/ALTER PROCEDURE GetSawSca_SearchList(
@BigCityID int,--城市大分类
@SmallCityID int,--城市小分类@CompanyJY nvarchar(50),--经营分类,采用Text属性@DateNum int, --间隔天数 @IsVip int, --只查看高级会员@Keys nvarchar(30),--关键字@IsOrderQuery bit, --是否是最顶部的被订购关键字@startIndex int,
@endIndex int,
@docount bit
)
as
set nocount ondeclare @strFilter nvarchar(500)
set @strFilter=''declare @strOrder nvarchar(200)
set @strOrder=' order by SawRole.RoleOrder Asc,SawCompany.SawManageSort desc,SawCompany.SawOrder Asc'-------------------------------------查询的Where条件组合(全部信息的话用1985)----------------------------------------------------------------
if(@BigCityID<>1985)
set @strFilter=@strFilter+ ' and ( BigAddress.SawID='+CAST (@BigCityID AS NVarChar)+')' if(@SmallCityID<>1985)
set @strFilter=@strFilter+' and ( SmallAddress.SawID='+CAST (@SmallCityID AS NVarChar)+')'
if(@CompanyJY <>'')
set @strFilter=@strFilter+' and (SawCompany.CompanyJY = '''+@CompanyJY +''')'/*
if(@SawScaSort<>1985)
set @strFilter=@strFilter+' and SawSca.SawScaSort='+Cast(@SawScaSort as nvarchar)
*/
if(@IsVip<>1985)
set @strFilter=@strFilter+' and (SawRole.IsVip=1)'if(@DateNum<>1985 and @DateNum<>0)
set @strFilter=@strFilter+' and SawSca.SawAddDate>='+CONVERT(char(12),dateadd(d,-@DateNum,GetDate()), 3) declare @strSQL NVARCHAR(800)
set @strSQL=''create table #TempSawSca(Pid int identity(1,1),SawID int, SawCompanyID int)
--专门查询关键字订购表(30个以内)
if(@Keys<>'' and @IsOrderQuery=1 and @startIndex<>1)
begin ---------------------------------------------------------------1
insert into #TempSawSca
set @strSQL='Select SawSca.SawID,SawSca.SawCompanyID
from SawSca
inner join
(
select CompanyID,SawOrder
from CompanyAdsKey
where SawAdsKey=''%'+@Keys+'%'' and SawSearchPage=2 and SawEDate>=GetDate()
and SawManageSort in(2,3)) bb
on SawSca.SawCompanyID=bb.CompanyID
where SawSca.SawIsShow=2 and SawSca.SawManageSort in(2,3) and
SawScaSort=1
and ( SawSca.SawTitle like ''%'+@Keys+'%'' or SawScaContect like ''%'+@Keys+'%'')
order by bb.SawOrder,SawCompanyID Asc
'
exec @strSQL
--过滤重复行
--过滤掉重复数据项
delete a from #TempSawSca a where Pid not in
(select max(Pid) from #TempSawSca where a.SawCompanyID=#TempSawSca.SawCompanyID)set @strSQL='select Top 30 SawThumbPic,SawSourcePic,SawTitle,SawScaJS,BigAddress.City as BigCity,
SmallAddress.City as SmallCity,SawSca.SawScaSort,SawSca.SawAddDate,SawSca.SawID
from SawSca
inner join #TempSawSca
on #TempSawSca.SawID=SawSca.SawID
inner join SawCompany
on SawSca.SawCompanyID=SawCompany.SawID
inner join SawUser
on SawUser.SawUserTag=SawCompany.UserTag
inner join SawRole
on SawRole.RoleID=SawUser.UserType
inner join SawCity SmallAddress
on SmallAddress.SawID=SawCompany.CityID
inner join SawCity BigAddress
on BigAddress.SawID=SmallAddress.OwnID
where 1=1 '+ @strFilter+'
'end ---------------------------------------------------------------------1if(@Keys<>'' and @IsOrderQuery<>1 )
begin ----------------------------------------------------------------------------2
--检测是否是以企业名义进行检索
declare @SearchCompanyID int
set @strSQL='select @SearchCompanyID=SawID
from SawCompany
where CompanyName like ''%'+@Keys+'%''
'if(@@rowcount=1)--符合以企业名义进行检索(查询出全部该企业的供应信息)
begin ---------------------------------------------------------------------------------------3if(@docount=1)
set @strSQL='Select count(SawSca.SawID)
from SawSca
inner join SawCompany
on SawSca.SawCompanyID=SawCompany.SawID
inner join SawUser
on SawUser.SawUserTag=SawCompany.UserTag
inner join SawRole
on SawRole.RoleID=SawUser.UserType
inner join SawCity SmallAddress
on SmallAddress.SawID=SawCompany.CityID
inner join SawCity BigAddress
on BigAddress.SawID=SmallAddress.OwnID
where 1=1 and SawSca.SawCompanyID='+ltrim(@SearchCompanyID)+ @strFilter+'
'
exec @strSQLelse
begin -------------------------------------------------------------------------5
create table #indextable(id int identity(1,1),nid int)
set rowcount @endIndex
insert into #indextable(nid)
set @strSQL='Select SawSca.SawID
from SawSca
inner join SawCompany
on SawSca.SawCompanyID=SawCompany.SawID
inner join SawUser
on SawUser.SawUserTag=SawCompany.UserTag
inner join SawRole
on SawRole.RoleID=SawUser.UserType
inner join SawCity SmallAddress
on SmallAddress.SawID=SawCompany.CityID
inner join SawCity BigAddress
on BigAddress.SawID=SmallAddress.OwnID
where 1=1 and SawSca.SawCompanyID='+ltrim(@SearchCompanyID)+ @strFilter+'
'
exec @strSQLset @strSQL='select SawThumbPic,SawSourcePic,SawTitle,SawScaJS,BigAddress.City as BigCity,
SmallAddress.City as SmallCity,SawSca.SawScaSort,SawSca.SawAddDate,SawSca.SawID
from SawSca
inner join #indextable
on SawSca.Sawid=#indextable.nid
inner join SawCompany
on SawSca.SawCompanyID=SawCompany.SawID
inner join SawUser
on SawUser.SawUserTag=SawCompany.UserTag
inner join SawRole
on SawRole.RoleID=SawUser.UserType
inner join SawCity SmallAddress
on SmallAddress.SawID=SawCompany.CityID
inner join SawCity BigAddress
on BigAddress.SawID=SmallAddress.OwnID
where #indextable.id>='+ltrim(@startIndex)+' and #indextable.id<='+ltrim(@endIndex)+' '
exec @strSQLend --------------------------------------------------------------------------------------------5end ----------------------------------------------------------------------------------------3else --不是以企业名义进行检索,以产品信息检索(查询出每家企业单条企业信息)begin -----------------------------------------------------------------------------------------4 delete from #TempSawSca
insert into #TempSawSca
set @strSQL='Select SawSca.SawID,SawSca.SawCompanyID
from SawSca
inner join SawCompany
on SawSca.SawCompanyID=SawCompany.SawID
inner join SawUser
on SawUser.SawUserTag=SawCompany.UserTag
inner join SawRole
on SawRole.RoleID=SawUser.UserType
inner join SawCity SmallAddress
on SmallAddress.SawID=SawCompany.CityID
inner join SawCity BigAddress
on BigAddress.SawID=SmallAddress.OwnID
where 1=1 '+@strFilter+@strOrder+'
'--过滤重复行
--过滤掉重复数据项
delete a from #TempSawSca a where Pid not in
(select max(Pid) from #TempSawSca where a.SawCompanyID=#TempSawSca.SawCompanyID)if(@docount=1)
set @strSQL='select count(Pid)
from #TempSawSca
'
exec @strSQL
else
create table #indextable2(id int identity(1,1),nid int)
set rowcount @endIndex
insert into #indextable2(nid)
set @strSQL='Select SawID
from #TempSawSca
order by #TempSawSca.Pid Asc'
exec @strSQLset @strSQL='
select SawThumbPic,SawSourcePic,SawTitle,SawScaJS,BigAddress.City as BigCity,
SmallAddress.City as SmallCity,SawSca.SawScaSort,SawSca.SawAddDate,SawSca.SawID
from SawSca
inner join #indextable2
on #indextable2.SawID=SawSca.SawID
inner join SawCompany
on SawSca.SawCompanyID=SawCompany.SawIDinner join SawCity SmallAddress
on SmallAddress.SawID=SawCompany.CityID
inner join SawCity BigAddress
on BigAddress.SawID=SmallAddress.OwnID
where 1=1 and #indextable2.id>='+ltrim(@startIndex)+' and #indextable2.id<='+ltrim(@endIndex)+'
order by #indextable2.id Asc'
exec @strSQLend -------------------------------------------------------------------------------------------4end -------------------------------------------------------------------------------2
drop table #TempSawScaset nocount off=================
各位帮我调试下。实在是解决不了。谢谢啦。写的有点复杂。自己看的都难受。汗~~~~很无奈
在关键字 'set' 附近有语法错误。
服务器: 消息 156,级别 15,状态 1,过程 GetSawSca_SearchList,行 150
在关键字 'else' 附近有语法错误。
服务器: 消息 156,级别 15,状态 1,过程 GetSawSca_SearchList,行 202
在关键字 'set' 附近有语法错误。
服务器: 消息 156,级别 15,状态 1,过程 GetSawSca_SearchList,行 227
在关键字 'else' 附近有语法错误。
服务器: 消息 156,级别 15,状态 1,过程 GetSawSca_SearchList,行 231
在关键字 'set' 附近有语法错误。
/*------------------------------------------------------------------------
1.搜查的名称符合企业名称,则按普通排序列出全部该企业的产品信息(可重复)
2.搜寻的名称符合关键字订购,则上部按关键字查询前30条,下部按普通查询
3.搜索的名称不符合关键字订购,则上部为Null,下部为普通查询
注:本模块为站点复杂模块算法。请注意建立好对应索引和缓存策略*------------------------------------------------------------------------*/ALTER PROCEDURE GetSawSca_SearchList(
@BigCityID int,--城市大分类
@SmallCityID int,--城市小分类@CompanyJY nvarchar(50),--经营分类,采用Text属性@DateNum int, --间隔天数 @IsVip int, --只查看高级会员@Keys nvarchar(30),--关键字@IsOrderQuery bit, --是否是最顶部的被订购关键字@startIndex int,
@endIndex int,
@docount bit
)
as
set nocount ondeclare @strFilter nvarchar(500)
set @strFilter=''declare @strOrder nvarchar(200)
set @strOrder=' order by SawRole.RoleOrder Asc,SawCompany.SawManageSort desc,SawCompany.SawOrder Asc'-------------------------------------查询的Where条件组合(全部信息的话用1985)----------------------------------------------------------------
if(@BigCityID<>1985)
set @strFilter=@strFilter+ ' and ( BigAddress.SawID='+CAST (@BigCityID AS NVarChar)+')' if(@SmallCityID<>1985)
set @strFilter=@strFilter+' and ( SmallAddress.SawID='+CAST (@SmallCityID AS NVarChar)+')'
if(@CompanyJY <>'')
set @strFilter=@strFilter+' and (SawCompany.CompanyJY = '''+@CompanyJY +''')'/*
if(@SawScaSort<>1985)
set @strFilter=@strFilter+' and SawSca.SawScaSort='+Cast(@SawScaSort as nvarchar)
*/
if(@IsVip<>1985)
set @strFilter=@strFilter+' and (SawRole.IsVip=1)'if(@DateNum<>1985 and @DateNum<>0)
set @strFilter=@strFilter+' and SawSca.SawAddDate>='+CONVERT(char(12),dateadd(d,-@DateNum,GetDate()), 3) declare @strSQL NVARCHAR(800)
set @strSQL=''create table #TempSawSca(Pid int identity(1,1),SawID int, SawCompanyID int)
--专门查询关键字订购表(30个以内)
if(@Keys<>'' and @IsOrderQuery=1 and @startIndex<>1)
begin ---------------------------------------------------------------1set @strSQL=' insert into #TempSawSca Select SawSca.SawID,SawSca.SawCompanyID
from SawSca
inner join
(
select CompanyID,SawOrder
from CompanyAdsKey
where SawAdsKey=''%'+ltrim(@Keys)+'%'' and SawSearchPage=2 and SawEDate>=GetDate()
and SawManageSort in(2,3)) bb
on SawSca.SawCompanyID=bb.CompanyID
where SawSca.SawIsShow=2 and SawSca.SawManageSort in(2,3) and
SawScaSort=1
and ( SawSca.SawTitle like ''%'+@Keys+'%'' or SawScaContect like ''%'+@Keys+'%'')
order by bb.SawOrder,SawCompanyID Asc
'
exec @strSQL
--过滤重复行
--过滤掉重复数据项
delete a from #TempSawSca a where Pid not in
(select max(Pid) from #TempSawSca where a.SawCompanyID=#TempSawSca.SawCompanyID)set @strSQL='select Top 30 SawThumbPic,SawSourcePic,SawTitle,SawScaJS,BigAddress.City as BigCity,
SmallAddress.City as SmallCity,SawSca.SawScaSort,SawSca.SawAddDate,SawSca.SawID
from SawSca
inner join #TempSawSca
on #TempSawSca.SawID=SawSca.SawID
inner join SawCompany
on SawSca.SawCompanyID=SawCompany.SawID
inner join SawUser
on SawUser.SawUserTag=SawCompany.UserTag
inner join SawRole
on SawRole.RoleID=SawUser.UserType
inner join SawCity SmallAddress
on SmallAddress.SawID=SawCompany.CityID
inner join SawCity BigAddress
on BigAddress.SawID=SmallAddress.OwnID
where 1=1 '+ @strFilter+'
'end ---------------------------------------------------------------------1if(@Keys<>'' and @IsOrderQuery<>1 )
begin ----------------------------------------------------------------------------2
--检测是否是以企业名义进行检索
declare @SearchCompanyID int
set @strSQL='select @SearchCompanyID=SawID
from SawCompany
where CompanyName like ''%'+@Keys+'%''
'if(@@rowcount=1)--符合以企业名义进行检索(查询出全部该企业的供应信息)
begin ---------------------------------------------------------------------------------------3if(@docount=1)
set @strSQL='Select count(SawSca.SawID)
from SawSca
inner join SawCompany
on SawSca.SawCompanyID=SawCompany.SawID
inner join SawUser
on SawUser.SawUserTag=SawCompany.UserTag
inner join SawRole
on SawRole.RoleID=SawUser.UserType
inner join SawCity SmallAddress
on SmallAddress.SawID=SawCompany.CityID
inner join SawCity BigAddress
on BigAddress.SawID=SmallAddress.OwnID
where 1=1 and SawSca.SawCompanyID='+ltrim(@SearchCompanyID)+ @strFilter+'
'
exec @strSQLelse
begin -------------------------------------------------------------------------5
set @strSQL='
create table #indextable(id int identity(1,1),nid int)
set rowcount '+ltrim(@endIndex)+'
insert into #indextable(nid)
Select SawSca.SawID
from SawSca
inner join SawCompany
on SawSca.SawCompanyID=SawCompany.SawID
inner join SawUser
on SawUser.SawUserTag=SawCompany.UserTag
inner join SawRole
on SawRole.RoleID=SawUser.UserType
inner join SawCity SmallAddress
on SmallAddress.SawID=SawCompany.CityID
inner join SawCity BigAddress
on BigAddress.SawID=SmallAddress.OwnID
where 1=1 and SawSca.SawCompanyID='+ltrim(@SearchCompanyID)+ @strFilter+'
'
exec @strSQLset @strSQL='select SawThumbPic,SawSourcePic,SawTitle,SawScaJS,BigAddress.City as BigCity,
SmallAddress.City as SmallCity,SawSca.SawScaSort,SawSca.SawAddDate,SawSca.SawID
from SawSca
inner join #indextable
on SawSca.Sawid=#indextable.nid
inner join SawCompany
on SawSca.SawCompanyID=SawCompany.SawID
inner join SawUser
on SawUser.SawUserTag=SawCompany.UserTag
inner join SawRole
on SawRole.RoleID=SawUser.UserType
inner join SawCity SmallAddress
on SmallAddress.SawID=SawCompany.CityID
inner join SawCity BigAddress
on BigAddress.SawID=SmallAddress.OwnID
where #indextable.id>='+ltrim(@startIndex)+' and #indextable.id<='+ltrim(@endIndex)+' '
exec @strSQLend --------------------------------------------------------------------------------------------5end ----------------------------------------------------------------------------------------3else --不是以企业名义进行检索,以产品信息检索(查询出每家企业单条企业信息)begin -----------------------------------------------------------------------------------------4 delete from #TempSawScaset @strSQL='insert into #TempSawSca Select SawSca.SawID,SawSca.SawCompanyID
from SawSca
inner join SawCompany
on SawSca.SawCompanyID=SawCompany.SawID
inner join SawUser
on SawUser.SawUserTag=SawCompany.UserTag
inner join SawRole
on SawRole.RoleID=SawUser.UserType
inner join SawCity SmallAddress
on SmallAddress.SawID=SawCompany.CityID
inner join SawCity BigAddress
on BigAddress.SawID=SmallAddress.OwnID
where 1=1 '+@strFilter+@strOrder+'
'--过滤重复行
--过滤掉重复数据项
delete a from #TempSawSca a where Pid not in
(select max(Pid) from #TempSawSca where a.SawCompanyID=#TempSawSca.SawCompanyID)if(@docount=1)
set @strSQL='select count(Pid)
from #TempSawSca
'
exec @strSQL
else
create table #indextable2(id int identity(1,1),nid int)
set rowcount @endIndexset @strSQL='insert into #indextable2(nid) Select SawID
from #TempSawSca
order by #TempSawSca.Pid Asc'
exec @strSQLset @strSQL='
select SawThumbPic,SawSourcePic,SawTitle,SawScaJS,BigAddress.City as BigCity,
SmallAddress.City as SmallCity,SawSca.SawScaSort,SawSca.SawAddDate,SawSca.SawID
from SawSca
inner join #indextable2
on #indextable2.SawID=SawSca.SawID
inner join SawCompany
on SawSca.SawCompanyID=SawCompany.SawIDinner join SawCity SmallAddress
on SmallAddress.SawID=SawCompany.CityID
inner join SawCity BigAddress
on BigAddress.SawID=SmallAddress.OwnID
where 1=1 and #indextable2.id>='+ltrim(@startIndex)+' and #indextable2.id<='+ltrim(@endIndex)+'
order by #indextable2.id Asc'
exec @strSQLend -------------------------------------------------------------------------------------------4end -------------------------------------------------------------------------------2
drop table #TempSawScaset nocount off
==================
现在语法该正确了一大半。可是提示
服务器: 消息 156,级别 15,状态 1,过程 GetSawSca_SearchList,行 150
在关键字 'else' 附近有语法错误。
服务器: 消息 156,级别 15,状态 1,过程 GetSawSca_SearchList,行 227
在关键字 'else' 附近有语法错误。
--缺少begin或end
ALTER PROCEDURE GetSawSca_SearchList(
@BigCityID int,--城市大分类
@SmallCityID int,--城市小分类@CompanyJY nvarchar(50),--经营分类,采用Text属性@DateNum int, --间隔天数 @IsVip int, --只查看高级会员@Keys nvarchar(30),--关键字@IsOrderQuery bit, --是否是最顶部的被订购关键字@startIndex int,
@endIndex int,
@docount bit
)
as
set nocount ondeclare @strFilter nvarchar(500)
set @strFilter=''declare @strOrder nvarchar(200)
set @strOrder=' order by SawRole.RoleOrder Asc,SawCompany.SawManageSort desc,SawCompany.SawOrder Asc'-------------------------------------查询的Where条件组合(全部信息的话用1985)----------------------------------------------------------------
if(@BigCityID<>1985)
set @strFilter=@strFilter+ ' and ( BigAddress.SawID='+CAST (@BigCityID AS NVarChar)+')' if(@SmallCityID<>1985)
set @strFilter=@strFilter+' and ( SmallAddress.SawID='+CAST (@SmallCityID AS NVarChar)+')'
if(@CompanyJY <>'')
set @strFilter=@strFilter+' and (SawCompany.CompanyJY = '''+@CompanyJY +''')'/*
if(@SawScaSort<>1985)
set @strFilter=@strFilter+' and SawSca.SawScaSort='+Cast(@SawScaSort as nvarchar)
*/
if(@IsVip<>1985)
set @strFilter=@strFilter+' and (SawRole.IsVip=1)'if(@DateNum<>1985 and @DateNum<>0)
set @strFilter=@strFilter+' and SawSca.SawAddDate>='+CONVERT(char(12),dateadd(d,-@DateNum,GetDate()), 3) declare @strSQL NVARCHAR(800)
set @strSQL=''create table #TempSawSca(Pid int identity(1,1),SawID int, SawCompanyID int)
--专门查询关键字订购表(30个以内)
if(@Keys<>'' and @IsOrderQuery=1 and @startIndex<>1)
begin ---------------------------------------------------------------1set @strSQL=' insert into #TempSawSca Select SawSca.SawID,SawSca.SawCompanyID
from SawSca
inner join
(
select CompanyID,SawOrder
from CompanyAdsKey
where SawAdsKey=''%'+ltrim(@Keys)+'%'' and SawSearchPage=2 and SawEDate>=GetDate()
and SawManageSort in(2,3)) bb
on SawSca.SawCompanyID=bb.CompanyID
where SawSca.SawIsShow=2 and SawSca.SawManageSort in(2,3) and
SawScaSort=1
and ( SawSca.SawTitle like ''%'+@Keys+'%'' or SawScaContect like ''%'+@Keys+'%'')
order by bb.SawOrder,SawCompanyID Asc
'
exec @strSQL
--过滤重复行
--过滤掉重复数据项
delete a from #TempSawSca a where Pid not in
(select max(Pid) from #TempSawSca where a.SawCompanyID=#TempSawSca.SawCompanyID)set @strSQL='select Top 30 SawThumbPic,SawSourcePic,SawTitle,SawScaJS,BigAddress.City as BigCity,
SmallAddress.City as SmallCity,SawSca.SawScaSort,SawSca.SawAddDate,SawSca.SawID
from SawSca
inner join #TempSawSca
on #TempSawSca.SawID=SawSca.SawID
inner join SawCompany
on SawSca.SawCompanyID=SawCompany.SawID
inner join SawUser
on SawUser.SawUserTag=SawCompany.UserTag
inner join SawRole
on SawRole.RoleID=SawUser.UserType
inner join SawCity SmallAddress
on SmallAddress.SawID=SawCompany.CityID
inner join SawCity BigAddress
on BigAddress.SawID=SmallAddress.OwnID
where 1=1 '+ @strFilter+'
'end ---------------------------------------------------------------------1if(@Keys<>'' and @IsOrderQuery<>1 )
begin ----------------------------------------------------------------------------2
--检测是否是以企业名义进行检索
declare @SearchCompanyID int
set @strSQL='select @SearchCompanyID=SawID
from SawCompany
where CompanyName like ''%'+@Keys+'%''
'if(@@rowcount=1)--符合以企业名义进行检索(查询出全部该企业的供应信息)
begin ---------------------------------------------------------------------------------------3if(@docount=1)
set @strSQL='Select count(SawSca.SawID)
from SawSca
inner join SawCompany
on SawSca.SawCompanyID=SawCompany.SawID
inner join SawUser
on SawUser.SawUserTag=SawCompany.UserTag
inner join SawRole
on SawRole.RoleID=SawUser.UserType
inner join SawCity SmallAddress
on SmallAddress.SawID=SawCompany.CityID
inner join SawCity BigAddress
on BigAddress.SawID=SmallAddress.OwnID
where 1=1 and SawSca.SawCompanyID='+ltrim(@SearchCompanyID)+ @strFilter+'
'
exec @strSQL
end
else
begin -------------------------------------------------------------------------5
set @strSQL='
create table #indextable(id int identity(1,1),nid int)
set rowcount '+ltrim(@endIndex)+'
insert into #indextable(nid)
Select SawSca.SawID
from SawSca
inner join SawCompany
on SawSca.SawCompanyID=SawCompany.SawID
inner join SawUser
on SawUser.SawUserTag=SawCompany.UserTag
inner join SawRole
on SawRole.RoleID=SawUser.UserType
inner join SawCity SmallAddress
on SmallAddress.SawID=SawCompany.CityID
inner join SawCity BigAddress
on BigAddress.SawID=SmallAddress.OwnID
where 1=1 and SawSca.SawCompanyID='+ltrim(@SearchCompanyID)+ @strFilter+'
'
exec @strSQLset @strSQL='select SawThumbPic,SawSourcePic,SawTitle,SawScaJS,BigAddress.City as BigCity,
SmallAddress.City as SmallCity,SawSca.SawScaSort,SawSca.SawAddDate,SawSca.SawID
from SawSca
inner join #indextable
on SawSca.Sawid=#indextable.nid
inner join SawCompany
on SawSca.SawCompanyID=SawCompany.SawID
inner join SawUser
on SawUser.SawUserTag=SawCompany.UserTag
inner join SawRole
on SawRole.RoleID=SawUser.UserType
inner join SawCity SmallAddress
on SmallAddress.SawID=SawCompany.CityID
inner join SawCity BigAddress
on BigAddress.SawID=SmallAddress.OwnID
where #indextable.id>='+ltrim(@startIndex)+' and #indextable.id<='+ltrim(@endIndex)+' '
exec @strSQLend --------------------------------------------------------------------------------------------5end ----------------------------------------------------------------------------------------3else --不是以企业名义进行检索,以产品信息检索(查询出每家企业单条企业信息)begin -----------------------------------------------------------------------------------------4 delete from #TempSawScaset @strSQL='insert into #TempSawSca Select SawSca.SawID,SawSca.SawCompanyID
from SawSca
inner join SawCompany
on SawSca.SawCompanyID=SawCompany.SawID
inner join SawUser
on SawUser.SawUserTag=SawCompany.UserTag
inner join SawRole
on SawRole.RoleID=SawUser.UserType
inner join SawCity SmallAddress
on SmallAddress.SawID=SawCompany.CityID
inner join SawCity BigAddress
on BigAddress.SawID=SmallAddress.OwnID
where 1=1 '+@strFilter+@strOrder+'
'--过滤重复行
--过滤掉重复数据项
delete a from #TempSawSca a where Pid not in
(select max(Pid) from #TempSawSca where a.SawCompanyID=#TempSawSca.SawCompanyID)if(@docount=1)
begin
set @strSQL='select count(Pid) from #TempSawSca'
exec @strSQL
end
else
begin
create table #indextable2(id int identity(1,1),nid int)
set rowcount @endIndex set @strSQL='insert into #indextable2(nid) Select SawID from #TempSawSca order by #TempSawSca.Pid Asc'
exec @strSQL set @strSQL='
select SawThumbPic,SawSourcePic,SawTitle,SawScaJS,BigAddress.City as BigCity,
SmallAddress.City as SmallCity,SawSca.SawScaSort,SawSca.SawAddDate,SawSca.SawID
from SawSca
inner join #indextable2
on #indextable2.SawID=SawSca.SawID
inner join SawCompany
on SawSca.SawCompanyID=SawCompany.SawID inner join SawCity SmallAddress
on SmallAddress.SawID=SawCompany.CityID
inner join SawCity BigAddress
on BigAddress.SawID=SmallAddress.OwnID
where 1=1 and #indextable2.id>='+ltrim(@startIndex)+' and #indextable2.id<='+ltrim(@endIndex)+'
order by #indextable2.id Asc'
exec @strSQL
end -------------------------------------------------------------------------------------------4end -------------------------------------------------------------------------------2
drop table #TempSawScaset nocount off
set @strSQL=''
exec @strSQL
???
详细跟踪代码如下:
运行[dbo].[GetSawSca_SearchList] ( @BigCityID = 1985, @SmallCityID = 1985, @CompanyJY = , @DateNum = 1985, @IsVip = 1985, @Keys = 金藏机电, @IsOrderQuery = True, @startIndex = 1, @endIndex = 35, @docount = False ). insert into #TempSawSca Select SawSca.SawID,SawSca.SawCompanyID
from SawSca
inner join
(
select CompanyID,SawOrder
from CompanyAdsKey
where SawAdsKey='%金藏机电%' and SawSearchPage=2 and SawEDate>=GetDate()
and SawManageSort in(2,3)) bb
on SawSca.SawCompanyID=bb.CompanyID
where SawSca.SawIsShow=2 and SawSca.SawManageSort in(2,3) and
SawScaSort=1
and ( SawSca.SawTitle like '%金藏机电%' or SawScaContect like '%金藏机电%')
order by bb.SawOrder,SawCompanyID Asc名称 ' insert into #TempSawSca Select SawSca.SawID,SawSca.SawCompanyID
from SawSca
inner join
(
select CompanyID,SawOrder
from CompanyAdsKey
where SawAdsKey='%金藏机电%' and SawSearchPage=2 and SawEDate>=GetDate()
and SawManageSort in(2,3)) bb
on SawSca.SawCompanyID=bb.CompanyID
where SawSca.SawIsShow=2 and SawSca.SawManageSort in(2,3) and
SawScaSort=1
and ( SawSca.SawTitle like '%金...
没有行受影响。
(返回 0 行)
@RETURN_VALUE =
完成 [dbo].[GetSawSca_SearchList] 运行。
线程 '(null)(62)' (0x80000f28) 已退出,返回值为 0 (0x0)。
程序“SQL 调试器: T-SQL”已退出,返回值为 0 (0x0)。
最好是print出来看看,
每个exec 换成print,
然后把每段sql在查询分析器里面执行看看.是哪部分出错了,
这样才能找到原因,
print的语法在SQL查询分析器都正常。但为什么在VS2005的调试中会提示 名称什么什么出错。太怪异了。
发现输出的sql放到查询分析器都正确。但在VS2005调试却是老是提升 名称出错