编写条件相同但列不同的查询，并使用聚合函数
编写条件相同但列不同的查询，并使用聚合函数
提问于 2020-08-03 01:25:57
我正在制作一个系统，为客户需求分配分析人员。我编写了一个过程，以寻找理想的分析师被分配到一个需求。
主要表如下：
解决requirements)
- grc_requirement (需求表)的分析师表(
- grc_analyst )与grc_analyst的关系多到一。需求有一个字段"due_date“，该字段表示必须解决需求的最后期限。
寻找分析员的规则如下：
- 必须计算与每个分析师相关的需求数。如果每个分析师的需求数量相同，即分析师分配的需求数量相同，则选择需求平均到期日距当前日期最远的分析师。如果需求数量不同，则选择
- ，也就是说，分析师分配的需求数量不同，然后选择需求数量最少的分析师。
以下是代码：
DECLARE @totalRequirementsAnalysts int
DECLARE @idAnalyst int = null
SELECT @totalRequirementsAnalysts =count(distinct(a.requirements))
FROM (
SELECT
a.id,count(r.id) requirements
FROM
grc_analyst a
INNER JOIN grc_analyststate ea ON a.id_analyststate = ea.id AND ea.code = 'A'
INNER JOIN grc_analyst_category ac ON a.id = ac.id_analyst
INNER JOIN grc_category c ON c.id = ac.id_category AND c.code = 'SOAP'
LEFT JOIN grc_requirement r ON a.id = r.id_analyst AND r.id_requirementstate in (
SELECT id from grc_requirementstate er where er.code IN ('AS','ER','DL','DC')
)
group by a.id
) a
IF (@totalRequirementsAnalysts = 1)
BEGIN
PRINT 'Analysts have the same amount of requirements assigned'
SET @idAnalyst = (
SELECT a.id from (
SELECT TOP(1)
a.id,avg (DATEDIFF(DAY,getdate(),r.due_date))average_due_date
FROM
grc_analyst a
INNER JOIN grc_analyststate ea ON a.id_analyststate = ea.id AND ea.code = 'A'
INNER JOIN grc_analyst_category ac ON a.id = ac.id_analyst
INNER JOIN grc_category c ON c.id = ac.id_category AND c.code = 'SOAP'
LEFT JOIN grc_requirement r ON a.id = r.id_analyst AND r.id_requirementstate in (
SELECT id from grc_requirementstate er where er.code IN ('AS','ER','DL','DC')
)
group by a.id
order by average_due_date DESC
) a
)
END
ELSE
BEGIN
PRINT 'Analysts have different number of requirements assigned'
SET @idAnalyst = (
SELECT a.id from (
SELECT TOP(1)
a.id,count(r.id) requirements
FROM
grc_analyst a
INNER JOIN grc_analyststate ea ON a.id_analyststate = ea.id AND ea.code = 'A'
INNER JOIN grc_analyst_category ac ON a.id = ac.id_analyst
INNER JOIN grc_category c ON c.id = ac.id_category AND c.code = 'SOAP'
LEFT JOIN grc_requirement r ON a.id = r.id_analyst AND r.id_requirementstate in (
SELECT id from grc_requirementstate er where er.code IN ('AS','ER','DL','DC')
)
group by a.id
order by requirements ASC
) a
)
END
SELECT ga.id from grc_analyst ga where ga.id = @idAnalyst正如您所看到的，我使用了三个查询，但是对于所有三个(相同条件的连接表)来说，"from“部分是相同的，.This过程符合规则并能工作，但是我希望减少查询的数量，因为有重复的代码。
提前感谢！
回答 3
Stack Overflow用户
回答已采纳
发布于 2020-08-03 01:58:42
为了避免重复代码，请创建查询公共部分的视图，然后在您的3个查询中使用该视图。
CREATE VIEW dbo.grc_analyst_view
AS
SELECT a.id
, COUNT(r.id) requirements
, AVG(DATEDIFF(DAY,GETDATE(),r.due_date)) average_due_date
FROM grc_analyst a
INNER JOIN grc_analyststate ea ON a.id_analyststate = ea.id AND ea.code = 'A'
INNER JOIN grc_analyst_category ac ON a.id = ac.id_analyst
INNER JOIN grc_category c ON c.id = ac.id_category AND c.code = 'SOAP'
LEFT JOIN grc_requirement r ON a.id = r.id_analyst AND r.id_requirementstate in (
SELECT id
FROM grc_requirementstate er
WHERE er.code IN ('AS','ER','DL','DC')
)
GROUP BY a.id;
GO
-- QUERY 1
SELECT @totalRequirementsAnalysts = COUNT(DISTINCT(requirements))
FROM dbo.grc_analyst_view;
-- QUERY 2
SELECT TOP(1) id
FROM dbo.grc_analyst_view
ORDER BY average_due_date DESC
-- QUERY 3
SELECT TOP(1) id
FROM dbo.grc_analyst_view
ORDER BY requirements ASC;Stack Overflow用户
发布于 2020-08-03 01:44:53
这个有用吗？
WITH base AS
(
SELECT
a.id,
count(r.id) requirements,
avg (DATEDIFF(DAY,getdate(),r.due_date)) average_due_date
FROM grc_analyst a
JOIN grc_analyststate ea ON a.id_analyststate = ea.id AND ea.code = 'A'
JOIN grc_analyst_category ac ON a.id = ac.id_analyst
JOIN grc_category c ON c.id = ac.id_category AND c.code = 'SOAP'
LEFT JOIN grc_requirement r ON a.id = r.id_analyst
JOIN grc_requirementstate es ON r.id_requirementstate IS NULL OR
(r.id_requirementstate IS NOT NULL
AND r.id_requirementstate = er.id
AND er.code IN ('AS','ER','DL','DC')
)
group by a.id
)
-- etc如果您不喜欢CTE，那么您可以使用上面的视图。
另外，我不知道grc_requirement表的左联接在日期差平均值为null时的规则是什么。如果这个结果是错误的，就应该解决这个问题。
Stack Overflow用户
发布于 2020-08-03 04:33:33
实际上，我相信您可以使用一个查询完成您想要的东西，利用窗口函数FIRST_VALUE和一个子查询，您完全可以获得您想要的值，而无需重复查询。这也应该表现得更好。
事实证明，您不能在DISTINCT中使用COUNT OVER ()，所以我们必须使用CTE和CROSS APPLY。
with cte as (
SELECT a.id
, COUNT(DISTINCT r.id) Requirements
, AVG(DATEDIFF(DAY,GETDATE(),r.due_date)) average_due_date
FROM grc_analyst a
INNER JOIN grc_analyststate ea ON a.id_analyststate = ea.id AND ea.code = 'A'
INNER JOIN grc_analyst_category ac ON a.id = ac.id_analyst
INNER JOIN grc_category c ON c.id = ac.id_category AND c.code = 'SOAP'
LEFT JOIN grc_requirement r ON a.id = r.id_analyst AND r.id_requirementstate in (
SELECT id
FROM grc_requirementstate er
WHERE er.code IN ('AS','ER','DL','DC')
)
GROUP BY a.id
)
SELECT TOP 1 @idAnalyst = CASE WHEN N.DistinctRequirements = 1 THEN FIRST_VALUE(X.id) OVER (ORDER BY average_due_date ASC) ELSE FIRST_VALUE(X.id) OVER (ORDER BY Requirements ASC) END
FROM cte X
CROSS APPLY (
SELECT COUNT(DISTINCT Requirements) DistinctRequirements
FROM cte
) N;
-- While one should normally use an 'order by' clause with 'top' it is meaningless in this case as all rows return the same.如果需要打印选择的选项，那么将requirements计数赋给一个变量，并在IF语句中使用它。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/63222589
复制相关文章
相似问题
腾讯云开发者
