sql - how to get the rows which doesn't have data

Question

I have a table called Temp with around 15 columns. In that table col1 is primary key and remaining all are normal keys(which may have null values) I have inserted the data in the table along with date(in col15). If the data is not available for all the columns except primary key column, then I inserted the primary key column value and the date column value (colulmn 15) and for all remaining columns null values.

Temp
------------------------------------------------------
col1  col2 col3 col4 col5 ....................col15
------------------------------------------------------
o124  xxx   xxx  xxx  null                    2019-10-17
o236  null  xxx  xxx  xxxx                    2019-10-17
o437  xxx   xxx  null xxxx                    2019-10-17
o448  xxx   null xxx  xxxx                    2019-10-17
o456  null  null null null                    2019-10-17
o458  null  null null null                    2019-10-17

Now I want to get the rows which don't have values in all the columns except primary key column (column1) and date column (column 15).

The output would be

------------------------------------------------------
col1  col2 col3 col4 col5 ....................col15
------------------------------------------------------
o456  null  null null null                  2019-10-17
o458  null  null null null                  2019-10-17

What should be the query I need to write.

Thanks

标签： sqloracle

Answer 1

“查找除 1 和 15 之外的所有列都为空的位置”：

SELECT * 
FROM temp
WHERE
  --col1 is PK and won't be null so we don't need to check it
  col2 IS NULL AND
  col3 IS NULL AND
  ...
  col14 IS NULL AND
  col15 IS NOT NULL --check it if it's a hard requirement that it must still have a value

请注意，虽然来自 Tejash 的查询可以工作，但作为一般规则，请尽量避免在 where 子句中使用对表值进行操作的函数，因为这意味着 oracle 必须为每一行评估函数的结果（可能是数百万评估）并且它通常禁用索引的使用）除非指定了功能索引，这很少见）。在这种情况下，除非您创建另一个带有标志的列以指示所有字段是否为空，否则此查询甚至不可能被明智地编制索引，但 thisbadvuce 可作为真正更一般情况的警告；例如，不要将一百万个日期转换为字符串以与单个字符串参数竞争，将单个字符串参数转换为日期并将其与表中的日期进行比较（非转换行数据）