我试图过滤掉行,其中每列中都有NULL值。由于大约有30列,因此逐个输入似乎不太可行。有什么办法可以让整个桌子都这样吗?
我试过这样做
SELECT *
FROM TABLE1
WHERE column1 IS NULL AND column2 IS NULL -- etc...
然而,手动遍历所有列非常耗时;我如何过滤掉所有列值为NULL的行?
也许输入一点,得到你需要的结果:
-- step 1. get the column list of a table
select group_concat(name) as column_list
from pragma_table_info('[table_name]');
-- step 2. select those ALL-NULL rows, [column_list] is from the above
select *
from [table_name]
where coalesce([column_list]) is null;
一个例子:
-- create a table
create table t_null (
c1 int,
c2 int,
c3 int);
insert into t_null
values
(null, null, null),
(null, null, null),
(null, null, null);
-- get column list from metadata
select group_concat(name) as column_list
from pragma_table_info('t_null');
column_list|
-----------+
c1,c2,c3 |
--
select *
from t_null
where COALESCE(c1,c2,c3) is null;
<代码>代码>