count(case OrgName when '成型车间' then EmployeeCode else 0 end)
时间: 2023-12-30 15:23:01 浏览: 30
这段代码的作用是统计OrgName为'成型车间'的EmployeeCode的数量。当OrgName为'成型车间'时,将对应的EmployeeCode计入统计,否则计入0。最终返回的是统计结果。
```sql
select count(case OrgName when '成型车间' then EmployeeCode else 0 end) as count_num from table_name;
```
相关问题
SELECT a.username, a.userid, a.orgname, a.orgnamehr orgnameHr, a.company company, a.idcard, sum(CASE a.hour_type WHEN 'A' THEN a.allot_hour ELSE 0 END ) aHour, sum(CASE a.hour_type WHEN 'B' THEN a.allot_hour ELSE 0 END ) bHour, sum(CASE a.hour_type WHEN 'C' THEN a.allot_hour ELSE 0 END ) cHour, sum(CASE a.hour_type WHEN 'D' THEN a.allot_hour ELSE 0 END ) dHour, sum(CASE a.hour_type WHEN 'E' THEN a.allot_hour ELSE 0 END ) eHour, sum(CASE a.hour_type WHEN 'F' THEN a.allot_hour ELSE 0 END ) fHour, sum(CASE WHEN a.hour_type IN ('A','B','C','D','E','F') THEN a.allot_hour ELSE 0 END ) countHour, CASE yearMonth WHEN CONVERT(varchar(10), d.break_sure_date, 23) >= CONVERT(varchar(10),'2023-06-06', 23) and CONVERT(varchar(10), d.break_sure_date, 23) < CONVERT(varchar(10),'2023-07-06', 23) THEN '1' ELSE '2' END FROM ems_hour_allot a 这个sql语法有错误吗
根据提供的SQL查询,有一个语法错误。在查询的末尾缺少一个`GROUP BY`子句,以指定如何对结果进行分组。根据查询中的列,你可能需要在`GROUP BY`子句中包含以下列:`a.username`、`a.userid`、`a.orgname`、`a.orgnamehr`、`a.company`、`a.idcard`和`yearMonth`。
以下是修正后的查询:
```sql
SELECT
a.username,
a.userid,
a.orgname,
a.orgnamehr AS orgnameHr,
a.company,
a.idcard,
SUM(CASE a.hour_type WHEN 'A' THEN a.allot_hour ELSE 0 END) AS aHour,
SUM(CASE a.hour_type WHEN 'B' THEN a.allot_hour ELSE 0 END) AS bHour,
SUM(CASE a.hour_type WHEN 'C' THEN a.allot_hour ELSE 0 END) AS cHour,
SUM(CASE a.hour_type WHEN 'D' THEN a.allot_hour ELSE 0 END) AS dHour,
SUM(CASE a.hour_type WHEN 'E' THEN a.allot_hour ELSE 0 END) AS eHour,
SUM(CASE a.hour_type WHEN 'F' THEN a.allot_hour ELSE 0 END) AS fHour,
SUM(CASE WHEN a.hour_type IN ('A','B','C','D','E','F') THEN a.allot_hour ELSE 0 END) AS countHour,
CASE
WHEN CONVERT(varchar(10), d.break_sure_date, 23) >= CONVERT(varchar(10),'2023-06-06', 23) AND CONVERT(varchar(10), d.break_sure_date, 23) < CONVERT(varchar(10),'2023-07-06', 23) THEN '1'
ELSE '2'
END AS yearMonth
FROM
ems_hour_allot a
GROUP BY
a.username,
a.userid,
a.orgname,
a.orgnamehr,
a.company,
a.idcard,
yearMonth;
```
请注意,此修正假设`ems_hour_allot`表中的列`d.break_sure_date`存在并且可以在查询中使用。如果该列不存在或者需要使用其他列,请相应地进行修改。
SELECT t1.org_id AS sonOrgId, t1.orgName AS sonOrgName, t1.id AS routeBasesectionId, t1.`name` AS routeBasesectionName, t1.nature, zw.billCode AS diseaseTypeSn, COUNT( t3.id ) AS num, ( CASE WHEN sum( CASE WHEN zw.`status` = '2' THEN zw.workNum END ) IS NOT NULL THEN sum( CASE WHEN zw.`status` = '2' THEN zw.workNum END ) WHEN sum( CASE WHEN zw.`status` = '1' THEN zw.workNum END ) IS NOT NULL THEN sum( CASE WHEN zw.`status` = '1' THEN zw.workNum END ) ELSE sum( CASE WHEN zw.`status` = '0' THEN zw.workNum END ) END ) AS totalAmount FROM mat_zx_section t3 JOIN base_bridge bb ON bb.id = t3.qshMainId LEFT JOIN mat_zx_workload zw ON zw.mainId = t3.id LEFT JOIN base_e_route_basesection t1 ON t3.baseRoute = t1.id LEFT JOIN main_construct t4 ON t3.constructId = t4.id -- 施工申请 LEFT JOIN main_construct_check t5 ON t5.id = t3.acceptanceId -- 施工申请验收 WHERE t5.process_status = '100' GROUP BY zw.billCode, t1.id, t1.nature, t1.org_id ORDER BY t1.org_id, t1.id, zw.billCode UNION ALL SELECT son_org_id AS sonOrgId, son_org_name AS sonOrgName, route_id AS routeBasesectionId, route_name AS routeBasesectionName, route_type AS nature, disease_type_sn AS diseaseTypeSn, num AS num, total_amount AS totalAmount FROM base_history_repair_statistics;这条sql语句执行失败
这个 SQL 语句可能会因为分号(;)导致执行失败。请尝试将分号(;)删除,然后再次执行该 SQL 语句。如下所示:
```
SELECT t1.org_id AS sonOrgId, t1.orgName AS sonOrgName, t1.id AS routeBasesectionId, t1.`name` AS routeBasesectionName, t1.nature, zw.billCode AS diseaseTypeSn, COUNT( t3.id ) AS num, ( CASE WHEN sum( CASE WHEN zw.`status` = '2' THEN zw.workNum END ) IS NOT NULL THEN sum( CASE WHEN zw.`status` = '2' THEN zw.workNum END ) WHEN sum( CASE WHEN zw.`status` = '1' THEN zw.workNum END ) IS NOT NULL THEN sum( CASE WHEN zw.`status` = '1' THEN zw.workNum END ) ELSE sum( CASE WHEN zw.`status` = '0' THEN zw.workNum END ) END ) AS totalAmount FROM mat_zx_section t3 JOIN base_bridge bb ON bb.id = t3.qshMainId LEFT JOIN mat_zx_workload zw ON zw.mainId = t3.id LEFT JOIN base_e_route_basesection t1 ON t3.baseRoute = t1.id LEFT JOIN main_construct t4 ON t3.constructId = t4.id -- 施工申请 LEFT JOIN main_construct_check t5 ON t5.id = t3.acceptanceId -- 施工申请验收 WHERE t5.process_status = '100' GROUP BY zw.billCode, t1.id, t1.nature, t1.org_id ORDER BY t1.org_id, t1.id, zw.billCode UNION ALL SELECT son_org_id AS sonOrgId, son_org_name AS sonOrgName, route_id AS routeBasesectionId, route_name AS routeBasesectionName, route_type AS nature, disease_type_sn AS diseaseTypeSn, num AS num, total_amount AS totalAmount FROM base_history_repair_statistics
```
相关推荐
![zip](https://img-home.csdnimg.cn/images/20210720083736.png)
![zip](https://img-home.csdnimg.cn/images/20210720083736.png)
![zip](https://img-home.csdnimg.cn/images/20210720083736.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)