如何从表中获取 NULL 值记录? [英] How can i get the NULL valued records from a table?

问题描述

在我的表中有 1,00,000 条记录，其中有 1000 行和 100 列，表中的某些记录具有 NULL 值，我想要的是如果记录的整行的 Null 值将出现在第一个我的表很容易检查哪一行包含和哪一列包含 NULL 值

In my table 1,00,000 records are there, where 1000 rows and 100 columns are there, some of the records in the table have NULL values, what i want is If the Null valued recorded of entire rows will come to first of my table it can be easy to me to check which row contains and which column contains NULL values

1. 如果ROW/COLUM的值=NULL，首先打印所有空值的行和列
2. 非空值列打印下一个空值行

我的表是这样的:

Staff_ID First_Name    Last_Name

  1   Stephen   Jiang
  2   Ravi      chaganti
  3   Michael   Blythe
  4   NULL      NULL
  5   Jillian   NULL
  6   NULL      Vargas

我想要这样的输出:

Staff_ID  First_Name  Last_Name

  4   NULL      NULL
  5   Jillian   NULL
  6   NULL      Vargas
  1   Stephen   Jiang
  2   Ravi      chaganti
  3   Michael   Blythe

我需要一个查询.

推荐答案

select Staff_ID, First_Name, Last_Name
from YourTable
order by cast(Staff_ID as varchar(10))+First_Name+Last_Name,
         Staff_ID

NULL 值将首先排序.

当用 NULL 连接一个值时，结果是 NULL 所以 First_Name+Last_Name 将是 NULL 如果有的话一种是NULL.

When concatenating a value with NULL the result is NULL so First_Name+Last_Name will be NULL if any one is NULL.

cast(Staff_ID as varchar(10))+... 是否可以保证 First_Name+Last_Name 为 not NULL(我假设 Staff_ID 是 int).

cast(Staff_ID as varchar(10))+... is there guarantee the order you want for the case when First_Name+Last_Name is not NULL (I assume Staff_ID is int).

并且按列 Staff_ID 的额外顺序是为了保证 First_Name+Last_Name is 的所有行的顺序>NULL

And the extra order by column Staff_ID is there to guarantee the order you want for all the rows where First_Name+Last_Name is NULL

更新

您可以使用 INFORMATION_SCHEMA.COLUMNS 动态构建查询.这可能会做你想做的.请注意，这并未针对所有类型的数据类型进行测试.

You can build the query dynamically using INFORMATION_SCHEMA.COLUMNS. This might do what you want. Note, this is not tested for all kinds of data types.

declare @TableName sysname  = 'YourTable'
declare @Sql nvarchar(max) = 'select * from '+quotename(@TableName)+ ' order by '

select @Sql = @Sql+'+cast('+COLUMN_NAME+' as varchar(max))'
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = @TableName

exec (@Sql)

更新 2

使用 XML 的非动态版本.

A non dynamic version using XML instead.

;with C as 
( 
  select *,
         row_number() over(order by (select 1)) as rn
  from YourTable
)
select C1.*
from C as C1
  cross apply (select *
               from C as C2
               where C1.rn = C2.rn
               for xml path('x'), type) as T(N)
order by T.N.value('count(/x/*)', 'int')

更新 3

如果您知道表的主键并且不介意输入 where 子句，您可以使用它而不是使用 row_number().

If you know the primary key of your table and don't mind typing the where clause you can use this instead of using row_number().

select Y1.*
from YourTable as Y1
order by (select *
          from YourTable as Y2
          where Y1.Staff_ID = Y2.Staff_ID
          for xml path('x'), type).value('count(/x/*)', 'int')

这篇关于如何从表中获取 NULL 值记录?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！