sql常用脚本

sql

常用sql脚本汇总

作者

不止BI

发布于

2024年3月2日

1 SQL server

1.1 生成日期列

代码

WITH DateSequence AS (
    SELECT convert(char(10),'2024-01-01',120) AS DateValue
    UNION ALL
    SELECT convert(char(10),dateadd(day, 1, DateValue),120)  AS DateValue
    FROM DateSequence
    WHERE DateValue < '2024-08-13'
)
SELECT DateValue FROM DateSequence
OPTION (MAXRECURSION 0);-- 允许无限递归，或设置一个足够大的数字以避免栈溢出

1.2 跨月数据展开

代码

DROP table if exists #DateRange;
-- 创建示例数据表  
CREATE TABLE #DateRange (  
    from_date DATETIME,  
    to_date DATETIME,  
    status VARCHAR(50)  -- 假设status是字符型  
);  

-- 插入示例数据  
INSERT INTO #DateRange (from_date, to_date, status)  
VALUES   
    ('2021-05-28 12:00:00.000', '2021-06-21 19:00:00.000', 'Active'),  
    ('2023-12-21 19:00:00.000', '2024-3-22 11:00:00.000', 'NoActive');  




-- 递归 CTE来展开数据  
WITH CTE_DateRange AS (  
    SELECT   
        from_date,  
        DATEADD(MONTH, DATEDIFF(MONTH, 0, from_date) + 1, 0) AS next_month_start,  
        to_date,  
        status  
    FROM   
        #DateRange  
    UNION ALL  
    SELECT   
        next_month_start AS from_date,  
        dateadd(month, 1, next_month_start) AS next_month_start,  
        to_date,  
        status  
    FROM   
        CTE_DateRange  
    WHERE   
        next_month_start < to_date  
)  
SELECT  
    from_date,  
    CASE   
        WHEN next_month_start < to_date   
        THEN next_month_start 
        ELSE to_date  
    END AS to_date,  
    status  
FROM   
    CTE_DateRange  
ORDER BY   
    from_date;

1.3 拆分字符串

将字符串拆分为多行

代码

SELECT value AS Number
FROM STRING_SPLIT('123,44,55', ',')

将列值中的字符串拆分为多行

代码

SELECT
    yt.ID,
    ss.value AS Number
FROM
    YourTable yt
CROSS APPLY
    STRING_SPLIT(yt.CommaSeparatedValues, ',') ss

CROSS APPLY

CROSS APPLY 是 SQL Server 中的一个操作符，它用于将表值函数或表值表达式应用到外部查询的每一行，并返回结果集。它类似于 JOIN 操作，但它对每一行都执行右侧表达式，并只返回与外部查询匹配的行。

在 CROSS APPLY 的上下文中，左侧的表（或表达式）被称为外部输入，而右侧的表值函数或表达式被称为内部输入。CROSS APPLY 会对外部输入的每一行执行内部输入，并只返回那些内部输入返回结果的行。

这里有几个关键点：

一对一关系：CROSS APPLY 会对外部输入的每一行执行内部输入，这意味着它建立的是一对一的关系。
过滤作用：如果内部输入对于某行没有返回结果，那么这一行将不会出现在最终的结果集中。这与 OUTER APPLY 不同，后者即使内部输入没有返回结果，也会返回外部输入的行，并将内部输入的结果设置为 NULL。
性能：由于 CROSS APPLY 会对每一行执行内部操作，它可能会对性能产生影响，特别是当内部操作复杂或者外部输入的数据量很大时。
使用场景：CROSS APPLY 常用于需要对每一行数据进行某种转换或计算的场景，例如，将字符串拆分为多行，或者对每一行的数据执行复杂的函数调用。

下面是一个简单的例子，说明 CROSS APPLY 的用法：

SELECT     
  e.EmployeeID,
  e.Name,     
  d.DivisionName FROM     
  Employees e 
CROSS APPLY dbo.GetDivisionName(e.DivisionID) d

在这个例子中，Employees 表有一个 DivisionID 列，我们想要获取每个员工对应的部门名称。dbo.GetDivisionName 是一个表值函数，它接受 DivisionID 作为参数，并返回部门名称。CROSS APPLY 会对 Employees 表的每一行执行这个函数，并返回结果。

如果 dbo.GetDivisionName 函数对于某个 DivisionID 没有找到对应的部门名称，那么这一行就不会出现在最终的结果集中。如果你想要即使没有找到部门名称也返回员工信息，可以使用 OUTER APPLY 替代 CROSS APPLY。

1.4 Openjson

代码

--simple cross apply example
DECLARE @JSON NVARCHAR(MAX) = N'[
{
"OrderNumber":"SO43659",
"OrderDate":"2011-05-31T00:00:00",
"AccountNumber":"AW29825",
"ItemPrice":2024.9940,
"ItemQuantity":1
},
{
"OrderNumber":"SO43661",
"OrderDate":"2011-06-01T00:00:00",
"AccountNumber":"AW73565",
"ItemPrice":2024.9940,
"ItemQuantity":3
}
]'


SELECT root.[key] AS [Order],TheValues.[key], TheValues.[value]
FROM OPENJSON ( @JSON ) AS root
CROSS APPLY OPENJSON ( root.value) AS TheValues
where TheValues.[Key] = 'OrderNumber'

2 MySQL

2.1 生成日期列

代码

SET CTE_MAX_RECURSION_DEPTH = 3000;
WITH RECURSIVE DateSequence AS (  
    SELECT '2024-01-01' AS DateValue  
    UNION ALL  
    SELECT DATE_ADD(DateValue, INTERVAL 1 DAY)  
    FROM DateSequence  
    WHERE DateValue < '2024-08-13'  
)  
SELECT DateValue FROM DateSequence;

回到顶部