sql server 2008 新特性

1.同时插入多行,

insert into a values(2,'C'),(3,'D'),(4,'E');

or 可套查询,如:

insert into a values((select top 1 id from b),(select top 1 name from c))

2.

定义表变量,定义完以后在整个DB中有效,且是持久的,如此可以在存储过程,函数中作为参数传递。

IF (SELECT COUNT(*) FROM sys.table_types
WHERE name = 'TVPParam' AND schema_id = 1) = 0
  CREATE TYPE dbo.TVPParam AS TABLE(ProdCode integer, Qty integer)
GO


3.

以后可以不再使 用union all 来组合查询了。

SELECT * FROM (VALUES('John Smith', 25, 5),('Jane Dow', 36, 6))
 mytable(name, age, score)
GO


4.合并两表结构相同的表,不同也可以,但是意义不大。

MERGE Customers C
  USING QuarterlyUpdate Q
  ON C.customer_id =Q.customer_id
  WHEN MATCHED THEN 
   UPDATE SET 
    C.customer_name = Q.customer_name,
    C.policy_type = Q.policy_type,
    C.deductable = Q.deductable,
    C.number_of_claims += new_claims
  WHEN NOT MATCHED BY SOURCE THEN
    DELETE
  WHEN NOT MATCHED BY TARGET THEN
    INSERT VALUES(customer_name, policy_type, deductable, new_claims)
    OUTPUT $action,
      INSERTED.customer_id,
      INSERTED.customer_name AS [New Name],
      INSERTED.policy_type AS [New Policy],
      INSERTED.deductable AS [New Deductable],
      INSERTED.number_of_claims AS [New Number Of Claims],
      DELETED.customer_id AS [Original Customer Id],
      DELETED.customer_name AS [Original Name],
      DELETED.policy_type AS [Original Policy],
      DELETED.deductable AS [Original Deductable],
      DELETED.number_of_claims AS [Original Number Of Claims];
GO



按多组一次执行分组。

CREATE TABLE T (
 a int,
 b int,
 c int,
 x int
)
GO

INSERT T VALUES(1,2,3,8)
INSERT T VALUES(1,2,4,1)
INSERT T VALUES(1,2,5,5)
INSERT T VALUES(1,3,4,9)
GO
SELECT a, b, c, SUM(x) AS sumx FROM T
GROUP BY GROUPING SETS (a, (b), (a, b, c))
GO

a    b    c    sumx
1    2    3    8
1    2    4    1
1    2    5    5
N    2    N    14
1    3    4    9
N    3    N    9
1    N    N    23

其效果与下面传统的写法相同,但是效率会提高。

SELECT a, NULL AS b, NULL AS c, SUM(x) AS sumx FROM T
GROUP BY a
UNION ALL
SELECT NULL AS a, b, NULL AS c, SUM(x) AS sumx FROM T
GROUP BY b
UNION ALL
SELECT a, b, c, SUM(x) AS sumx FROM T
GROUP BY a, b, c
GO


下面查询列出所有的组合,cube 解释为立方体。

SELECT a, b, c, SUM(x)
FROM T
GROUP BY a, b, c
WITH CUBE



评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值