mysql - 防止选择平均值出现 NULL

Question

我试图弄清楚如何防止从 a 返回 NULLselect并且不为该查询返回任何内容（0 行），其中我提供了不正确state的值作为

select avg(lat) as lat, avg(lon) as lon from locations where city = "New York" and state = "NC"

我尝试过类似的东西

select case when avg(lat) != NULL then avg(lat) end as lat, case when avg(lon) != NULL then avg(lon) end as lon from locations where city ="New York" and state="NC"

但这也没有用。

标签： mysqlsql

Answer 1

聚合函数并不group by总是返回一行。因此，您需要添加一些group by（例如group by city）或having avg(lat) is not null防止在空输入数据集上输出（已过滤）。

create table t
as
select *
from (values
  row(1, 'NY'),
  row(3, 'NY'),
  row(null, 'LA')
) as t(lat, state)

select state, avg(lat) as alat
from t
group by state

状态 | 阿拉特
:---- | -----：
纽约 | 2.0000
洛杉矶 |   空值

/*No input rows - empty*/
select avg(lat)
from t
where state = 'AR'
group by state

| 平均（纬度）|
| --------: |

/*There's input row,
but the avg is empty - empty output*/
select avg(lat)
from t
where state = 'LA'
group by state

| 平均（纬度）|
| --------: |
|     空|

/*There's a row and avg is empty,
but we filter it out with having*/
select avg(lat)
from t
where state = 'LA'
group by state
having avg(lat) is not null

| 平均（纬度）|
| --------: |

db<>在这里摆弄