sql server 新语法 收藏 2019 2022

1、行转列 PIVOT函数，行转列，列转换UNPIVOT

 select brlx as '姓名',西药费,中成药,中草药 from cc_mzjzzjb_zy a
PIVOT
(
max(a.ysje) for a.zy in(西药费,中成药,中草药)
)b;

select *
from ShoppingCart as C 
PIVOT(count(TotalPrice) FOR [Week] IN([1],[2],[3],[4],[5],[6],[7])) AS T 

https://www.cnblogs.com/linJie1930906722/p/6036714.html
https://www.cnblogs.com/wlsandwho/p/4423956.html
https://www.cnblogs.com/ylbtech/p/8028808.html
https://www.cnblogs.com/sword-successful/p/4814840.html

2.SQL XML

左外连接  LEFT OUTER JOIN 

 

select dm,ks from  c_dept for xml path//row  字段名 字段值

select dm,ks from c_dept for xml path('')//字段名字段值

select dm,ks from  c_dept for xml path('Dept')

select dm,ks from  c_dept for xml path('row')

select dm,ks from  c_dept for xml raw

select dm,ks from  c_dept for xml auto

 

字符串解析为表

https://www.cnblogs.com/lanyubaicl/p/5671966.html

 

特殊字符用nvarchar类型，插入的时候字符常量前面加N

create table TbN( itemno NVARCHAR(50));

INSERT INTO TbN(itemno) VALUES (N'A02.201†'  );

 

比较简单的生成多行数据类似select union

    SELECT * FROM (VALUES(1),(2),(3)) AS V(A)

A
1
2
3

字符串拼接分解

 SELECT stuff( (SELECT ',' + turnOffice  FROM (
  select ( select dm from c_dept  t where t.ks=items) as turnOffice  FROM dbo.[Split_StrByDelimiter](REPLACE( '内一科->内三科->外一科->骨科->妇科','->',','),',')
  ) A FOR XML PATH('')),1,1,'')  as turnOffice

 

https://blog.csdn.net/sqlserverdiscovery/article/details/79187307

SQL Server 2016新增了string_split函数，专门用来拆分字符串。

SQL Server 2017RTM版，新增函数string_agg

 

sql 2019没有sql debuger功能了，sql调试功能。T-SQL Debugger 没有这个太坑人了。

https://feedback.azure.com/forums/908035-sql-server/suggestions/35691865-dont-remove-debugging-from-ssms-18-0

https://www.sqlservercentral.com/forums/topic/debug-is-not-visible-in-sql-management-studio

ssms

https://learn.microsoft.com/zh-cn/sql/ssms/download-sql-server-management-studio-ssms?redirectedfrom=MSDN&view=sql-server-ver16

ssms2019最新版本

 https://download.microsoft.com/download/9/f/8/9f8197f4-0f71-42a3-8717-b2817c77b820/SSMS-Setup-CHS.exe

sql server2022

https://learn.microsoft.com/zh-cn/sql/sql-server/what-s-new-in-sql-server-2022?view=sql-server-ver16

https://learn.microsoft.com/zh-cn/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-ver16

https://aka.ms/ssmsfullsetup

要安装个 SQL Server Data Tools (SSDT)

SQL Server Data Tools 2015
找不到方法:“Boolean Microsoft.Data.Tools.Components.Diagnostics.SqlTracer.TraceException(Microsoft.Data.Tools.Components.Diagnostics.SqlTraceId, System.Exception)”。
安装后提示这个，用不了，还是存储过程调试不了
https://learn.microsoft.com/zh-cn/sql/ssdt/download-sql-server-data-tools-ssdt?source=recommendations&view=sql-server-ver16

 

行号  ROW_NUMBER

SELECT  ROW_NUMBER() over( order by 日期) as rows,
姓名,日期  
from  t

 

--禁止删除触发器

Create TRIGGER [dbo].[Trigger_delete]
   ON  [dbo].a1
   instead of delete
AS

BEGIN    
       RAISERROR  ( '不能删除 ' ,  --  Message text.
                 16 ,  --  Severity.
                 1   --  State.
               );
END

 

 

select SCOPE_IDENTITY() as id

    SELECT newid() --生成36位的GUID

    SELECT REPLACE(newid(), '-', '')  -- 生成32 位的GUID



SQL 2022新语法
 https://docs.microsoft.com/en-us/sql/t-sql/queries/table-value-constructor-transact-sql?view=sql-server-ver16
https://docs.microsoft.com/en-us/sql/t-sql/queries/from-transact-sql?view=sql-server-ver16

SELECT * FROM (VALUES (1, 2), (3, 4), (5, 6), (7, 8), (9, 10) ) AS MyTable(a, b);

INSERT INTO dbo.Test ([Value])  
  SELECT drvd.[NewVal]
  FROM   (VALUES (0), (1), (2), (3), ..., (5000)) drvd([NewVal]);

-- Used in an inner join to specify values to return. 
SELECT ProductID, a.Name, Color FROM Production.Product AS a INNER JOIN (VALUES ('Blade'), ('Crown Race'), ('AWC Logo Cap')) AS b(Name) ON a.Name = b.Name; 

INSERT INTO Sales.MySalesReason   
VALUES ('Recommendation','Other'), ('Advertisement', DEFAULT), (NULL, 'Promotion');  
  

INSERT INTO dbo.t VALUES (1,'a'), (2, CONVERT(CHAR,1));  

INSERT INTO dbo.MyProducts (Name, ListPrice)  
VALUES ('Helmet', 25.50),  
       ('Wheel', 30.00),  
       ((SELECT Name FROM Production.Product WHERE ProductID = 720),  
        (SELECT ListPrice FROM Production.Product WHERE ProductID = 720));  
GO  

-- This statement fails because the third values list contains multiple columns in the subquery.  
INSERT INTO dbo.MyProducts (Name, ListPrice)  
VALUES ('Helmet', 25.50),  
       ('Wheel', 30.00),  
       (SELECT Name, ListPrice FROM Production.Product WHERE ProductID = 720);  
GO  

DECLARE @json NVARCHAR(MAX);
SET @json=N'{"person":{"info":{"name":"John", "name":"Jack"}}}';

SELECT value
  FROM OPENJSON(@json,'$.person.info');

得到结果集

SELECT PersonID,FullName,
  JSON_QUERY(CustomFields,'$.OtherLanguages') AS Languages
FROM Application.People

JSON转为数据集

SELECT *
FROM OPENJSON(@json) WITH (...)
https://learn.microsoft.com/zh-cn/sql/relational-databases/json/json-data-sql-server?view=sql-server-ver16

 2023.2.9
https://learn.microsoft.com/zh-cn/sql/ssdt/how-to-debug-stored-procedures?view=sql-server-ver16
安装了VS2022，调试SQL可以了，但是调试存储过程还不行。
VS项目>SQL>使用调试器执行>
到了执行存储过程的语句， 逐行F10，逐语句F11,都进入不了存储过程里


https://learn.microsoft.com/zh-cn/sql/ssdt/how-to-debug-stored-procedures?view=sql-server-ver16