SELECT top 10 * FROM vi_Theme WHERE ThemeId > (
SELECT MAX(ThemeId) FROM (SELECT top 20 ThemeId,name from Lotgame_Theme WHERE Name LIKE '%' + parm + '%' ORDER BY ThemeId ASC)as t1)
and Name LIKE '%' + parm + '%'
执行了两次模糊查询..很郁闷..不知道这条SQL语句应该怎么优化呢..谢谢
SELECT MAX(ThemeId) FROM (SELECT top 20 ThemeId,name from Lotgame_Theme WHERE Name LIKE '%' + parm + '%' ORDER BY ThemeId ASC)as t1)
and Name LIKE '%' + parm + '%'
执行了两次模糊查询..很郁闷..不知道这条SQL语句应该怎么优化呢..谢谢
SELECT MAX(ThemeId) FROM
(SELECT top 30 ThemeId from Lotgame_Theme
WHERE Name LIKE '%' + parm + '%' ORDER BY ThemeId ASC)as t1)top30的前十个
(SELECT top 30 ThemeId from Lotgame_Theme
WHERE Name LIKE '%' + parm + '%' ORDER BY ThemeId ASC)tp上面大意了
(SELECT top 30 * from Lotgame_Theme
WHERE Name LIKE '%' + parm + '%' ORDER BY ThemeId ASC)tp
SELECT top 10 * FROM vi_Theme WHERE ThemeId > (
SELECT MAX(ThemeId) FROM (SELECT top 20 ThemeId,name from Lotgame_Theme WHERE Name LIKE '%' + parm + '%')as t1)
因为,1、SQL语句默认的就是第一列的升序排列的啦
2、外面的一层模糊是多余的
SELECT top 10 * FROM vi_Theme 不管怎么样这条语句都会执行..也就是说..top 10 应该是 ThemeId > 子查询中的Max(ThemeId) and Name like xxx
但是你们的都是在没有进行 Name like xxx这层过虑之后的 ThemeId > - -# 我说乱了..请再看一哈我的SQL 我用你们的SQL执行.和我原来的数据对不上
(SELECT top 30 * from Lotgame_Theme
WHERE Name LIKE '%' + parm + '%' ORDER BY ThemeId ASC)tp order by ThemeId desc再不行?
SELECT MAX(ThemeId) FROM (SELECT top 20 ThemeId from Lotgame_Theme WHERE Name LIKE '%' + parm + '%' ORDER BY ThemeId ASC)as t1)
and Name LIKE '%' + parm + '%'
SELECT top1 ThemeId from Lotgame_Theme WHERE Name LIKE '%' + parm + '%' ORDER BY ThemeId DEC)as t1)
and Name LIKE '%' + parm + '%'
select top 10 * from # where id>
(select max(id) from (select top 20 id from # order by id asc )tp)-- 我的写法 21到30
select top 10 * from (
select top 30 * from # order by id asc)tp order by id desc怎么不对?
写出存储过程
LIKE 写个函数什么的来替代