How can i get the NULL valued records from a table

问题:

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. If the Value of ROW/COLUM=NULL print all the null valued Rows and columns First
2. Non-Null valued columns print next of the NULL valued rows

My Table is like this:

Staff_ID First_Name    Last_Name

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

and I want output like this:

Staff_ID  First_Name  Last_Name

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

I need a query for this.

回答1:

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

NULL values will be ordered first.

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))+... is there guarantee the order you want for the case when First_Name+Last_Name is not NULL (I assume Staff_ID is int).

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

Update

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)

Update 2

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')

Update 3

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')

回答2:

select Staff_ID, First_Name, Last_Name FROM [tablename] would get you the output you described ;P nothing in particular needed to print out the null values.

If you want to look for specific null values use WHERE columnname IS NULL

Edit: if you are just looking to sort by null first just do a ORDER BY Last_Name, First_Name ASC/DESC depending on what way you want to sort it.

回答3:

SELECT Staff_ID, First_Name, Last_Name
FROM myTable
ORDER BY Last_Name DESC

Also try this:

SELECT Staff_ID, First_Name, Last_Name
FROM myTable
ORDER BY (CASE WHEN Last_Name IS NULL THEN 0 ELSE 1 END), Last_Name DESC

回答4:

select Staff_ID , First_Name , Last_Name from Staff where (First_Name Is Null or  

Last_Name is Null)

回答5:

'Union' is the answer. 

(SELECT Staff_ID, First_Name, Last_Name
FROM myTable
Where First_Name is null AND Last_Name is null)
Union
(SELECT Staff_ID, First_Name, Last_Name
FROM myTable
Where First_Name is not null AND Last_Name is null)
union
(SELECT Staff_ID, First_Name, Last_Name
FROM myTable
Where First_Name is null AND Last_Name is not null)
union
(SELECT Staff_ID, First_Name, Last_Name
FROM myTable
Where First_Name is not null AND Last_Name is not null)

Query with order by will be challenging so Here it is:

Select Staff_ID, First_Name, Last_Name FROM
(
    (SELECT '1' as firstSortPref, Staff_ID, First_Name, Last_Name
    FROM myTable
    Where First_Name is null AND Last_Name is null)
    Union
    (SELECT '2' as firstSortPref, Staff_ID, First_Name, Last_Name
    FROM myTable
    Where First_Name is not null AND Last_Name is null)
    union
    (SELECT '3' as firstSortPref, Staff_ID, First_Name, Last_Name
    FROM myTable
    Where First_Name is null AND Last_Name is not null)
    union
    (SELECT '4' as firstSortPref, Staff_ID, First_Name, Last_Name
    FROM myTable
    Where First_Name is not null AND Last_Name is not null)
) as tbl1 order by tbl1.firstSortPref,  tbl1.First_Name, tbl1.Last_Name