sql server 分页sql高效写法(mysql limit替代写法)

 mysql, sqlserver  sql server 分页sql高效写法(mysql limit替代写法)已关闭评论
3月 182021
 

习惯了mysql, 切到sql server真TM不习惯,处处觉得sql server的使用太垃圾了, 完全是拉低开发人员效率的产品!

今天谈下sql server为人诟病的分页,OMG , 在mysql里使用 limit n,m就能解决的事情,在sql server里动作就大了,常见的使用 NOT in 语法(实在无法想象这样的查询效率),或者使用自己定义一个存储过程的方式(但这种方式条件又不够灵活), 推荐一个相对比较高效并且相对简单的写法如下

SELECT TOP 页大小 * FROM( SELECT ROW_NUMBER() OVER (ORDER BY id) AS RowNumber,* FROM table1 )as A 
WHERE RowNumber > 页大小*(页数-1)

注解:

  • 首先利用Row_number()为table1表的每一行添加一个行号,给行号这一列取名’RowNumber’ ,在over()方法中将table1按’id’做了升序排列
  • 然后将’RowNumber’列 与table1表的所有列 形成一个表A
  • 重点在where条件。假如当前页(currentPage)是第2页,每页显示10个数据(pageSzie)。那么第一页的数据就是第11-20条

mysql的on duplicate key update语句在sql server中的替代写法

 sqlserver, 数据库  mysql的on duplicate key update语句在sql server中的替代写法已关闭评论
3月 162021
 

mysql在处理唯一主键记录时可以一个语句实现没有记录时插入有记录时更新, 只要使用“insert …… on duplicate key update …. ”,非常简洁高效,oracle 、 DB2 都有类似的语法可以实现相同的功能,但坑爹的是sql server没有对应的语句,但可以使用下面的语句实现相同的功能,但稍许复杂些:

  • 1. 对于sql server 2008及以后的版本,可以使用merge语法, 可以参考下面的实例用法:
CREATE TABLE #mytable(COL_A VARCHAR(10), COL_B VARCHAR(10), COL_C VARCHAR(10), COL_D VARCHAR(10))
INSERT INTO #mytable VALUES('1','0.1', '0.2', '0.3'); --<These are the values we'll be updating
SELECT * FROM #mytable --< Starting values (1 row)
--< 以上是测试数据,主要注意下面的MERGE语句用法
    MERGE #mytable AS target --< This is the target we want to merge into
    USING ( --< This is the source of your merge. Can me any select statement
        SELECT '1' AS VAL_A,'1.1' AS VAL_B, '1.2' AS VAL_C, '1.3' AS VAL_D --<These are the values we'll use for the update. (Assuming column COL_A = '1' = Primary Key)
        UNION
        SELECT '2' AS VAL_A,'2.1' AS VAL_B, '2.2' AS VAL_C, '2.3' AS VAL_D) --<These values will be inserted (cause no COL_A = '2' exists)
        AS source (VAL_A, VAL_B, VAL_C, VAL_D) --< Column Names of our virtual "Source" table
    ON (target.COL_A = source.VAL_A) --< This is what we'll use to find a match "JOIN source on Target" using the Primary Key
    WHEN MATCHED THEN --< This is what we'll do WHEN we find a match, in your example, UPDATE COL_D = VALUES(COL_D);
        UPDATE SET
            target.COL_B = source.VAL_B,
            target.COL_C = source.VAL_C,
            target.COL_D = source.VAL_D
    WHEN NOT MATCHED THEN --< This is what we'll do when we didn't find a match
    INSERT (COL_A, COL_B, COL_C, COL_D)
    VALUES (source.VAL_A, source.VAL_B, source.VAL_C, source.VAL_D)
    --OUTPUT deleted.*, $action, inserted.* --< Uncomment this if you want a summary of what was inserted on updated.
    --INTO #Output  --< Uncomment this if you want the results to be stored in another table. NOTE* The table must exists
    ;

