如何合并两条sql:SELECT COUNT(b.id) AS yilei FROM base_bridge_evaluation b JOIN base_bridge a ON a.id = b.bid WHERE a.bridgeGrade = '一类'; SELECT COUNT(b.id) AS erlei FROM base_bridge_evaluation b JOIN base_bridge a ON a.id = b.bid WHERE a.bridgeGrade = '二类';
时间: 2024-02-04 16:03:58 浏览: 33
可以使用UNION ALL将两条SQL语句合并,如下所示:
```
SELECT '一类' AS bridge_grade, COUNT(b) AS num
FROM base_bridge_evaluation b
JOIN base_bridge a ON a.id = b.bid
WHERE a.bridgeGrade = '一类'
UNION ALL
SELECT '二类' AS bridge_grade, COUNT(b.id) AS num
FROM base_bridge_evaluation b
JOIN base_bridge a ON a.id = b.bid
WHERE a.bridgeGrade = '二类';
```
这样可以将两个查询的结果合并成一个结果集,其中包括了每个桥梁等级的统计数量和等级名称。
相关问题
sql统计错误:select b.son_org_name sonOrgName,ifnull(count(CASE WHEN b.bridgeGrade = '一类' THEN 1 ELSE 0 END), 0) yilei from base_bridge b LEFT JOIN base_bridge_evaluation e on e.bid = b.id where b.isNormal = '正常' GROUP BY b.sonOrgId
这条 SQL 语句的问题在于,在使用 LEFT JOIN 连接时,如果左表(base_bridge)中没有对应的记录,右表(base_bridge_evaluation)中对应的值将会是 NULL。在对这个 NULL 值进行 CASE WHEN 判断时,会得到 NULL 值,而不是 0 或 1,因此最终的 count() 函数计算结果会出现错误。解决这个问题的方法是使用 IFNULL() 函数,将 NULL 值转换为 0 或 1。正确的 SQL 语句应该是:
```
SELECT
b.son_org_name AS sonOrgName,
IFNULL(COUNT(CASE WHEN b.bridgeGrade = '一类' THEN 1 ELSE NULL END), 0) AS yilei
FROM
base_bridge b
LEFT JOIN base_bridge_evaluation e ON e.bid = b.id
WHERE
b.isNormal = '正常'
GROUP BY
b.sonOrgId
```
相关推荐
![rar](https://img-home.csdnimg.cn/images/20210720083606.png)
![doc](https://img-home.csdnimg.cn/images/20210720083327.png)
![rar](https://img-home.csdnimg.cn/images/20210720083606.png)
![zip](https://img-home.csdnimg.cn/images/20210720083736.png)
![pdf](https://img-home.csdnimg.cn/images/20210720083512.png)
![rar](https://img-home.csdnimg.cn/images/20210720083606.png)
![txt](https://img-home.csdnimg.cn/images/20210720083642.png)
![zip](https://img-home.csdnimg.cn/images/20210720083736.png)
![rar](https://img-home.csdnimg.cn/images/20210720083606.png)
![zip](https://img-home.csdnimg.cn/images/20210720083736.png)
![7z](https://img-home.csdnimg.cn/images/20210720083312.png)
![rar](https://img-home.csdnimg.cn/images/20210720083606.png)
![sb3](https://img-home.csdnimg.cn/images/20210720083646.png)
![zip](https://img-home.csdnimg.cn/images/20210720083736.png)