分类目录归档：MSSQL

Linux、MSSQL

安装Linux版本的SQL Server

开放的态度让M$有了更多的想象空间，比如Linux版的Office和SQL Server。

下面介绍Linux下安装SQL Server的方法：

微软的安装资源库地址：https://packages.microsoft.com/config/rhel/7/

其中mssql-server-2017.repo 是安装包

prod.repo是工具包

curl https://packages.microsoft.com/config/rhel/7/mssql-server-2017.repo > /etc/yum.repos.d/mssql-server-2017.repo

然后

yum install -y mssql-server
/opt/mssql/bin/mssql-conf setup

安装工具：

curl https://packages.microsoft.com/config/rhel/7/prod.repo > /etc/yum.repos.d/mssql-server-tools.repo
yum install mssql-tools
sqlcmd -S localhost -U SA

命令行（/opt/mssql-tools/bin/目录）：

[root@souqu ~]# sqlcmd -S 127.0.0.1 -U sa
Password:
1>

 

MSSQL、数据库

MS SQL的CTE及MERGE用法

1条评论

CTE应用于 >=SQL Server 2005

MERGE应用于 >= SQL Server 2008

 

CTE: Common table expression

with CTE_NAME [(column_name [,…n] )] as

(

sql_query_script

)

select … from CTE_NAME

应用场景1：分页

with ttt as (

select row_number()  OVER(Order By ID) as n, * from table1

)

select * from ttt where n between 10001 and 10100

应用场景2：递归查询

WITH COL_CTE(Id,Name,ParentId,tLevel )
AS
(
    --基本语句
    SELECT Id,Name,ParentId,0 AS tLevel FROM Col
    WHERE ParentId = 0
    UNION ALL
    --递归语句
    SELECT c.Id,c.Name,c.ParentId,ce.tLevel+1 AS tLevel FROM COL as c 
    INNER JOIN COL_CTE AS ce 
    ON c.ParentId = ce.Id
)

SELECT * FROM COL_CTE
OPTION(MAXRECURSION 2)　　--指定最大递归次数为2

FOR XML PATH用法

select stuff((select top 2 ‘ ‘+DocNo from OrderInfo for xml path(”)),1,1,”)

查询出拼接成一个字符串的数据

用于GROUP更方便

select …… , stuff((select distinct ‘,’ + isnull(子表.字段2,”) from 子表 where 子表.字段1=主表.字段 for xml path(”)),1,1,”)

from 主表

MERGE用法：

把一个表或者查询的结果，合并到另一个表中，可以方便指定insert或者update的字段。

MERGE ProductNew AS d
USING
Product
AS s
ON s.ProductID = d.ProductId
WHEN NOT MATCHED THEN
INSERT( ProductID,ProductName,Price)
VALUES(s.ProductID,s.ProductName,s.Price)
WHEN MATCHED THEN
UPDATE SET d.ProductName = s.ProductName, d.Price = s.Price;

— 如果需要同步删除的数据，需要指定WHEN NOT MATCHED BY SOURCE，即按源数据匹配不到目的数据。如果同步删数据，则表示全量删除，否则会删除目的表中不应该删除的数据。

MERGE ProductNew AS d
USING
Product
AS s
ON s.ProductID = d.ProductId
WHEN NOT MATCHED BY TARGET THEN
INSERT( ProductID,ProductName,Price)
VALUES(s.ProductID,s.ProductName,s.Price)
WHEN NOT MATCHED BY SOURCE THEN
DELETE
WHEN MATCHED THEN
UPDATE SET d.ProductName = s.ProductName, d.Price = s.Price;