常用SQL总结

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

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

二 常用的的集中分页方式(30~40条数据)

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

或下面这种形式

SELECT * 
FROM (SELECT ROW_NUMBER() OVER (ORDER BY ID asc)AS rowId,* FROM ANIC_Compound) AS T 
WHERE T.rowId BETWEEN 11 AND 20

 

三 CET 的入门

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)

 5CET递归,取出一个树来, 取出根节点是0的,联合上父节点被取出来的子节点

WITH cr as
  (SELECT Id, Name, ParentId
   FROM SPProductClass
   WHERE ParentId = 0
   UNION ALL SELECT s.Id, s.Name, s.ParentId
   FROM SPProductClass s
   INNER JOIN cr ON s.ParentId = cr.Id)
SELECT *
FROM cr;