我有两个数据表(以下是定义它们的SQL语句)
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'ETab')
DROP TABLE ETab;
GO
CREATE TABLE ETab
([MRN] varchar(20), [LSPEC] varchar(2), [ADT] DATETIME, [SDT] DATETIME, [Source] varchar(20), [Enum] varchar(20));
GO
INSERT INTO ETab ([MRN], [LSPEC], [ADT], [SDT], [Source], [Enum])
VALUES
('HOMECARE', 'HM', CONVERT(datetime, '2017-04-01 00:00:00.000', 20), CONVERT(datetime, '2017-04-30 00:00:00.000', 20), 'PRODPAT', 'HOMEBLD04'),
('HOMECARE', 'HM', CONVERT(datetime, '2017-05-01 00:00:00.000', 20), CONVERT(datetime, '2017-05-31 00:00:00.000', 20), 'PRODPAT', 'HOMEBLD05'),
('HOMECARE', 'HM', CONVERT(datetime, '2017-06-01 00:00:00.000', 20), CONVERT(datetime, '2017-06-30 00:00:00.000', 20), 'PRODPAT', 'HOMEBLD06'),
('HOMECARE', 'HM', CONVERT(datetime, '2017-07-01 00:00:00.000', 20), CONVERT(datetime, '2017-07-31 00:00:00.000', 20), 'PRODPAT', 'HOMEBLD07'),
('HOMECARE', 'HM', CONVERT(datetime, '2017-08-01 00:00:00.000', 20), CONVERT(datetime, '2017-08-31 00:00:00.000', 20), 'PRODPAT', 'HOMEBLD08'),
('HOMECARE', 'HM', CONVERT(datetime, '2017-09-01 00:00:00.000', 20), CONVERT(datetime, '2017-09-30 00:00:00.000', 20), 'PRODPAT', 'HOMEBLD09'),
('HOMECARE', 'HQ', CONVERT(datetime, '2017-04-01 00:00:00.000', 20), CONVERT(datetime, '2017-04-30 00:00:00.000', 20), 'PRODPAT', 'HOMEDRG04HM'),
('HOMECARE', 'HM', CONVERT(datetime, '2017-05-01 00:00:00.000', 20), CONVERT(datetime, '2017-05-31 00:00:00.000', 20), 'PRODPAT', 'HOMEDRG05HM'),
('HOMECARE', 'HM', CONVERT(datetime, '2017-06-01 00:00:00.000', 20), CONVERT(datetime, '2017-06-30 00:00:00.000', 20), 'PRODPAT', 'HOMEDRG06HM'),
('HOMECARE', 'HM', CONVERT(datetime, '2017-07-01 00:00:00.000', 20), CONVERT(datetime, '2017-07-31 00:00:00.000', 20), 'PRODPAT', 'HOMEDRG07HM'),
('HOMECARE', 'HM', CONVERT(datetime, '2017-08-01 00:00:00.000', 20), CONVERT(datetime, '2017-08-31 00:00:00.000', 20), 'PRODPAT', 'HOMEDRG08HM'),
('HOMECARE', 'HM', CONVERT(datetime, '2017-09-01 00:00:00.000', 20), CONVERT(datetime, '2017-09-30 00:00:00.000', 20), 'PRODPAT', 'HOMEDRG09HM'),
('111824', 'UR', CONVERT(datetime, '2017-09-22 00:00:00.000', 20), CONVERT(datetime, '2017-09-22 00:00:00.000', 20), 'OP', 'OP1118240003'),
('111824', 'NL', CONVERT(datetime, '2017-04-19 00:00:00.000', 20), CONVERT(datetime, '2017-04-19 00:00:00.000', 20), 'OP', 'OP1118240001'),
('111824', 'MS', CONVERT(datetime, '2017-06-30 00:00:00.000', 20), CONVERT(datetime, '2017-06-30 00:00:00.000', 20), 'OP', 'OP1118240002'),
('111824', 'MS', CONVERT(datetime, '2017-04-24 00:00:00.000', 20), CONVERT(datetime, '2017-04-24 00:00:00.000', 20), 'IP', 'IP1118240001'),
('111824', 'MS', CONVERT(datetime, '2017-04-28 00:00:00.000', 20), CONVERT(datetime, '2017-04-28 00:00:00.000', 20), 'IP', 'IP1118240005'),
('111824', 'MS', CONVERT(datetime, '2017-04-27 00:00:00.000', 20), CONVERT(datetime, '2017-04-27 00:00:00.000', 20), 'IP', 'IP1118240004'),
('111824', 'MS', CONVERT(datetime, '2017-04-26 00:00:00.000', 20), CONVERT(datetime, '2017-04-26 00:00:00.000', 20), 'IP', 'IP1118240003'),
('111824', 'MS', CONVERT(datetime, '2017-04-25 00:00:00.000', 20), CONVERT(datetime, '2017-04-25 00:00:00.000', 20), 'IP', 'IP1118240002');
GO
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'UTab')
DROP TABLE UTab;
GO
CREATE TABLE UTab
(MRN varchar(20), SIDate DATETIME, LSPEC varchar(2), Source varchar(20), Enum varchar(20), Iteration varchar(20));
GO
INSERT INTO UTab
(MRN, SIDate, LSPEC, Source, Enum, Iteration)
VALUES
('HOMECARE', CONVERT(datetime, '2017-04-20 00:00:00.000', 20), 'HM', 'N', NULL, NULL),
('HOMECARE', CONVERT(datetime, '2017-04-20 00:00:00.000', 20), 'HM', 'N', NULL, NULL),
('HOMECARE', CONVERT(datetime, '2017-04-20 00:00:00.000', 20), 'HM', 'N', NULL, NULL),
('HOMECARE', CONVERT(datetime, '2017-04-20 00:00:00.000', 20), 'HM', 'N', NULL, NULL),
('HOMECARE', CONVERT(datetime, '2017-04-20 00:00:00.000', 20), 'HM', 'N', NULL, NULL),
('HOMECARE', CONVERT(datetime, '2017-04-20 00:00:00.000', 20), 'HM', 'N', NULL, NULL),
('HOMECARE', CONVERT(datetime, '2017-04-30 00:00:00.000', 20), 'HM', 'N', NULL, NULL),
('HOMECARE', CONVERT(datetime, '2017-04-30 00:00:00.000', 20), 'HM', 'N', NULL, NULL),
('HOMECARE', CONVERT(datetime, '2017-04-30 00:00:00.000', 20), 'HM', 'N', NULL, NULL),
('HOMECARE', CONVERT(datetime, '2017-04-20 00:00:00.000', 20), 'HM', 'N', NULL, NULL),
('HOMECARE', CONVERT(datetime, '2017-04-30 00:00:00.000', 20), 'HM', 'N', NULL, NULL),
('HOMECARE', CONVERT(datetime, '2017-04-30 00:00:00.000', 20), 'HM', 'N', NULL, NULL),
('HOMECARE', CONVERT(datetime, '2017-05-17 00:00:00.000', 20), 'HM', 'N', NULL, NULL),
('HOMECARE', CONVERT(datetime, '2017-04-30 00:00:00.000', 20), 'HQ', 'N', NULL, NULL),
('HOMECARE', CONVERT(datetime, '2017-04-30 00:00:00.000', 20), 'HM', 'N', NULL, NULL),
('HOMECARE', CONVERT(datetime, '2017-04-30 00:00:00.000', 20), 'HM', 'N', NULL, NULL),
('HOMECARE', CONVERT(datetime, '2017-05-30 00:00:00.000', 20), 'HM', 'N', NULL, NULL),
('HOMECARE', CONVERT(datetime, '2017-06-01 00:00:00.000', 20), 'HM', 'N', NULL, NULL),
('HOMECARE', CONVERT(datetime, '2017-06-01 00:00:00.000', 20), 'HM', 'N', NULL, NULL),
('HOMECARE', CONVERT(datetime, '2017-06-01 00:00:00.000', 20), 'HM', 'N', NULL, NULL),
('HOMECARE', CONVERT(datetime, '2017-06-01 00:00:00.000', 20), 'HM', 'N', NULL, NULL),
('HOMECARE', CONVERT(datetime, '2017-06-01 00:00:00.000', 20), 'HM', 'N', NULL, NULL),
('HOMECARE', CONVERT(datetime, '2017-06-01 00:00:00.000', 20), 'HM', 'N', NULL, NULL),
('HOMECARE', CONVERT(datetime, '2017-05-30 00:00:00.000', 20), 'HM', 'N', NULL, NULL),
('HOMECARE', CONVERT(datetime, '2017-06-01 00:00:00.000', 20), 'HM', 'N', NULL, NULL),
('HOMECARE', CONVERT(datetime, '2017-06-26 00:00:00.000', 20), 'HM', 'N', NULL, NULL),
('HOMECARE', CONVERT(datetime, '2017-06-26 00:00:00.000', 20), 'HM', 'N', NULL, NULL),
('HOMECARE', CONVERT(datetime, '2017-06-26 00:00:00.000', 20), 'HM', 'N', NULL, NULL),
('HOMECARE', CONVERT(datetime, '2017-06-26 00:00:00.000', 20), 'HM', 'N', NULL, NULL),
('HOMECARE', CONVERT(datetime, '2017-06-26 00:00:00.000', 20), 'HM', 'N', NULL, NULL),
('HOMECARE', CONVERT(datetime, '2017-06-26 00:00:00.000', 20), 'HM', 'N', NULL, NULL),
('HOMECARE', CONVERT(datetime, '2017-06-26 00:00:00.000', 20), 'HM', 'N', NULL, NULL),
('HOMECARE', CONVERT(datetime, '2017-06-26 00:00:00.000', 20), 'HM', 'N', NULL, NULL),
('HOMECARE', CONVERT(datetime, '2017-06-26 00:00:00.000', 20), 'HM', 'N', NULL, NULL),
('HOMECARE', CONVERT(datetime, '2017-06-26 00:00:00.000', 20), 'HM', 'N', NULL, NULL),
('HOMECARE', CONVERT(datetime, '2017-06-26 00:00:00.000', 20), 'HM', 'N', NULL, NULL),
('HOMECARE', CONVERT(datetime, '2017-06-26 00:00:00.000', 20), 'HM', 'N', NULL, NULL),
('HOMECARE', CONVERT(datetime, '2017-06-26 00:00:00.000', 20), 'HM', 'N', NULL, NULL),
('HOMECARE', CONVERT(datetime, '2017-06-26 00:00:00.000', 20), 'HM', 'N', NULL, NULL),
('HOMECARE', CONVERT(datetime, '2017-06-26 00:00:00.000', 20), 'HM', 'N', NULL, NULL),
('HOMECARE', CONVERT(datetime, '2017-06-26 00:00:00.000', 20), 'HM', 'N', NULL, NULL),
('HOMECARE', CONVERT(datetime, '2017-06-26 00:00:00.000', 20), 'HM', 'N', NULL, NULL),
('HOMECARE', CONVERT(datetime, '2017-06-26 00:00:00.000', 20), 'HM', 'N', NULL, NULL),
('HOMECARE', CONVERT(datetime, '2017-05-30 00:00:00.000', 20), 'HM', 'N', NULL, NULL),
('HOMECARE', CONVERT(datetime, '2017-05-30 00:00:00.000', 20), 'HM', 'N', NULL, NULL),
('HOMECARE', CONVERT(datetime, '2017-05-04 00:00:00.000', 20), 'HM', 'N', NULL, NULL),
('HOMECARE', CONVERT(datetime, '2017-05-04 00:00:00.000', 20), 'HM', 'N', NULL, NULL),
('HOMECARE', CONVERT(datetime, '2017-05-04 00:00:00.000', 20), 'HM', 'N', NULL, NULL),
('HOMECARE', CONVERT(datetime, '2017-05-04 00:00:00.000', 20), 'HM', 'N', NULL, NULL),
('HOMECARE', CONVERT(datetime, '2017-05-04 00:00:00.000', 20), 'HM', 'N', NULL, NULL),
('HOMECARE', CONVERT(datetime, '2017-05-04 00:00:00.000', 20), 'HM', 'N', NULL, NULL),
('HOMECARE', CONVERT(datetime, '2017-05-04 00:00:00.000', 20), 'HM', 'N', NULL, NULL),
('HOMECARE', CONVERT(datetime, '2017-05-04 00:00:00.000', 20), 'HM', 'N', NULL, NULL),
('HOMECARE', CONVERT(datetime, '2017-05-04 00:00:00.000', 20), 'HM', 'N', NULL, NULL),
('HOMECARE', CONVERT(datetime, '2017-05-04 00:00:00.000', 20), 'HM', 'N', NULL, NULL),
('HOMECARE', CONVERT(datetime, '2017-05-04 00:00:00.000', 20), 'HM', 'N', NULL, NULL),
('HOMECARE', CONVERT(datetime, '2017-05-04 00:00:00.000', 20), 'HM', 'N', NULL, NULL),
('HOMECARE', CONVERT(datetime, '2017-05-04 00:00:00.000', 20), 'HM', 'N', NULL, NULL),
('HOMECARE', CONVERT(datetime, '2017-05-04 00:00:00.000', 20), 'HM', 'N', NULL, NULL),
('HOMECARE', CONVERT(datetime, '2017-05-04 00:00:00.000', 20), 'HM', 'N', NULL, NULL),
('HOMECARE', CONVERT(datetime, '2017-05-04 00:00:00.000', 20), 'HM', 'N', NULL, NULL),
('111824', CONVERT(datetime, '2017-04-21 00:00:00.000', 20), 'MS', 'IP', NULL, NULL),
('111824', CONVERT(datetime, '2017-04-24 00:00:00.000', 20), 'NL', 'OP', NULL, NULL),
('111824', CONVERT(datetime, '2017-04-27 00:00:00.000', 20), 'NL', 'OP', NULL, NULL),
('111824', CONVERT(datetime, '2017-04-20 00:00:00.000', 20), 'NL', 'OP', NULL, NULL),
('111824', CONVERT(datetime, '2017-04-20 00:00:00.000', 20), 'NL', 'OP', NULL, NULL),
('111824', CONVERT(datetime, '2017-04-20 00:00:00.000', 20), 'NL', 'OP', NULL, NULL);
GO
SELECT * FROM ETab
WHERE Source = 'PRODPAT' AND LSPEC = 'HM'
GO
SELECT * FROM UTab
WHERE LSPEC = 'HM';
GO
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '__Tmp')
DROP TABLE __Tmp;
GO
我有一个C#应用程序,在其中用户在运行时请求某些匹配子句。使用用户指定的信息,我生成一些SQL语句并执行更新UTab
。
通过生成的SQL语句,我希望执行一些匹配操作,根据链接字段[MRN]
以及[UTab].[SIDate]
是否位于[ETab].[ADT]
和[ETab].[SDT]
的前后指定天数内更新[UTab].[Enum]
。因此,用户可能会指定两个命令,第一个:
MatchCmd:MRN,LSPEC:ETab:UTab:ADT:SDT:SIDate:0:1:'Iteration#1':WHERE [UTab].[Source] = 'OP' AND [UTab].[LSPEC] = [ETab].[LSPEC]
这段话的意思是,在源表格
ETab
中匹配MRN
和LSPEC
,并更新目标表格UTab
,其中满足条件:SIDate > ADT - 0 day(s)
和SIDate < SDT + 1 day(s)
,以及[UTab].[Source] = 'OP'
。我会用“迭代号”Iteration#1
标记所有符合条件的匹配项。
我的代码会生成以下 SQL:
;WITH cte AS (
SELECT [ETab].[Enum] AS Enum,
[ETab].[MRN] AS Link,
[ETab].[ADT] AS ADT,
[ETab].[SDT] AS SDT,
[UTab].[SIDate] AS DT,
[ETab].[MRN] AS [MRN],
[ETab].[LSPEC] AS [LSPEC],
[ETab].[Source] AS [Source],
ROW_NUMBER() OVER (PARTITION BY [UTab].[MRN], [UTab].[LSPEC], [UTab].[SIDate]
ORDER BY ABS(DATEDIFF(mi, [UTab].[SIDate], [ETab].[ADT]))) AS Idx,
ABS(DATEDIFF(mi, [UTab].[SIDate], [ETab].[ADT])) AS Diff
FROM [UTab]
LEFT JOIN [ETab] ON [UTab].[MRN] = [ETab].[MRN]
WHERE ([UTab].[SIDate] BETWEEN
DATEADD(dd, -0, [ETab].[ADT]) AND
DATEADD(dd, 1, [ETab].[SDT]) AND [Iteration] IS NULL)
AND ETab.Source = 'OP'
) SELECT *
INTO __Tmp
FROM cte;
GO
为了获取符合条件(在时间窗口内且遵守自定义 where 子句)的所有记录。然后通过另一个生成的查询来更新 [UTab].[Enum]
和 [UTab].[Iteration]
。
UPDATE [UTab]
SET [ENum] = [__Tmp].[ENum], [Iteration] = N'Iteration#1'
--SELECT __Tmp.ENum, __Tmp.Link, __Tmp.LSPEC, __Tmp.ADT, __Tmp.SDT, __Tmp.DT, __Tmp.Idx
FROM [UTab] AS up
INNER JOIN [__Tmp]
ON [up].[MRN] = [__Tmp].[Link]
AND [up].[SIDate] = [__Tmp].[DT]
AND [up].[LSPEC] = [__Tmp].[LSPEC]
WHERE __Tmp.Idx = 1;
看起来这个方法和SQL语句运行得不错,但是有一些问题:
问:我使用的方法/SQL是否有明显的问题?
感谢您的时间。
where
子句中的所有内容移动到on
子句中。在更新语句中,最好将AND up.SIDate = __Tmp.DT
和AND up.LSPEC = __Tmp.LSPEC
移动到on
子句中以获得语义,但这并不是关键。 - Zohar Peled