use pubs
select * from sales order by ord_num desc,ord_date desc希望能够高效率,因为我每输入一个字符就要执行一次create proc usp_get5Rows
@colname varchar(25), --按这个列,order_date排序
@coltext varchar(25) --匹配字符
As
.......我想先让它以 @colname列按指定顺序排序,然后 找出与@coltext最匹配的数据放在第三行。
如下
exec usp_get5Rows 'ord_num','X99'得到如下数据
8042 423LL930 1994-09-14 00:00:00.000 10 ON invoice BU1032
8042 423LL922 1994-09-14 00:00:00.000 15 ON invoice MC3021
7896 X999 1993-02-21 00:00:00.000 35 ON invoice BU2075
7896 TQ456 1993-12-12 00:00:00.000 10 Net 60 MC2222
7896 QQ2299 1993-10-28 00:00:00.000 15 Net 60 BU7832
exec usp_get5Rows 'ord_num','p'8042 QA879.1 1993-05-22 00:00:00.000 30 Net 30 PC1035
7066 QA7442.3 1994-09-13 00:00:00.000 75 ON invoice PS2091
8042 P723 1993-03-11 00:00:00.000 25 Net 30 BU1111
7131 P3087a 1993-05-29 00:00:00.000 15 Net 60 PS3333
7131 P3087a 1993-05-29 00:00:00.000 25 Net 60 PS7777
exec usp_get5Rows 'ord_num','423'
6380 722a 1994-09-13 00:00:00.000 3 Net 60 PS2091
6380 6871 1994-09-14 00:00:00.000 5 Net 60 BU1032
8042 423LL930 1994-09-14 00:00:00.000 10 ON invoice BU1032
8042 423LL922 1994-09-14 00:00:00.000 15 ON invoice MC3021
7896 X999 1993-02-21 00:00:00.000 35 ON invoice BU2075exec usp_get5Rows 'ord_num',''8042 423LL930 1994-09-14 00:00:00.000 10 ON invoice BU1032
8042 423LL922 1994-09-14 00:00:00.000 15 ON invoice MC3021
7896 X999 1993-02-21 00:00:00.000 35 ON invoice BU2075
7896 TQ456 1993-12-12 00:00:00.000 10 Net 60 MC2222
7896 QQ2299 1993-10-28 00:00:00.000 15 Net 60 BU7832
select * from sales order by ord_num desc,ord_date desc希望能够高效率,因为我每输入一个字符就要执行一次create proc usp_get5Rows
@colname varchar(25), --按这个列,order_date排序
@coltext varchar(25) --匹配字符
As
.......我想先让它以 @colname列按指定顺序排序,然后 找出与@coltext最匹配的数据放在第三行。
如下
exec usp_get5Rows 'ord_num','X99'得到如下数据
8042 423LL930 1994-09-14 00:00:00.000 10 ON invoice BU1032
8042 423LL922 1994-09-14 00:00:00.000 15 ON invoice MC3021
7896 X999 1993-02-21 00:00:00.000 35 ON invoice BU2075
7896 TQ456 1993-12-12 00:00:00.000 10 Net 60 MC2222
7896 QQ2299 1993-10-28 00:00:00.000 15 Net 60 BU7832
exec usp_get5Rows 'ord_num','p'8042 QA879.1 1993-05-22 00:00:00.000 30 Net 30 PC1035
7066 QA7442.3 1994-09-13 00:00:00.000 75 ON invoice PS2091
8042 P723 1993-03-11 00:00:00.000 25 Net 30 BU1111
7131 P3087a 1993-05-29 00:00:00.000 15 Net 60 PS3333
7131 P3087a 1993-05-29 00:00:00.000 25 Net 60 PS7777
exec usp_get5Rows 'ord_num','423'
6380 722a 1994-09-13 00:00:00.000 3 Net 60 PS2091
6380 6871 1994-09-14 00:00:00.000 5 Net 60 BU1032
8042 423LL930 1994-09-14 00:00:00.000 10 ON invoice BU1032
8042 423LL922 1994-09-14 00:00:00.000 15 ON invoice MC3021
7896 X999 1993-02-21 00:00:00.000 35 ON invoice BU2075exec usp_get5Rows 'ord_num',''8042 423LL930 1994-09-14 00:00:00.000 10 ON invoice BU1032
8042 423LL922 1994-09-14 00:00:00.000 15 ON invoice MC3021
7896 X999 1993-02-21 00:00:00.000 35 ON invoice BU2075
7896 TQ456 1993-12-12 00:00:00.000 10 Net 60 MC2222
7896 QQ2299 1993-10-28 00:00:00.000 15 Net 60 BU7832
@colname varchar(25), --按这个列,order_date排序
@coltext varchar(25) --匹配字符
As
set nocount on
declare @str nvarchar(4000)
set @str='';
set @str= ' select top 5 * from sales where '
set @str=@str + @colname + ' like ' + '''%'+ @coltext +'%'''set nocount off
--我觉得你描述的不清楚,是先排序后取出5条,再把符合的放在第3条?
create proc usp_get5Rows
@colname nvarchar(50),
@coltext nvarchar(20)
AS
set nocount on
declare @sql nvarchar(1000), @id int
select id=identity(int,11,1), * into #t from sales where 1=2 set @sql='select top 5 * from sales order by '+@colname+' desc,ord_date desc'
insert into #t exec(@sql) set @sql=N'select @id=min(id) from #t where '+@colname+N' like '''+@coltext+N'%''' exec sp_executesql @sql,N'@id int output',@id output
select * from #t
order by case when id in (select top 2 id from #t where id<>@id order by id) then 1
when id in (select top 2 id from #t where id<>@id order by id desc ) then 3
else 2 end, idset nocount off
GO
union all
select top 1 * from sales where colname=@coltext order by ord_num desc,ord_date desc
union all
select * from sales where id not in(select top 2 id from sales where colname order by ord_num desc,ord_date desc)
and id not <>(select top 1 id from sales where colname=@coltext order by ord_num desc,ord_date desc)
exec usp_get5Rows 'ord_num','X99' 得到如下数据
8042 423LL930 1994-09-14 00:00:00.000 10 ON invoice BU1032
8042 423LL922 1994-09-14 00:00:00.000 15 ON invoice MC3021
7896 X999 1993-02-21 00:00:00.000 35 ON invoice BU2075
7896 TQ456 1993-12-12 00:00:00.000 10 Net 60 MC2222
7896 QQ2299 1993-10-28 00:00:00.000 15 Net 60 BU7832 这是怎么来的
先谢谢啊。我刚才运行了,还没有完全达到我的效果,是先排序得到了临时表#a,有21条数据若匹配数据行数为8,则取#a 中的(6,7,8,9,10)
若匹配数据行数为21,则取#a 中的(19,20,21,1,12)
若匹配数据行数为1,则取#a 中的(20,21,1,2,3)
若匹配数据行数为2,则取#a 中的(21,1,2,3,4)
若匹配为空 ,取#a 中的(20,21,1,2,3)
顺序不能乱
他是先按 order_no列 desc 排序的,因为最匹配的为
7896 X999 1993-02-21 00:00:00.000 35 ON invoice BU2075所以把它前面后面的两行一起取出
@colname nvarchar(50),
@coltext nvarchar(20)
AS
set nocount on
declare @sql nvarchar(1000), @id int
select id=identity(int,1,1), convert(int,null) as tmp,* into #t from sales where 1=2 set @sql='select 1,* from sales order by '+@colname+' desc,ord_date desc'
insert into #t exec(@sql)
set @sql='select 2,* from sales order by '+@colname+' desc,ord_date desc'
insert into #t exec(@sql)
set @sql='select 3,* from sales order by '+@colname+' desc,ord_date desc'
insert into #t exec(@sql) set @sql=N'select @id=min(id) from #t where tmp=2 and '+@colname+N' like '''+@coltext+N'%''' exec sp_executesql @sql,N'@id int output',@id output select top 5 stor_id,ord_num,ord_date,qty,payterms,title_id from #t
where id between (@id-2) and (@id+2)
order by idset nocount off
GO
--結果
exec usp_get5Rows 'ord_num','X99'
/*
8042 423LL930 1994-09-14 00:00:00.000 10 ON invoice BU1032
8042 423LL922 1994-09-14 00:00:00.000 15 ON invoice MC3021
7896 X999 1993-02-21 00:00:00.000 35 ON invoice BU2075
7896 TQ456 1993-12-12 00:00:00.000 10 Net 60 MC2222
7896 QQ2299 1993-10-28 00:00:00.000 15 Net 60 BU7832*/
exec usp_get5Rows 'ord_num','p'
/*
8042 QA879.1 1993-05-22 00:00:00.000 30 Net 30 PC1035
7066 QA7442.3 1994-09-13 00:00:00.000 75 ON invoice PS2091
8042 P723 1993-03-11 00:00:00.000 25 Net 30 BU1111
7131 P3087a 1993-05-29 00:00:00.000 15 Net 60 PS3333
7131 P3087a 1993-05-29 00:00:00.000 25 Net 60 PS7777*/exec usp_get5Rows 'ord_num','423'
/*
6380 722a 1994-09-13 00:00:00.000 3 Net 60 PS2091
6380 6871 1994-09-14 00:00:00.000 5 Net 60 BU1032
8042 423LL930 1994-09-14 00:00:00.000 10 ON invoice BU1032
8042 423LL922 1994-09-14 00:00:00.000 15 ON invoice MC3021
7896 X999 1993-02-21 00:00:00.000 35 ON invoice BU2075*/exec usp_get5Rows 'ord_num',''
/*
8042 423LL930 1994-09-14 00:00:00.000 10 ON invoice BU1032
8042 423LL922 1994-09-14 00:00:00.000 15 ON invoice MC3021
7896 X999 1993-02-21 00:00:00.000 35 ON invoice BU2075
7896 TQ456 1993-12-12 00:00:00.000 10 Net 60 MC2222
7896 QQ2299 1993-10-28 00:00:00.000 15 Net 60 BU7832*/
这里要做一下处理,在(1)和(3)处,前者只要最后的4条即可,后者只要最前的4条即可
(1) set @sql='select 1,* from sales order by '+@colname+' desc,ord_date desc'
insert into #t exec(@sql)
(2) set @sql='select 2,* from sales order by '+@colname+' desc,ord_date desc'
insert into #t exec(@sql)
(3) set @sql='select 3,* from sales order by '+@colname+' desc,ord_date desc'
insert into #t exec(@sql)
(2)加上匹配條件(where),也取5條,這樣臨時表一共就15條記錄,應該不慢了