常用SQL总结

一 插入多行数据的几种方式

1 insert into target_table(column1,column2) select column1,5 from source_table;
target_table 表存在的时候
2 select * into target_table from source_table;
target_table 表不存在的时候
3 select * identity(int,1,1) As sid target_table from source_table
target_table 表不存在的时候, 插入标识符, 但是如果source_table 也有唯一标识符的时候,就不能把那个也插进去了

二

1 不在前30条的前10条

SELECT top 10 *
FROM dbo.ActivityComment
WHERE id NOT IN
    ( SELECT top 30 id
     FROM [dbo].[ActivityComment] )

2 id大于第30条的id,的前10条

SELECT top 10 *
FROM dbo.ActivityComment
WHERE id >
    (SELECT max(id)
     FROM
       (SELECT top 30 id
        FROM dbo.ActivityComment
        ORDER BY id) AS ids)
ORDER BY id

3 利用 ROW_NUMBER() over(order by id) as num 来实现分页, 取出rumber 大于30的前10条

SELECT top 10 *
FROM
  ( SELECT ROW_NUMBER() over(ORDER BY id) AS num, * FROM dbo.SPProduct) AS a
WHERE num > 30

三

1,用子查询解决

SELECT *
FROM dbo.SPProduct
WHERE id NOT in
    (SELECT id
     FROM dbo.SPProduct
     WHERE name LIKE ‘%30%‘);

2,用存储过程解决

 定义表变量
 declare @t table(SPProduct int) 
 把子查询的结果存入表变量
 insert into @t(SPProduct)(select id from dbo.SPProduct where name like ‘%30%‘)
 再做外层查询
 select * from dbo.SPProduct where id not in (select * from @t)

3 CTE方式

  with cr as (select id from SPProduct where name like ‘%30%‘)
  select * from SPProduct where id not in (select * from cr)

4多个CET

  with cr1(id) as (select id from SPProduct where name like ‘%30%‘ union all select id from SPProduct where name like ‘%20%‘)
  select * from SPProduct where id not in (select * from cr1)