34,587
社区成员
发帖
与我相关
我的任务
分享
--条件优先级是:
--1.先按照gid精确查找,如果输如了gid但是没有精确值就直接返回空了。
--2.若没有输入gid,按照组名精确查找
--3.组名精确查找后,再惊醒模糊查找,优先返回通配符在name后面的,再返回通配符在name前面,
--4当上述数据相等时,以 type 从高到低(3,2,1)的顺序进行查找。
--5.在所有符合条件的数据中返回前15条。
declare @sql nvarchar(4000);
declare @gid varchar(30);
declare @group varchar(20);
declare @name varchar(20);
set @sql='select top 15 * from Tb ';
select @sql=@sql +' where '+
case when @gid is null then ' gid=@gid ' else ' [group]=@group ' end
+ ' and [name] like @name% and [name] like %@name order by [type] desc '
exec (@sql)
DECLARE @sql varchar(max)
IF @gid IS NOT NULL
SET @sql = 'SELECT TOP 15 *, 0 t FROM table1 WHERE gid='+Convert(varchar(11),@gid
ELSE
SET @sql = 'SELECT TOP 15 * FROM (
SELECT TOP 15 *, 1 t FROM table1 WHERE name='''+@name+''' ORDER BY type DESC
UNION ALL
SELECT TOP 15 *, 1 t FROM table1 WHERE name like'''+@name+'%'' ORDER BY type DESC
UNION ALL
SELECT TOP 15 *, 1 t FROM table1 WHERE name like''%'+@name+''' ORDER BY type DESC
) t
ORDER BY t, type DESC'
EXEC (@sql)