主页 / dba / 问题 / 318298
Accepted
omerix
Asked: 2022-10-18 00:21:54 +0800 CST

Sql Server 中是否有类似模板的表达式？

  • 772

我将sql中的别名值放入一个字符串语句中，并不断生成一个带有replace的新字符串。

但是我必须做很多以这种方式嵌套的“替换”。

string 子句中的 {{name}} 实际上是 sql 子句中的别名

有没有办法定义 sql 和模板子句并使其自动化？

样本：

CREATE TABLE [dbo].[message](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [name] [nvarchar](50) NULL,
    [quantity] [float] NULL,
    [delivery] [datetime] NULL,
    [status] [tinyint] NULL,
)
INSERT [dbo].[message] ( [name], [quantity], [delivery], [status]) VALUES ( N'Omer', 124, CAST(N'2022-10-18T00:00:00.000' AS DateTime), 0)
INSERT [dbo].[message] ( [name], [quantity], [delivery], [status]) VALUES ( N'Jacob', 548, CAST(N'2022-11-05T00:00:00.000' AS DateTime), 0)
INSERT [dbo].[message] ([name], [quantity], [delivery], [status]) VALUES ( N'Hasan', 56454, CAST(N'2022-08-09T00:00:00.000' AS DateTime), 1)
INSERT [dbo].[message] ( [name], [quantity], [delivery], [status]) VALUES ( N'Hans', 548, CAST(N'2023-11-01T00:00:00.000' AS DateTime), 0)

4 rows affected

select * from message where status=0
ID 姓名 数量 送货 地位
1 奥马尔 124 2022-10-18 00:00:00.000 0
2 雅各布 548 2022-11-05 00:00:00.000 0
4 汉斯 548 2023-11-01 00:00:00.000 0 
declare @template nvarchar(max)='Hello, {{name}} todays order quantity {{quantity}} this order must be sent by the latest {{delivery}}.'
print @template

select 
replace(
replace(
replace(@template
,'{{name}}',name)
,'{{quantity}}',quantity)
,'{{delivery}}',format(delivery,'dd.MM.yyy'))
from message where status=0
（无列名）
您好，Omer 今天的订单数量 124 此订单必须在 2022 年 10 月 18 日之前发出。
您好，Jacob 今天订单数量 548 此订单必须在 2022 年 11 月 5 日之前发出。
您好，汉斯今天的订单数量 548 此订单必须在 2023 年 11 月 1 日之前发出。 
Hello, {{name}} todays order quantity {{quantity}} this order must be sent by the latest {{delivery}}.


declare @template nvarchar(max)='Hello, {{name}} todays order quantity {{quantity}} this order must be sent by the latest {{delivery}}.'
print @template

declare @sqlString nvarchar(max)='select name,quantity,format(delivery,''dd.MM.yyy'') as [delivery] from message where status=0'
print @sqlString


Hello, {{name}} todays order quantity {{quantity}} this order must be sent by the latest {{delivery}}.
select name,quantity,format(delivery,'dd.MM.yyy') as [delivery] from message where status=0


/*
template embed code

*/

小提琴

sql-server

1 个回答

  1. Best Answer

    字符串格式化最好在应用程序代码表示层而不是 T-SQL 中完成。让数据库负担此类任务会限制可伸缩性并影响性能。

    看来您尝试小提琴的目标不仅是避免嵌套REPLACE函数，而且是一种通用技术，可以使用查询中的列名来指示模板中要替换的值（即嗅探列名）。

    T-SQL 递归技术将避免嵌套REPLACE函数，但性能会更差。下面的示例使用递归 CTE 和 JSON（而不是您小提琴中的 XML）。我敢肯定这里有改进的余地，但它确实有效。

    DECLARE @TemplateString nvarchar(MAX) = N'Hello, {{name}} todays order quantity {{quantity}} this order must be sent by the latest {{delivery}}.';
WITH 
    FormattedValues AS (
        SELECT (
            SELECT 
                  m.id
                , m.name
                , FORMAT(m.quantity, 'N2') AS quantity
                , FORMAT(m.delivery, 'dd.MM.yyy') AS delivery
            FROM dbo.message AS m
            FOR JSON AUTO
            ) AS FormattedValues_json
    )
    ,ColumnValues AS (
        SELECT 
             JSON_VALUE(FormattedValues_json.value, '$.id') AS id
            ,columns.[key] AS ColumnName 
            ,columns.[value] AS ColumnValue
            ,ROW_NUMBER() OVER(PARTITION BY JSON_VALUE(FormattedValues_json.value, '$.id') ORDER BY (SELECT 0)) AS rownum
        FROM FormattedValues
        CROSS APPLY OPENJSON(FormattedValues_json) AS FormattedValues_json
        CROSS APPLY OPENJSON(FormattedValues_json.value) AS columns
    )
    ,ReplacedValues AS (
        SELECT
              id
            , REPLACE(@TemplateString, '{{' + ColumnName + '}}', ColumnValue) AS ReplacedString
            , ColumnName
            , ColumnValue
            , rownum
        FROM ColumnValues
        WHERE rownum = 1
        UNION ALL
        SELECT
              cv.id
            , REPLACE(rv.ReplacedString, '{{' + cv.ColumnName + '}}', cv.ColumnValue) AS ReplacedString
            , cv.ColumnName
            , cv.ColumnValue
            , rv.rownum + 1
        FROM ReplacedValues AS rv
        JOIN ColumnValues AS cv ON cv.id = rv.id AND cv.rownum = rv.rownum + 1
    )
SELECT ReplacedString
FROM ReplacedValues
WHERE rownum = (SELECT MAX(rownum) FROM ColumnValues); --final string
    • 1

相关问题