我在撰寫 SQL 查詢時遇到問題。
我有一個包含以下列的資料表。
資料表1
id notes
----------------------------------------------------------------
1 The Organization
1 develop document disseminate to {{param = "ac-1_prm_1"}}
2 develop document to {{param = "ac-1_prm_2"}}
2 Test
資料表引數
parameterid value. Id
-------------------------------—————----------------
ac-1_prm_1 apple doc. 1
ac-1_prm_2 google doc. 1
ac-1_prm_3 facebook doc. 2
我需要創建一個final_notes列：
id notes final_notes
---------------------------------------------------------------------------------------------
1 The Organization The Organization
1 develop document disseminate develop document disseminate to apple
to{<!-- -->{param = "ac-1_prm_1"}} doc and google doc
and {<!-- -->{param = "ac-1_prm_2"}}
2 develop document develop document facebook doc
to {<!-- -->{param = "ac-1_prm_3"}}
2 Test Test
Sql 代碼不起作用。
在我的代碼中，我必須對我不想要的值進行硬編碼。
DECLARE @DataTable TABLE (
id int,
notes varchar(1000)
);
INSERT INTO @DataTable VALUES
(1, 'Organization'),
(1, 'develop document disseminate to {{param = "ac-1_prm_1"}} and
{{param = "ac-1_prm_2"}} '),
(2, 'develop document to {{param = "ac-1_prm_3"}}'),
(2, 'test');
DECLARE @DataTableParameter TABLE (
parameterid varchar(100),
[value] varchar(100),
id int
);
INSERT INTO @DataTableParameter VALUES
('ac-1_prm_1', 'apple doc.', 1),
('ac-1_prm_2', 'google doc.', 1),
('ac-1_prm_3', 'facebook doc.', 2)
;WITH CTE AS (
SELECT t1.id, t1.notes, t2.parameterid, t2.value
FROM @DataTable AS t1
INNER JOIN @DataTableParameter AS t2 ON t1.id = t2.id
)
SELECT
t.id,
REPLACE('develop document disseminate to {' STUFF(
(
SELECT ', {' parameterid '}'
FROM CTE
WHERE id = t.id
ORDER BY parameterid
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'),
1,
1,
''
) '}', ',', ' and ') AS notes,
REPLACE(REPLACE('develop document disseminate to ' STUFF(
(
SELECT ', ' value
FROM CTE
WHERE id = t.id
ORDER BY value
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'),
1,
1,
''
), '.', ''), ',', ' and ') AS final_notes
FROM CTE AS t
GROUP BY t.id;
uj5u.com熱心網友回復：
遞回 CTE 在這里可能會相當笨拙，因為您不能使用TOP并且您還必須僅過濾最終結果。
而是使用表變數或臨時表，并在回圈中更新它。
DECLARE @results TABLE (id int, notes varchar(1000));
INSERT @results (id, notes)
SELECT id, notes
FROM @DataTable dt;
DECLARE @dtp varchar(100), @dtv varchar(100);
WHILE 1=1
BEGIN
SELECT TOP (1)
@dtp = dtp.parameterid,
@dtv = dtp.value
FROM @DataTableParameter dtp
WHERE parameterid > @dtp OR @dtp IS NULL
ORDER BY parameterid;
IF @@ROWCOUNT = 0
BREAK;
UPDATE @results
SET
notes = REPLACE(notes, '{{param = "' @dtp '"}}', @dtv)
FROM @results r
WHERE notes LIKE '%' @dtp '%';
END;
SELECT *
FROM @results;
資料庫<>小提琴
uj5u.com熱心網友回復：
請嘗試以下解決方案。
查詢陳述句
-- DDL and sample data population, start
DECLARE @DataTable TABLE (id int, notes varchar(1000));
INSERT INTO @DataTable VALUES
(1, 'Organization'),
(1, 'develop document disseminate to {{param = "ac-1_prm_1"}} and
{{param = "ac-1_prm_2"}} '),
(2, 'develop document to {{param = "ac-1_prm_3"}}'),
(2, 'test');
DECLARE @DataTableParameter TABLE (parameterid varchar(100), [value] varchar(100), id int);
INSERT INTO @DataTableParameter VALUES
('ac-1_prm_1', 'apple doc.', 1),
('ac-1_prm_2', 'google doc.', 1),
('ac-1_prm_3', 'facebook doc.', 2);
-- DDL and sample data population, end
DECLARE @parameterid varchar(100), @value VARCHAR(100);
DECLARE @RowCount INT = (SELECT COUNT(*) FROM @DataTableParameter);
WHILE @RowCount > 0 BEGIN
SELECT @parameterid = parameterid, @value = value
FROM @DataTableParameter
ORDER BY ID DESC OFFSET @RowCount - 1 ROWS FETCH NEXT 1 ROWS ONLY;
-- do whatever needed, apply any logic, call stored procedures, etc.
UPDATE @DataTable
SET notes = REPLACE(notes, CONCAT('{{param = "', @parameterid, '"}}'), @value);
SET @RowCount -= 1;
END;
-- test
SELECT * FROM @DataTable;
輸出
---- --------------------------------------------------------------
| id | notes |
---- --------------------------------------------------------------
| 1 | Organization |
| 1 | develop document disseminate to apple doc. and google doc. |
| 2 | develop document to facebook doc. |
| 2 | test |
---- --------------------------------------------------------------
uj5u.com熱心網友回復：
這可能是一個奇怪的更新的情況：
CREATE DATABASE testDb_Shnugo;
GO
USE testDb_Shnugo;
GO
--用你的測驗資料創建表
CREATE TABLE dbo.DataTable (id INT, notes VARCHAR(1000));
INSERT INTO dbo.DataTable VALUES
(1, 'The Organization'),
(1, 'develop document disseminate to {{param = "ac-1_prm_1"}} and {{param = "ac-1_prm_2"}} '),
(2, 'develop document to {{param = "ac-1_prm_3"}}'),
(2, 'test');
GO
CREATE TABLE DataTableParameter (parameterid VARCHAR(100), [value] VARCHAR(100), id INT);
INSERT INTO DataTableParameter VALUES
('ac-1_prm_1', 'apple doc.', 1),
('ac-1_prm_2', 'google doc.', 1),
('ac-1_prm_3', 'facebook doc.', 2);
GO
——神奇的地方來了：古怪的更新
CREATE FUNCTION dbo.MultiReplaceDataTableParameters(@inp VARCHAR(1000), @id INT)
RETURNS VARCHAR(1000)
AS
BEGIN
DECLARE @result VARCHAR(1000)=@inp;
SELECT @result = REPLACE(@result,CONCAT('{{param = "',dtp.parameterid,'"}}'),dtp.[value])
FROM dbo.DataTableParameter dtp
WHERE dtp.id=@id;
RETURN @result;
END
GO
- 試試看
SELECT *
,dbo.MultiReplaceDataTableParameters(dt.notes,dt.id) AS ReplacedText
FROM dbo.DataTable dt
ORDER BY dt.id;
GO
--清理（小心真實資料！）
USE master;
GO
DROP DATABASE testDb_Shnugo;
簡而言之的想法：
該古怪的更新通過表的結果，并使用一個運行引數宣告作為輸入和assingment。因此，該值將隨著訪問的任何行而變化。
這允許一次性執行多個操作。
避免這種技術有很好的理由，但在這種情況下它可能會有所幫助。
1 The Organization
1 develop document disseminate to apple doc. and google doc.
2 develop document to facebook doc.
2 test
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/377487.html
標籤：sql sql-server 查询语句 公用表表达式 sql-server-2017
上一篇：如何獲取范圍內的第一個可用數字