在SQL +中选择Query以列中的所有值

I am having a table Table1 with columns id1, id2, id3 all the columns are nullable

我有一个表Table1与列id1,id2,id3所有列都可以为空

I may enter null or value to all columns in rows.

我可以为行中的所有列输入null或value。

My question is I need to select the rows whose all the column values should not be null.

我的问题是我需要选择所有列值不应为空的行。

Thanks

There are totally around 300 columns in the table. I can't do the is null property for all the columns in where condition.

表中总共有大约300列。我不能为where条件中的所有列执行null属性。

9 个解决方案

#1

You need to do this:

你需要这样做:

SELECT *
FROM yourtable
WHERE
    column1 IS NOT NULL
AND column2 IS NOT NULL
AND column3 IS NOT NULL
AND ....

#2

The answer to use a "function" to test the null-values is correct. The syntax depends on the database. If ISNULL() does not exist in your database then try:

使用“函数”来测试空值的答案是正确的。语法取决于数据库。如果数据库中不存在ISNULL(),请尝试:

SELECT * FROM Table1 WHERE id1 IS NOT NULL AND id2 IS NOT NULL AND id3 IS NOT NULL

SELECT * FROM Table1 WHERE id1 IS NOT NULL且id2 IS NOT NULL且id3 IS NOT NULL

And there is no way to short this down even if you have 300 fields in your table.

即使您的桌子中有300个字段,也无法将其缩短。

#3

Don't understand why this question is getting negitive reviews - This question can be extended to people who inherited a large table from a non-programmer in a community (I know from previous experience), and likewise if the table is unknown. To downgrade this because its '300' columns is pointless IMO.

不明白为什么这个问题会得到负面的评论 - 这个问题可以扩展到从社区中的非程序员那里继承大表的人(我从以前的经验中知道),同样如果表是未知的。降级这是因为它的'300'列是毫无意义的IMO。

#4

Best bet is to either rethink the design of your tables, splitting them if required.

最好的办法是重新考虑表格的设计,如果需要可以拆分它们。

otherwise best bet is do it progmatically - grab the table metadata, itterate through the columns and drynamically create the SQL from there. Most coding languages have access to the tables metadata, failing that a second SQL is required for it.

否则最好的选择是以进程方式执行 - 抓取表元数据,遍历列并从那里干燥地创建SQL。大多数编码语言都可以访问表元数据,因为它不需要第二个SQL。

But, best bet is to think how can I design the table better.

但是,最好的办法是考虑如何更好地设计表格。

#5

Are you saying you want to select the rows where none of the columns are null?

您是说要选择没有列为空的行?

 SELECT id1, id2, id3 
FROM Table1 
WHERE id1 IS NOT NULL AND id2 IS NOT NULL AND id3 IS NOT NULL

#6

Sorry - I might be being a bit thick here. You're trying to get back the rows that have got SOMETHING in one of the columns (other than the id column)?

对不起 - 我这里可能有点厚。你试图在其中一列(除了id列)之前找回已经有SOMETHING的行?

Can't you do;

你不能这样做;

create vw_View_Fields1to5 as 
  select id from employees 
  where name is not null or description is not null or field3 is not null 
  or field4 is not null or field5 is not null;
create vw_View_Fields6to10 as 
  select id from employees 
  where field6 is not null or field7 is not null or field8 is not null 
  or field 9 is not null or field10 is not null;
(etc)

select id from vw_View_Fields1to5
union 
select id from vw_View_Fields6to10 .... (etc)

You'd have to take a DISTINCT or something to cut down the rows that fall into more than one view, of course.

当然,你必须采用DISTINCT或其他东西来减少属于多个视图的行。

If you want the rows back that have NOTHING in any column other than id, you'd switch 'or blah is not null' to be 'and blah is null' (etc).

如果你想在除id之外的任何列中返回没有NOTHING的行,你可以将'或blah is not null'改为'而blah为null'(等等)。

Does that make sense... or am I missing something? :-)

那是否有意义......或者我错过了什么? :-)

EDIT: Actually, I believe the UNION process will only bring back distinct rows anyway (as opposed to UNION ALL), but I could be wrong - I haven't actually tried this.... (yet!)

编辑:实际上,我相信UNION进程只会带回不同的行(而不是UNION ALL),但我可能错了 - 我实际上没有尝试过这个....(还是!)

#7

you can try CLR stored procedure (if you're using SQL Server) or move this logic to the other layer of your application using C# or whatever language you're using.

您可以尝试CLR存储过程(如果您正在使用SQL Server)或使用C#或您正在使用的任何语言将此逻辑移动到应用程序的其他层。

another option is to create the query dynamically, concatenating your WHERE clause and EXECute your dynamically generated query.

另一种选择是动态创建查询,连接WHERE子句和EXECute动态生成的查询。

#8

Are you just reading the data, or will you want to try and update the rows in question?

您只是在阅读数据,还是想尝试更新相关行?

I'm just wondering if there's something you can go by making a half-dozen views, each one based on say 50 columns being NOT NULL, and then linking them with some kind of EXISTS or UNION statement?

我只是想知道是否有一些东西可以通过制作六个视图,每一个基于50个列是非NULL,然后将它们与某种EXISTS或UNION语句链接?

Can you tell us a bit more about what you want to do with your result set?

您能告诉我们您想要对结果集做些什么吗?

#9

For the first time whatever Georgi or engram or robsoft is the way. However for subsequent stuff you can if possible alter the table and add one more column, called CSELECTFLAG, and initially updated this to Y for all columns that have values and N for others. Everytime there is an insert this needs to be updated. This would help make your subsequent queries faster and easier.

无论是Georgi还是engram或者robsoft,这是第一次。但是对于后续内容,您可以在可能的情况下更改表并添加一个名为CSELECTFLAG的列,并且最初将此更新为Y,以用于所有具有值的列和其他值的N.每次有插入时都需要更新。这有助于您更快,更轻松地进行后续查询。

#1