sqlserver-优化仪表板查询

Question

我正在构建一个应用程序，在这个应用程序中，我想显示系统中项目数量及其各种状态的简单统计信息，我创建了一个查询，虽然它有效，但我觉得效率非常低，但老实说，我不知道如何让它变得更好。

我最终得到的是一个可爱的，它只不过是一堆子选择，以便获得我的仪表板统计数据：

with
cte_dashboard(active_employees, inactive_employees, linked_employees, total_employees, 
                ongoing, completed, pending, cancelled, total_contracts) as
(
    select 
        (select count(id) from [tam].[employees] where isActive = 1) as 'active',
        (select count(id) from [tam].[employees] where isActive = 0) as 'inactive',
        (select count(id) from [tam].[employees] where UserId is not null) as 'linked',
        (select count(id) from [tam].[employees]) as 'total',
        (select count(id) from [tam].[contracts] where Status = 'ongoing') as 'ongoing',
        (select count(id) from [tam].[contracts] where Status = 'completed') as 'completed',
        (select count(id) from [tam].[contracts] where Status = 'pending') as 'pending',
        (select count(id) from [tam].[contracts] where Status = 'cancelled') as 'cacnelled',
        (select count(id) from [tam].[contracts]) as 'total'
)    
select * from cte_dashboard;

如果没有所有这些子查询，我该如何做得更好，或者这真的是我要做的全部吗？

Answer 1

这也可能起作用：

SELECT * FROM
(
    SELECT 
        SUM(CASE WHEN isActive = 1 THEN 1 ELSE 0 END) AS [Active],
        SUM(CASE WHEN isActive = 0 THEN 1 ELSE 0 END) AS [Inactive],
        SUM(CASE WHEN UserId IS NOT NULL THEN 1 ELSE 0 END) AS [linked],
        COUNT(*) AS [total] -- OR SUM(1)
    FROM [tam].[employees]
) a
CROSS JOIN
(
    SELECT 
        SUM(CASE WHEN Status = 'ongoing' THEN 1 ELSE 0 END) AS [ongoing],
        SUM(CASE WHEN Status = 'completed' THEN 1 ELSE 0 END) AS [completed],
        SUM(CASE WHEN Status = 'pending' THEN 1 ELSE 0 END) AS [pending],
        SUM(CASE WHEN Status = 'cancelled' THEN 1 ELSE 0 END) AS [cancelled],
        COUNT(*) AS [total] -- OR SUM(1)
    FROM [tam].[contracts]
) b

Answer 2

类似这样的东西：

;WITH Emp AS (
    SELECT 1 RowId, 
           SUM(1) AS Total, 
           SUM(CASE WHEN IsActive = 1 THEN 1 END) Active,
           SUM(CASE WHEN IsActive = 0 THEN 1 END) Inactive,
           SUM(CASE WHEN UserId IS NOT NULL THEN 1 END) Linked
    FROM Employees
),
Con AS (
    --similar query to Contracts table
)
SELECT *
FROM Emp e
      JOIN Con c ON e.RowId = c.RowId

Answer 3

您可以使用分区进行计数

COUNT(ID) OVER(Partition By [tam].[employees].isActive  Group By IsActive)
COUNT(ID) OVER(Partition By [contracts].Status Group By Status)

像那样的东西，看起来干净多了。