将PostgreSQL转换为MS SQL

Question

我需要帮助将PostgreSQL查询转换为MSSQ。

以下是我到目前为止所做的工作，但我正在发布我认为在MS SQL中不允许使用的函数和数组区域。

我需要做些什么来更改函数吗？看起来WHERE语句中也有一个数组。

我已经为#temp表添加了select语句，但当我创建#temp表格时，我会收到错误，说语法不正确

CREATE FUNCTION pm_aggregate_report 
(
_facility_ids uuid[]
, _risk_ids uuid[] DEFAULT NULL::uuid[]
, _assignee_ids uuid[] DEFAULT NULL::uuid[]
, _start_date date DEFAULT NULL::date
, _end_date date DEFAULT NULL::date
) 
RETURNS TABLE
(
facility character varying
, pm_id uuid, grouped_pm boolean
, risk_id uuid
, risk character varying
, pm_status_id uuid
, user_id uuid
, assignee text
, completed_by uuid
, total_labor bigint
)

CREATE TABLE #tmp_pm_aggregate
(
facility_id VARCHAR(126),
pm_id VARCHAR(126),
grouped_pm VARCHAR(126),
risk_id VARCHAR(126),
pm_status_id VARCHAR(126),
user_id VARCHAR(126),
completed_by VARCHAR(126)
)
SELECT DISTINCT 
COALESCE(gp.facility_id, a.facility_id) as facility_id,
COALESCE(p.grouped_pm_id, p.id) as pm_id,
CASE WHEN p.grouped_pm_id IS NULL THEN false ELSE true END as grouped_pm,
COALESCE(gp.risk_id, a.risk_id) as risk_id,
COALESCE(gp.pm_status_id, p.pm_status_id) as pm_status_id,
COALESCE(gass.user_id, sass.user_id) as user_id,
COALESCE(gp.completed_by, p.completed_by) as completed_by
FROM pms p
JOIN assets a
ON p.asset_id = a.id
LEFT JOIN grouped_pms gp
ON p.grouped_pm_id = gp.id
LEFT JOIN assignees sass
ON p.id = sass.record_id
AND sass.type = 'single_pm'
LEFT JOIN assignees gass
ON p.grouped_pm_id = gass.record_id
AND gass.type = 'grouped_pm'
LEFT JOIN users u
ON (sass.user_id = u.id OR gass.user_id = u.id)
WHERE a.facility_id = ANY(_facility_ids)
AND NOT a.is_component
AND COALESCE(gp.pm_status_id, p.pm_status_id) in ('f9bdfc17-3bb5-4ec0-8477-24ef05ea3b9b', '06fc910c-3d07-4284-8f6e-8fb3873f5333')
AND COALESCE(gp.completion_date, p.completion_date) BETWEEN COALESCE(_start_date, '1/1/2000') AND COALESCE(_end_date, '1/1/3000')
AND COALESCE(gp.show_date, p.show_date) <= CURRENT_TIMESTAMP
AND COALESCE(gass.user_id, sass.user_id) IS NOT NULL
AND u.user_type_id != 'ec823d98-7023-4908-8006-2e33ddf2c11b' 
AND (_risk_ids IS NULL OR COALESCE(gp.risk_id, a.risk_id) = ANY(_risk_ids)   
AND (_assignee_ids IS NULL OR COALESCE(gass.user_id, sass.user_id) = ANY(_assignee_ids);

SELECT
f.name as facility,
t.pm_id,
t.grouped_pm,  
t.risk_id,
r.name as risk, 
t.pm_status_id,
t.user_id,
u.name_last + ', ' + u.name_first as assignee, 
t.completed_by,
ISNULL(gwl.total_labor, swl.total_labor) as total_labor
FROM #tmp_pm_aggregate t
JOIN facilities f
ON t.facility_id = f.id
JOIN risks r
ON t.risk_id = r.id
JOIN users u
ON t.user_id = u.id
LEFT JOIN (SELECT wl.record_id, wl.user_id, SUM(wl.labor_time) as total_labor
FROM work_logs wl
WHERE wl.type = 'single_pm'
GROUP BY wl.record_id, wl.user_id) as swl
ON t.pm_id = swl.record_id
AND t.user_id = swl.user_id
AND t.grouped_pm = false
LEFT JOIN (SELECT wl.record_id, wl.user_id, SUM(wl.labor_time) as total_labor
FROM work_logs wl
WHERE wl.type = 'grouped_pm'
GROUP BY wl.record_id, wl.user_id) as gwl
ON t.pm_id = gwl.record_id
AND t.user_id = gwl.user_id
AND t.grouped_pm = true
ORDER BY facility,
assignee,
risk;

DROP TABLE #tmp_pm_aggregate;

Answer 1

您可以创建一个inline表值函数，并简单地从中返回一个结果集。您不需要(也不能使用(临时表，也不声明返回的"行集"；形状

对于数组参数，可以使用表类型：

CREATE TYPE dbo.GuidList (value uniqueidentifier NOT NULL PRIMARY KEY);

• 因为表参数是实际的表，所以必须像以下(NOT EXISTS (SELECT 1 FROM @risk_ids) OR ISNULL(gp.risk_id, a.risk_id) IN (SELECT r.value FROM @risk_ids))那样查询它们
• 参数必须以@开头
• 没有boolean类型，必须使用bit
• 文字始终使用确定性日期格式。yyyymmdd适用于日期。你需要考虑小时和分钟吗，因为你没有
• ISNULL在SQL Server中的性能通常比COALESCE好，因为编译器更了解它
• 您可能需要传递一个单独的参数，显示是否为可选表参数传递了任何内容
• 我建议您仔细查看实际查询：为什么它需要DISTINCT？它的性能很差，而且通常是一种代码气味，表明连接考虑不周。也许您需要在assignees上组合这两个联接，或者您应该在某个地方使用行编号策略

CREATE FUNCTION dbo.pm_aggregate_report 
(
@facility_ids dbo.GuidList
, @risk_ids dbo.GuidList
, @assignee_ids dbo.GuidList
, @start_date date
, @end_date date
) 
RETURNS TABLE AS RETURN
SELECT DISTINCT   -- why DISTINCT, perhaps rethink your joins
ISNULL(gp.facility_id, a.facility_id) as facility_id,
ISNULL(p.grouped_pm_id, p.id) as pm_id,
CASE WHEN p.grouped_pm_id IS NULL THEN CAST(0 AS bit) ELSE CAST(1 AS bit) END as grouped_pm,
ISNULL(gp.risk_id, a.risk_id) as risk_id,
ISNULL(gp.pm_status_id, p.pm_status_id) as pm_status_id,
ISNULL(gass.user_id, sass.user_id) as user_id,
ISNULL(gp.completed_by, p.completed_by) as completed_by
FROM pms p
JOIN assets a
ON p.asset_id = a.id
LEFT JOIN grouped_pms gp
ON p.grouped_pm_id = gp.id
LEFT JOIN assignees sass
ON p.id = sass.record_id
AND sass.type = 'single_pm'
LEFT JOIN assignees gass
ON p.grouped_pm_id = gass.record_id
AND gass.type = 'grouped_pm'
LEFT JOIN users u
ON (sass.user_id = u.id OR gass.user_id = u.id)  -- is this doubling up your rows?
WHERE a.facility_id IN (SELECT f.value FROM @facility_ids f)
AND a.is_component = 0
AND ISNULL(gp.pm_status_id, p.pm_status_id) in ('f9bdfc17-3bb5-4ec0-8477-24ef05ea3b9b', '06fc910c-3d07-4284-8f6e-8fb3873f5333')
AND ISNULL(gp.completion_date, p.completion_date) BETWEEN ISNULL(@start_date, '20000101') AND ISNULL(@end_date, '30000101')  -- perhaps use >= AND <
AND ISNULL(gp.show_date, p.show_date) <= CURRENT_TIMESTAMP
AND ISNULL(gass.user_id, sass.user_id) IS NOT NULL
AND u.user_type_id != 'ec823d98-7023-4908-8006-2e33ddf2c11b' 
AND (NOT EXISTS (SELECT 1 FROM @risk_ids) OR ISNULL(gp.risk_id, a.risk_id) IN (SELECT r.value FROM @risk_ids))
AND (NOT EXISTS (SELECT 1 FROM @assignee_ids) OR ISNULL(gass.user_id, sass.user_id) IN (SELECT aid.value FROM @assignee_ids aid));