--< MERGE后测试数据显示
SELECT * FROM #mytable --< Ending values (2 row, 1 new, 1 updated)
  • 2. 对于sql server 2008以前的版本可以参考下面语句的使用方法:
update mytable
  set col_d = 'val_d'
  where col_a = 'val_a'
    and col_b = 'val_b'
    and col_c = 'val_c';

insert into mytable (col_a, col_b, col_c, col_d)
  select 'val_a','val_b', 'val_c', 'val_d'
  where not exists (select * 
    from mytable with (serializable) 
    where col_a = 'val_a'
      and col_b = 'val_b'
      and col_c = 'val_c'
      );

参考:https://stackoverflow.com/questions/27076348/equivalent-of-mysql-on-duplicate-key-update-in-sql-server

mybatis下limit有参数计算时的写法

 开发  mybatis下limit有参数计算时的写法已关闭评论
4月 232020
 

mybatis下limit如果想做分页,对limit做参数计算时:

错误的写法:

<select id=”queryMyApplicationRecord” parameterType=”MyApplicationRequest” resultMap=”myApplicationMap”>
SELECT
a.*,
FROM
tb_user a
WHERE 1=1
<if test=”ids != null and ids.size()!=0″>
AND a.id IN
<foreach collection=”ids” item=”id” index=”index”
open=”(” close=”)” separator=”,”>
#{id}
</foreach>
</if>
<if test=”statusList != null and statusList.size()!=0″>
AND a.status IN
<foreach collection=”statusList” item=”status” index=”index”
open=”(” close=”)” separator=”,”>
#{status}
</foreach>
</if>
ORDER BY a.create_time desc
LIMIT (#{pageNo}-1)*#{pageSize},#{pageSize}; // 错误
</select>
在MyBatis中LIMIT之后的语句不允许的变量不允许进行算数运算,会报错。

正确的写法一:
<select id=”queryMyApplicationRecord” parameterType=”MyApplicationRequest” resultMap=”myApplicationMap”>
SELECT
a.*,
FROM
tb_user a
WHERE 1=1
<if test=”ids != null and ids.size()!=0″>
AND a.id IN
<foreach collection=”ids” item=”id” index=”index”
open=”(” close=”)” separator=”,”>
#{id}
</foreach>
</if>
<if test=”statusList != null and statusList.size()!=0″>
AND a.status IN
<foreach collection=”statusList” item=”status” index=”index”
open=”(” close=”)” separator=”,”>
#{status}
</foreach>
</if>
ORDER BY a.create_time desc
LIMIT ${(pageNo-1)*pageSize},${pageSize}; (正确)
</select>
正确的写法二:(推荐)
<select id=”queryMyApplicationRecord” parameterType=”MyApplicationRequest” resultMap=”myApplicationMap”>
SELECT
a.*,
FROM
tb_user a
WHERE 1=1
<if test=”ids != null and ids.size()!=0″>
AND a.id IN
<foreach collection=”ids” item=”id” index=”index”
open=”(” close=”)” separator=”,”>
#{id}
</foreach>
</if>
<if test=”statusList != null and statusList.size()!=0″>
AND a.status IN
<foreach collection=”statusList” item=”status” index=”index”
open=”(” close=”)” separator=”,”>
#{status}
</foreach>
</if>
ORDER BY a.create_time desc
LIMIT #{offSet},#{limit}; (推荐,代码层可控)
</select>
分析:方法二的写法,需要再请求参数中额外设置两个get函数,如下:
@Data
public class QueryParameterVO {

private List<String> ids;

private List<Integer> statusList;

// 前端传入的页码
private int pageNo; // 从1开始

// 每页的条数
private int pageSize;

// 数据库的偏移
private int offSet;

// 数据库的大小限制
private int limit;

// 这里重写offSet和limit的get方法
public int getOffSet() {
return (pageNo-1)*pageSize;
}

public int getLimit() {
return pageSize;
}
}