我使用的是 **PostgreSQL 13**。我有几个表。我想执行一个选择请求,获取这些表的名称以及 `updated_date` 列中 NULL 值的行数。
我可以输出具有 NULL 日期的行数。我这样做:
```sql
select count(*)
from TABLE_1
where updated_date is null
UNION
select count(*)
from TABLE_2
where updated_date is null;
-------
count |
-------
1 |
2 |
-------
```
我可以这样获取表的名称:
```sql
select t.tableoid::regclass as table_name
from TABLE_1 t
where updated_date is null;
------------
table_name |
------------
TABLE_1 |
------------
select t.tableoid::regclass as table_name
from TABLE_2 t
where updated_date is null;
------------
table_name |
------------
TABLE_2 |
TABLE_2 |
------------
```
最后,我需要一个 SQL 请求,它将给我一个结果:
```sql
--------------------
table_name | count |
--------------------
TABLE_1 | 1 |
TABLE_2 | 2 |
--------------------
```
我尝试这样做:
```sql
select t1.tableoid::regclass, count(*)
from TABLE_1 t1
where updated_date is null
UNION
select t2.tableoid::regclass, count(*)
from TABLE_2 t2
where updated_date is null;
```
但是我得到了错误:
```sql
[42803] ERROR: column "t1.tableoid" must appear in the GROUP BY clause or be used in an aggregate function
```
我该如何解决这个问题?
1 个回答