case when a.id='1' then left join tab
时间: 2024-05-31 15:10:55 浏览: 14
I'm sorry, but the query you provided is incomplete and contains syntax errors. Can you please provide more context and details about the query you are trying to write or the problem you are trying to solve? I'll do my best to assist you.
相关问题
优化这一段sql:SELECT user_worker.Title AS worker, COUNT(CASE WHEN (CASE WHEN bl.Checker = 0 THEN '' WHEN bl.Checker > 0 AND bl.Audited = -1 THEN 'NG' WHEN bl.Checker > 0 AND bl.Audited = 1 THEN 'OK' END) ='' THEN 1 END) as nullstr, COUNT(CASE WHEN (CASE WHEN bl.Checker = 0 THEN '' WHEN bl.Checker > 0 AND bl.Audited = -1 THEN 'NG' WHEN bl.Checker > 0 AND bl.Audited = 1 THEN 'OK' END) = 'OK' THEN 1 END) as okstr, COUNT(CASE WHEN (CASE WHEN bl.Checker = 0 THEN '' WHEN bl.Checker > 0 AND bl.Audited = -1 THEN 'NG' WHEN bl.Checker > 0 AND bl.Audited = 1 THEN 'OK' END) = 'NG' THEN 1 END) as NGstr, (COUNT(CASE WHEN (CASE WHEN bl.Checker = 0 THEN '' WHEN bl.Checker > 0 AND bl.Audited = -1 THEN 'NG' WHEN bl.Checker > 0 AND bl.Audited = 1 THEN 'OK' END) = 'OK' THEN 1 END) +COUNT(CASE WHEN (CASE WHEN bl.Checker = 0 THEN '' WHEN bl.Checker > 0 AND bl.Audited = -1 THEN 'NG' WHEN bl.Checker > 0 AND bl.Audited = 1 THEN 'OK' END) ='' THEN 1 END) )as totalOKStr FROM [bdWorkOrderList] bl LEFT JOIN [bdWorkOrder] bd ON bl.OrderID = bd.ID LEFT JOIN [reportMJWXJL] rwx ON bl.OrderID = rwx.OrderID LEFT JOIN [reportMJQHLHJL] rqh ON bl.OrderID = rqh.OrderID LEFT JOIN user_worker ON user_worker.ID = bl.Worker WHERE bd.IsDeleted = 0 AND bl.StepName LIKE 'Maker%' AND bl.StepName IN ('Maker修模', 'maker切换料号', 'Maker模具保养') AND DATEPART(MONTH, bl.StartTime) = DATEPART(MONTH, GETDATE()) and DATEPART(YEAR, bl.StartTime) = DATEPART(YEAR, GETDATE()) GROUP BY user_worker.Title)
你可以尝试优化以上SQL查询的性能:
```sql
WITH CTE_Counts AS (
SELECT
user_worker.Title AS worker,
COUNT(CASE WHEN bl.Checker = 0 THEN 1 END) AS nullstr,
COUNT(CASE WHEN bl.Audited = -1 THEN 1 END) AS NGstr,
COUNT(CASE WHEN bl.Audited = 1 THEN 1 END) AS okstr
FROM [bdWorkOrderList] bl
LEFT JOIN [bdWorkOrder] bd ON bl.OrderID = bd.ID
LEFT JOIN [reportMJWXJL] rwx ON bl.OrderID = rwx.OrderID
LEFT JOIN [reportMJQHLHJL] rqh ON bl.OrderID = rqh.OrderID
LEFT JOIN user_worker ON user_worker.ID = bl.Worker
WHERE bd.IsDeleted = 0
AND bl.StepName LIKE 'Maker%'
AND bl.StepName IN ('Maker修模', 'maker切换料号', 'Maker模具保养')
AND DATEPART(MONTH, bl.StartTime) = DATEPART(MONTH, GETDATE())
AND DATEPART(YEAR, bl.StartTime) = DATEPART(YEAR, GETDATE())
GROUP BY user_worker.Title
)
SELECT
worker,
nullstr,
NGstr,
okstr,
(nullstr + okstr) AS totalOKStr
FROM CTE_Counts;
```
在上述优化中,我使用了一个公共表表达式(CTE)来计算每个分类的数量。通过将计算逻辑放在CTE中,可以提高查询的可读性和性能。然后,从CTE中选择所需的列,并计算总的OK数量。
请注意,为了进一步优化查询性能,你可能需要创建适当的索引来支持查询中使用的过滤和连接操作。具体索引的创建需要根据表结构和查询需求进行评估和调整。
优化这段sql:WITH user_worker AS (SELECT Title, ID FROM [biUser]) SELECT user_worker.Title AS worker, COUNT(CASE WHEN (CASE WHEN bl.Checker = 0 THEN '' WHEN bl.Checker > 0 AND bl.Audited = -1 THEN 'NG' WHEN bl.Checker > 0 AND bl.Audited = 1 THEN 'OK' END) ='' THEN 1 END) as nullstr, COUNT(CASE WHEN (CASE WHEN bl.Checker = 0 THEN '' WHEN bl.Checker > 0 AND bl.Audited = -1 THEN 'NG' WHEN bl.Checker > 0 AND bl.Audited = 1 THEN 'OK' END) = 'OK' THEN 1 END) as okstr, COUNT(CASE WHEN (CASE WHEN bl.Checker = 0 THEN '' WHEN bl.Checker > 0 AND bl.Audited = -1 THEN 'NG' WHEN bl.Checker > 0 AND bl.Audited = 1 THEN 'OK' END) = 'NG' THEN 1 END) as NGstr, (COUNT(CASE WHEN (CASE WHEN bl.Checker = 0 THEN '' WHEN bl.Checker > 0 AND bl.Audited = -1 THEN 'NG' WHEN bl.Checker > 0 AND bl.Audited = 1 THEN 'OK' END) = 'OK' THEN 1 END) +COUNT(CASE WHEN (CASE WHEN bl.Checker = 0 THEN '' WHEN bl.Checker > 0 AND bl.Audited = -1 THEN 'NG' WHEN bl.Checker > 0 AND bl.Audited = 1 THEN 'OK' END) ='' THEN 1 END) )as totalOKStr FROM [bdWorkOrderList] bl LEFT JOIN [bdWorkOrder] bd ON bl.OrderID = bd.ID LEFT JOIN [reportMJWXJL] rwx ON bl.OrderID = rwx.OrderID LEFT JOIN [reportMJQHLHJL] rqh ON bl.OrderID = rqh.OrderID LEFT JOIN user_worker ON user_worker.ID = bl.Worker WHERE bd.IsDeleted = 0 AND bl.StepName LIKE 'Maker%' AND bl.StepName IN ('Maker修模', 'maker切换料号', 'Maker模具保养') AND DATEPART(MONTH, bl.StartTime) = DATEPART(MONTH, GETDATE()) and DATEPART(YEAR, bl.StartTime) = DATEPART(YEAR, GETDATE()) GROUP BY user_worker.Title
你可以尝试优化以上SQL查询的性能:
```sql
WITH user_worker AS (
SELECT Title, ID
FROM [biUser]
),
Counts AS (
SELECT
bl.Worker,
COUNT(CASE WHEN bl.Audited = -1 THEN 1 END) AS NGstr,
COUNT(CASE WHEN bl.Audited = 1 THEN 1 END) AS okstr,
COUNT(CASE WHEN bl.Audited IS NULL THEN 1 END) AS nullstr
FROM [bdWorkOrderList] bl
LEFT JOIN [bdWorkOrder] bd ON bl.OrderID = bd.ID
LEFT JOIN [reportMJWXJL] rwx ON bl.OrderID = rwx.OrderID
LEFT JOIN [reportMJQHLHJL] rqh ON bl.OrderID = rqh.OrderID
LEFT JOIN user_worker ON user_worker.ID = bl.Worker
WHERE bd.IsDeleted = 0
AND bl.StepName LIKE 'Maker%'
AND bl.StepName IN ('Maker修模', 'maker切换料号', 'Maker模具保养')
AND DATEPART(MONTH, bl.StartTime) = DATEPART(MONTH, GETDATE())
AND DATEPART(YEAR, bl.StartTime) = DATEPART(YEAR, GETDATE())
GROUP BY bl.Worker
)
SELECT
uw.Title AS worker,
c.nullstr,
c.okstr,
c.NGstr,
(c.okstr + c.nullstr) AS totalOKStr
FROM Counts c
JOIN user_worker uw ON uw.ID = c.Worker;
```
在上述优化中,我使用了两个公共表表达式(CTE):`user_worker` 和 `Counts`。首先,从 `[biUser]` 表中获取工人的标题和ID,存储在 `user_worker` CTE 中。然后,使用 `Counts` CTE 计算每个工人的统计数据,避免了重复计算。最后,通过将 `Counts` CTE 与 `user_worker` CTE 进行连接,选择所需的列并进行汇总。
请注意,在优化查询性能方面,你可能还需要考虑适当的索引来支持连接和过滤操作。具体索引的创建需要根据表结构和查询需求进行评估和调整。