SQL返回包含非NULL数据的字段列表 [英] SQL to return list of fields containing non-NULL data

问题描述

如何从给定表中返回包含任何非NULL数据的字段列表?

How can I return a list of fields from a given table that contain any non-NULL data?

作为示例，我如何查询下面的示例表，以仅返回以下三个值，因为它们包含非NULL的值.

As en example, how could I query the example table below to only return the following three values as they contain something other than NULL.

• LO2_HiddenOrgID
• LO2_BranchOfOrgID
• LO2_ShortName

由于要检查数千个字段，因此我希望尽可能地使它自动化.

I'm looking to automate this as much as possible as I have a few thousand fields to inspect.

此外，我目前在开发时使用SQLite，但很乐意接受有关SQLite，MySQL或PostgreSQL的任何建议.

Also, I'm currently using SQLite while developing, but would happily accept any suggestions specific to SQLite, MySQL or PostgreSQL.

推荐答案

因此，您的目标是获取列名称的列表，以使它们全部具有至少一个非NULL.值在任何行中，对不对?如果是这样，请参见下文...

So your goal is to get the list of column names such that all of them have at least one non-NULL value in any of the rows, right? If so, see below...

您无法对SQL查询中的列名称进行参数设置，因此您需要以所选的客户端语言动态构建SQL文本.该算法将如下所示:

You can't parametrize names of columns in an SQL query, so you'll need to build your SQL text dynamically, in the client language of your choosing. The algorithm would look like this:

1. 您必须事先知道列名列表.在 PostgreSQL ， MySQL 和大多数其他数据库.
2. 遍历此列表，并为每个column_name动态构建SQL文本，例如:SELECT column_name FROM YOUR_TABLE WHERE column_name IS NOT NULL LIMIT 1(请参见MySQL LIMIT ).
3. 执行上面的查询并获取结果.如果其中有一行，请将column_name添加到结果列表.
4. 在列名称列表中有元素时保持迭代.

1. You'll have to know the list of column names in advance. There are ways to automate retrieving this list in PostgreSQL, MySQL and most other databases.
2. Iterate through this list and for each column_name dynamically build the SQL text such as: SELECT column_name FROM YOUR_TABLE WHERE column_name IS NOT NULL LIMIT 1 (see the MySQL LIMIT and PostgreSQL LIMIT).
3. Execute the query above and fetch the result. If has a row in it, add the column_name to the resulting list.
4. Keep iterating while there are elements in the column name list.

现在，结果列表包含具有至少一个非NULL值的列.

The resulting list now contains columns with at least one non-NULL value.

这篇关于SQL返回包含非NULL数据的字段列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！