select Top 10 *from [512auto_auto] as A Left join [512auto_Photo] as P on P.Re_AutoID=A.auto_ID Left join [512auto_Brand] as B on B.Brand_ID=A.Re_BrandID Left join [512auto_Catena] as C on C.Catena_ID=A.Re_CatenaID where auto_ID NOT IN (SELECT TOP 10 auto_ID FROM [512auto_auto] where B.Brand_Name like '%北%' or C.Catena_Name like '%北%' or auto_Model like '%北%' ORDER BY auto_id) and B.Brand_Name like '%北%' or C.Catena_Name like '%北%' or auto_Model like '%北%' ORDER BY auto_ID
select Top 10 *from [512auto_auto] as A Left join [512auto_Photo] as P
on P.Re_AutoID=A.auto_ID Left join [512auto_Brand] as B
on B.Brand_ID=A.Re_BrandID Left join [512auto_Catena] as C on C.Catena_ID=A.Re_CatenaID where
auto_ID NOT IN (SELECT TOP 10 auto_ID FROM [512auto_auto] 当这里有TOP变成10或都20的时候查出的数据是应该会变的但现在出来的数不变呀
where B.Brand_Name like '%北%' or C.Catena_Name like '%北%' or auto_Model like '%北%' ORDER BY auto_id) and B.Brand_Name like '%北%' or C.Catena_Name like '%北%' or auto_Model like '%北%'
ORDER BY auto_ID
2: 内层的 B.Brand_Name like '%北%' or C.Catena_Name like '%北%',那来的B和C
改为 Brand_Name like '%北%' or Catena_Name like '%北%'..............
3,但不知道楼主想要什么结果?
子查詢的table沒有與B相關聯SELECT TOP 10 auto_ID FROM [512auto_auto]
where B.Brand_Name like '%北%' or C.Catena_Name like '%北%' or auto_Model like '%北%'
ORDER BY auto_id
@blnError int output,
@rowscount int =0 output,
@searchType char(255),
@Price char(100),
@Price_End char(100),
@strAuto char(255),
@PageSize int = 10, -- 页尺寸
@PageIndex int = 1 -- 页码
AS
DECLARE @Num int
DECLARE @SQL varchar(8000)
SET @Num = @PageSize * (@PageIndex - 1)
If '1' = @searchType
BEGIN
SELECT @rowscount=count(Auto_ID) FROM [512auto_auto] where auto_Price BETWEEN @Price and @Price_End
set @SQL = 'select Top '+cast(@PageSize as varchar(80))+' P.Photo_Path_S as auto_PhotoPath_S,P.Photo_Path as auto_PhotoPath,B.Brand_Name as auto_BrandName,C.Catena_Name as auto_CatenaName,auto_ID,auto_Model,auto_Marketdate,auto_Figure,auto_Price,auto_Exhaust from [512auto_auto] as A Left join [512auto_Photo] as P on P.Re_AutoID=A.auto_ID Left join [512auto_Brand] as B on B.Brand_ID=A.Re_BrandID Left join [512auto_Catena] as C on C.Catena_ID=A.Re_CatenaID where auto_Price BETWEEN '+rtrim(cast(@Price as varchar(80)))+' and '+rtrim(cast(@Price_End as varchar(80)))+' and auto_ID>'+rtrim(cast(@Num as varchar(80)))+'and (auto_ID NOT IN (SELECT TOP '+cast(@Num as varchar(80))+' auto_ID FROM [512auto_auto] where auto_Price BETWEEN '+rtrim(cast(@Price as varchar(80)))+' and '+rtrim(cast(@Price_End as varchar(80)))+' ORDER BY auto_id)) ORDER BY auto_ID' END
Else
BEGIN
select @rowscount=count(Auto_ID) from [512auto_Photo] as P Left join [512auto_auto] as A on P.Re_AutoID=A.auto_ID Left join [512auto_Brand] as B on B.Brand_ID=A.Re_BrandID Left join [512auto_Catena] as C on C.Catena_ID=A.Re_CatenaID where B.Brand_Name like '%'+rtrim(@strAuto)+'%' or C.Catena_Name like '%'+rtrim(@strAuto)+'%' or auto_Model like '%'+rtrim(@strAuto)+'%'
set @SQL = 'select Top '+cast(@PageSize as varchar(80))+' P.Photo_Path_S as auto_PhotoPath_S,P.Photo_Path as auto_PhotoPath,B.Brand_Name as auto_BrandName,C.Catena_Name as auto_CatenaName,auto_ID,auto_Model,auto_Marketdate,auto_Figure,auto_Price,auto_Exhaust from [512auto_auto] as A Left join [512auto_Photo] as P on P.Re_AutoID=A.auto_ID Left join [512auto_Brand] as B on B.Brand_ID=A.Re_BrandID Left join [512auto_Catena] as C on C.Catena_ID=A.Re_CatenaID where B.Brand_Name like ''%'+rtrim(@strAuto)+'%'' or C.Catena_Name like ''%'+rtrim(@strAuto)+'%'' or auto_Model like ''%'+rtrim(@strAuto)+'%''and (auto_ID NOT IN (SELECT TOP '+cast(@Num as varchar(80))+' auto_ID from [512auto_auto] as A Left join [512auto_Photo] as P on P.Re_AutoID=A.auto_ID Left join [512auto_Brand] as B on B.Brand_ID=A.Re_BrandID Left join [512auto_Catena] as C on C.Catena_ID=A.Re_CatenaID where B.Brand_Name like ''%'+rtrim(@strAuto)+'%'' or C.Catena_Name like ''%'+rtrim(@strAuto)+'%'' or auto_Model like ''%'+rtrim(@strAuto)+'%'' ORDER BY auto_id)) ORDER BY auto_ID'END
exec(@SQL)
GO
这是我的全部语句 但就是查不出想要的结果,上面的可以正常得到结果
多个and 和or连用的时候这个需要注意and B.Brand_Name like '%北%' or C.Catena_Name like '%北%' or auto_Model like '%北%' 需要改为
and ( B.Brand_Name like '%北%' or C.Catena_Name like '%北%' or auto_Model like '%北%' )
你这是表全部字段的like吗?
或者这是固定的?固定的把它拿出去,用一个@str表示,看着清楚点