假设 有表A, 查询条件字段为a,a为外键 .当a=1时候,查询结果如下
(8 行受影响)(8 行受影响)
当a=3时候,查询结果
(3 行受影响)(0 行受影响)就是查询到有值,但是返回为0不了解,请问是什么原因引起的,有大虾知道吗?
(8 行受影响)(8 行受影响)
当a=3时候,查询结果
(3 行受影响)(0 行受影响)就是查询到有值,但是返回为0不了解,请问是什么原因引起的,有大虾知道吗?
(
@TotalRecs int,
@PageIndex int,
@PageCount int,
@IntoWare_No varchar(50),
@IntoWare_Warehouse_No varchar(10),
@OpTime1 nvarchar(20),
@OpTime2 nvarchar(20),
@IntoWare_MarkPerson varchar(20)
)
ASdeclare @str varchar(5000)
declare @firstrec varchar(10)
declare @lastrec varchar(10)
declare @firstreci int
declare @lastreci int
declare @strSql varchar(3000)
declare @strColums varchar(1500)
set @firstrec=(@PageIndex-1)*@PageCount+1
set @lastreci=@PageIndex*@PageCount
set @firstrec=cast(@firstrec as varchar(10))
if @PageIndex*@PageCount>=@TotalRecs
set @lastrec=cast(@TotalRecs as varchar(10))
else
set @lastrec=cast(@lastreci as varchar(10))
set @strSql=' where 1=1 '
set @strColums='SELECT IntoWare_No, IntoWare_Warehouse_No, IntoWare_Date, IntoWare_MakePerson, IntoWare_Operator
FROM IntoWarehourse 'if isnull(@IntoWare_No,'') <> ''
set @strSql = @strSql + ' AND (IntoWare_No like ''%' + @IntoWare_No +'%'')'
if isnull(@IntoWare_Warehouse_No,'') <> ''
set @strSql = @strSql + ' AND (IntoWare_Warehouse_No = ''' + @IntoWare_Warehouse_No +''')' //为字段a
if isnull(@OpTime1,'')<>''
set @strSql = @strSql + ' AND (IntoWare_Date>=''' + @OpTime1 +''')'
if isnull(@OpTime2,'')<>''
set @strSql = @strSql + ' AND (IntoWare_Date<=''' + @OpTime2 +''')'
if isnull(@IntoWare_MarkPerson,'') <> ''
set @strSql = @strSql + ' AND (IntoWare_MakePerson like ''%' + @IntoWare_MarkPerson +'%'')'
CREATE TABLE #temp (ser int)
execute('insert into #temp select Ser from IntoWarehourse '+@strSql+'order by Ser ')
set @str=rtrim(@strColums)+' where Ser in (select ser from #temp where ser>='+'(select max(ser) from #temp where ser in (select top '+@firstrec +' ser from #temp ))'
+' and ser<=(select max(ser) from #temp where ser in (select top '+@lastrec +' ser from #temp )))'
--print @str
execute(@str)
--exec IntoWarehouse_SelectByParamsPerPage 1000,1,50,'','3','','',''
IntoWare_Warehouse_No varchar(10),
OpTime1 nvarchar(20),
OpTime2 nvarchar(20),
IntoWare_MarkPerson varchar(20)
IntoWare_Date, IntoWare_MakePerson, IntoWare_Operator
FROM IntoWarehourse
WHERE 1=1 AND (IntoWare_Warehouse_No = '3')然后在看下往临时表里查的这句话 看有没有记录
select Ser from IntoWarehourse where 1=1 AND (IntoWare_Warehouse_No = '3') order by ser 如果都有记录。
那就是这句话的问题了。个人感觉这句话出问题的概率最大,嵌套太多。建议仔细查看下。
SELECT IntoWare_No, IntoWare_Warehouse_No,
IntoWare_Date, IntoWare_MakePerson, IntoWare_Operator
FROM IntoWarehourse
where Ser in (select ser from #temp where
ser>=(select max(ser) from #temp where ser in (select top
1 ser from #temp )) and ser <=(select max(ser) from #temp
where ser in (select top 50 ser from #temp )))
set nocount on-- 设置成返回结果集, 也返回每个语句的 多少行受影响
set nocount off
SELECT IntoWare_No, IntoWare_Warehouse_No,
IntoWare_Date, IntoWare_MakePerson, IntoWare_Operator
FROM IntoWarehourse
WHERE 1=1 AND (IntoWare_Warehouse_No = '3')s2:
select Ser from IntoWarehourse where 1=1 AND (IntoWare_Warehouse_No = '3') order by ser s1+s2:
SELECT IntoWare_No, IntoWare_Warehouse_No,
IntoWare_Date, IntoWare_MakePerson, IntoWare_Operator
FROM IntoWarehourse
WHERE Ser in (select Ser from IntoWarehourse where 1=1 AND (IntoWare_Warehouse_No = '3'))
order by sers3:
CREATE TABLE #temp (ser int)
insert into #temp select Ser from IntoWarehourse where 1=1 AND (IntoWare_Warehouse_No = '3') order by ser
SELECT IntoWare_No, IntoWare_Warehouse_No,
IntoWare_Date, IntoWare_MakePerson, IntoWare_Operator
FROM IntoWarehourse
where Ser in (select ser from #temp where
ser>=(select max(ser) from #temp where ser in (select top
1 ser from #temp ))
and ser <=(select max(ser) from #temp where ser in
(select top 1000 ser from #temp )))
全部OK,,, 不知道哪里出错了.