如何在列中找到第一个非空值 [英] How to find first not null value in column
问题描述
如何在整列表格列表中找到第一个非空值
（如果有的话）一个not null值然后告诉我它并停止搜索，
表非常大）？
thx，
Martin
***通过开发人员指南 http：// www。 developersdex.com ***
Martin R（ma******@go2.pl）写道：如何在列whitout chacking整个表中找到第一个非空值
（如果有一个非空值然后显示它并停止搜索，
表格相当大）？
SELECT TOP 1 col FROM tbl WHERE col IS NOT NULL
请注意first这里没有极其明确的定义，因为
a定义的表是一组无数的数据。
如果没有涉及col的行索引，SQL Server很可能从左到右扫描聚簇索引。 （但理论上它可以打开并行流，并在中间给你一行。）
如果有一个索引涉及col，SQL Serer可能会扫描该指数的b
。如果有一个索引作为第一列的col，
SQL Server可能会寻找该索引，并且您将得到col的最低值
。如果你然后改变SELECT col要选择*，您可以再次将
重新打开表扫描。
如果您有任何其他标准来定义此第一个 ;值，然后
你需要在查询中添加一个ORDER BY子句。
-
Erland Sommarskog，SQL Server MVP， es **** @ sommarskog.se
SQL Server 2005联机丛书
http://www.microsoft。 com / technet / pro ... ads / books.mspx
SQL Server 2000联机丛书
http://www.microsoft.com/sql/prodinf...ons/books.mspx
>>如何在不查询整个表的情况下在列中找到第一个非空值<<
让我们回到RDBMS的基础知识。行不是记录;字段
不是列;表不是文件;在RDBMS中没有顺序访问或
排序，因此first，next和和最后完全没有意义的b $ b无意义。如果您想要订购，那么您需要有一个定义该订购的列
。你必须在
游标上使用ORDER BY子句。
接下来，你谈论的是SQL，就像你在文件系统中一样，
您一次读取一条记录并通过
程序语句获得明确的控制流程。这也完全错了; SQL是一种
声明式编译语言。
让我们根据你发布的内容猜测DDL是什么>
看起来像：
CREATE TABLE Foobar
（foo_key INTEGER NOT NULL PRIMARY KEY， - 由他订购??
klugger INTEGER， - 目标栏？
..）;
SELECT foo_key
FROM Foobar
WHERE klugger IS NULL
AND foo_key
=（SELECT MIN（foo_key）FROM Foobar）;
（如果有一个非空值然后显示它并停止搜索，
表非常大）？
显示NULL值那不存在？这没有任何意义。
也没有停止搜索，因为SQL是一种面向集合的语言。
你得到整个结果集;它可以是空的，也可以有任意行的行数。
你需要阅读一本关于RDBMS基础知识的书。你错过了最重要的
重要概念。
如果klugger中没有NULL，那么你将得到一个空的回调。
2006年1月28日11:51:33 -0800， - CeKO--写道：
< blockquote class =post_quotes>如何在不查询整个表的情况下在列中找到第一个非空值<<
（snip）让我们根据什么做出猜测你发布了DDL
的样子：
创建表Foobar
（foo_key INTEGER NOT NULL PRIMARY KEY， - 由他订购??
klugger INTEGER， - 目标栏？
..）;
SELECT foo_key
FROM Foobar
在哪里klugger IS NULL
和foo_key
=（SELECT MIN（foo_key）FROM Foobar）;
HHi Joe，
我希望你真的打算发表
SELECT MIN（foo_key）
来自Foobar
在哪里klugger是空的
-
Hugo Kornelis，SQL Server MVP
Hi,
How to find first not null value in column whitout chacking whole table
(if there is a not null value then show me it and stop searching, the
table is quite big)?
thx,
Martin
*** Sent via Developersdex http://www.developersdex.com ***
Martin R (ma******@go2.pl) writes:How to find first not null value in column whitout chacking whole table
(if there is a not null value then show me it and stop searching, the
table is quite big)?
SELECT TOP 1 col FROM tbl WHERE col IS NOT NULL
Note that "first" here is not extremly well-defined, as a table by
a defintion is a unorded set of data.
If there is no index that involves col at row, SQL Server is likely
to scan the clustered index from left to right. (But in theory it
could open parallel steams, and give you a row in the middle.)
If there is an index that involves col, SQL Serer is likely to scan
that index. And if there is an index with col as the first column,
SQL Server is likely to seek that index, and you would get the lowest
value of col. If you then change "SELECT col" to "SELECT *", you may
be back on the table scan again.
If you have any additional criteria to define this "first" value, then
you need to add an ORDER BY clause to the query.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
>> How to find first not null value in column without chacking whole table <<
Let''s get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files; there is no sequential access or
ordering in an RDBMS, so "first", "next" and "last" are totally
meaningless. If you want an ordering, then you need to havs a column
that defines that ordering. You must use an ORDER BY clause on a
cursor.
Next, you are talking about SQL as if you were in a file system, where
you read one record at a time and have explicit control flow via
procedural statements. That is also totally wrong; SQL is a
declarative, compiled language.
Let''s make a guess, based on nothing you posted, as to what the DDL
looks like:
CREATE TABLE Foobar
(foo_key INTEGER NOT NULL PRIMARY KEY, -- order by him??
klugger INTEGER, -- target column ??
..);
SELECT foo_key
FROM Foobar
WHERE klugger IS NULL
AND foo_key
= (SELECT MIN(foo_key) FROM Foobar);
(if there is a not null value then show me it and stop searching, the
table is quite big)?
Show you the NULL value that does not exist? That makes no sense.
Neither does "stop searching", since SQL is a set-oriented language.
You get the entire result set back; it can be empty or it can have any
number of rows.
You need to read a book on RDBMS basics. You have missed the most
important concepts.
If there is no NULL in klugger, then yuou will get an empty set back.
On 28 Jan 2006 11:51:33 -0800, --CELKO-- wrote:
How to find first not null value in column without chacking whole table <<(snip)Let''s make a guess, based on nothing you posted, as to what the DDL
looks like:
CREATE TABLE Foobar
(foo_key INTEGER NOT NULL PRIMARY KEY, -- order by him??
klugger INTEGER, -- target column ??
..);
SELECT foo_key
FROM Foobar
WHERE klugger IS NULL
AND foo_key
= (SELECT MIN(foo_key) FROM Foobar);
HHi Joe,
I do hope that you actually intended to post
SELECT MIN(foo_key)
FROM Foobar
WHERE klugger IS NULL
--
Hugo Kornelis, SQL Server MVP
这篇关于如何在列中找到第一个非空值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
