少见的sql

2023-01-23 19:50:38

1，values 的新用法,出现自2008

SELECT * FROM  table AS a
,,,'qq3')) tem(id,name) ON a.id=tem.id

insert into xxxx values(),(),() 语句，最多可包含1000行，否则报错

The number of row value expressions in the INSERT statement exceeds the maximum allowed number of 1000 row values.

http://blog.csdn.net/downmoon/article/details/5936706

2,新的分页方式，出现自2012，不知道以后会不会流行起来

 ROWS fetc  ROWS only

http://www.cnblogs.com/CareySon/archive/2012/03/09/2387825.html

3，output,貌似从2005就有了。很华丽的功能

用法很多，可以使用的操作有 insert,update,delete,merge

写一种用法：获取批量插入后的自增id

DECLARE @idTb TABLE(iid)
INSERT INTO [table1]
OUTPUT INSERTED.id
INTO @idTb
SELECT id,name FROM [table2]

注意：update statement 的结果集不是存在updated里，是在inserted里。从output的结果集可以看出来，output的update操作其实分为了insert+delete两个结果集

http://www.cnblogs.com/yuananyun/archive/2010/12/04/1896276.html

http://msdn.microsoft.com/en-us/library/ms177564.aspx

4，merge，数据处理，出现自2008

注意，merge要以分号结束，有output的话，也算在merge语句内部

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[source_table]') AND type in (N'U'))
DROP TABLE [dbo].[source_table]
GO
CREATE TABLE [dbo].[source_table](
    ,) NOT NULL,
    ) NULL,
    ) NULL,
    [date] [datetime] NULL
) ON [PRIMARY]

GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[target_table]') AND type in (N'U'))
DROP TABLE [dbo].[target_table]
GO
CREATE TABLE [dbo].[target_table](
    ,) NOT NULL,
    ) NULL,
    ) NULL,
    [date] [datetime] NULL
) ON [PRIMARY]

GO

INSERT INTO dbo.source_table
        ( code, name, date )
',
          'test1',
          '2014-05-23 07:34:23'
          )
          ,
          ( ',
          'test2',
          '2014-05-25 07:34:23'
          )

INSERT INTO dbo.target_table
        ( code, name, date )
',
          'test11',
          '2014-05-23 07:34:23'
          )
          ,
          ( ',
          'test3',
          '2014-05-25 07:34:23'
          )          

--确定目标表
Merge Into target_table t
--用编码匹配
using source_table s on t.code=s.code
--如果编码相同，则更新目标表的名称
When Matched and t.name<>s.name Then Update set t.name=s.name
--如果目标表中不存在，则从数据源插入目标表
When Not Matched By Target Then Insert (name,code,date) values (s.name,s.code,s.date)
--如果目标表的行在源表中不存在，则删除目标表的行
When Not Matched By Source Then DELETE
--输出所有操作的类型和数据
output $action as [action],inserted.*,DELETED.*;
--返回上个Merge语句影响的行数
select @@ROWCOUNT as Count1,ROWCOUNT_BIG() as Count2

http://blog.csdn.net/downmoon/article/details/5947360

5，top 接受参数，可以按照百分比取数，还可以用在update和delete上

;
SELECT TOP (@top) * FROM table
 PERCENT * FROM  table

6,拼接字符串

DECLARE @sql VARCHAR(max) = '';
SELECT   @sql=(@sql + filtersql + ' uinon ') FROM dbo.MyTable
)
exec @sql

7,CTE，也就是with as 实现递归

--这个例子是用来遍历树结构的，有两个方向，向上或向下：
--向下 ： 找一个节点所有的子节点；向上 : 找一个节点的从它自己直到根节点的所有经过点，也就是路径
; --with 前边必须带有 ；
with resTb(iid,pid,name)--这是结果表，列名最好和原始表的列名区分一下
as
(
     --这是递归的起始节点
    union all
    select SrcTable.id,SrcTable.parentId,SrcTable.name from SrcTable
    inner join resTb on SrcTable.id = resTb.pid --这是向上，向下：SrcTable.parentId = resTb.iid
)

select * from     resTb -- 必须在最后使用这个表

CTE 可用于：

创建递归查询。有关详细信息，请参阅使用公用表表达式的递归查询。
在不需要常规使用视图时替换视图，也就是说，不必将定义存储在元数据中。
启用按从标量嵌套 select 语句派生的列进行分组，或者按不确定性函数或有外部访问的函数进行分组。
在同一语句中多次引用生成的表。

使用 CTE 可以获得提高可读性和轻松维护复杂查询的优点。查询可以分为单独块、简单块、逻辑生成块。之后，这些简单块可用于生成更复杂的临时 CTE，直到生成最终结果集。可以在用户定义的例程（如函数、存储过程、触发器或视图）中定义 CTE。

公用表表达式 (CTE) 具有一个重要的优点，那就是能够引用其自身，从而创建递归 CTE

Note:

一、CTE其实是面向对象的，运行的基础是CLR。一个很好的说明是With查询语句中是区分字段的大小写的。即"C_Code"和"c_Code"是不一样的，后者会报错。这与普通的SQL语句不同。

二、这个应用示例重在简化业务逻辑，即便是性能不佳，但对临时表\表变量\游标等传统处理方式是一种业务层次上的简化或者说是优化。

http://www.cnblogs.com/downmoon/archive/2009/10/23/1588405.html

码农公寓

相关文章