当select top（n）记录返回较少的记录时，如何将NULL作为值 [英] How to get NULL as value when select top (n) records return less record

问题描述

大家好，



我有一个查询是：从FEmployee E中选择前5名薪水，其中E.Designation ='经理'



此查询返回4条记录，因为只有4条记录符合条件。



现在我想要的是什么时候它返回4条记录，我选择前5条记录然后它应该返回我的第5条记录为NULL



请帮助我实现这一点。我真的被困了。



谢谢。



Krunal

解决方案

类似

 选择 < span class =code-keyword> top   5  salary 来自 
（ 
 选择  top   5 薪水来自员工其中指定= '  Manager' 
  union   all  
 选择  null  来自 employee 
 
） AS  ABC 

应该做什么的你想要它。

更优雅的解决方案可能是

  DECLARE   @ TBL   TABLE  
（
 EMPID  INT ，
 DESIGNATION  VARCHAR （ 20 ），
 SALARY  FLOAT  
）
 
  INSERT   INTO   @ TBL （EMPID，DESIGNATION，SALARY）
  SELECT   102 ，'  MANAGER'， 55000  
  UNION  所有 
  SELECT   234 ，' <跨度class =code-string> MANAGER'， 45000  
  UNION  < span class =code-keyword> ALL  
  SELECT   345 ，< span class =code-string>'  MANAGER'， 35000  
  UNION   ALL  
  SELECT  < span class =code-digit> 446 ，'  MANAGER'， 25000  
 
  SELECT  T.SALARY 
  FROM  
（选择  1   as  ROWID 
  UNION   ALL  
 选择 < span class =code-digit> 2  
  UNION   ALL  
 选择  3  
  UNION   ALL  
 选择  4  
  UNION   ALL  
  select   5  
） LEFT   OUTER   JOIN  
（
  SELECT   TOP   5  ROW_NUMBER（） OVER （ ORDER   BY  EMPID） AS  ROWID，* 
  FROM   @ TBL  T 
）T  ON  T.ROWID = A.ROWID 

Hello everyone,

I have one query which is : select top 5 Salary from FEmployee E where E.Designation ='Manager'

this query returns me 4 records as there are only 4 records which match the criteria.

Now what i want is when it returns 4 records and i select top 5 records then it should return me 5th record as NULL

Please help me in achieving this. I am really stuck up.

Thanks.

Krunal

解决方案

Something like

Select top 5 salary from
(
select top 5  salary from Employee where designation = 'Manager'
union all
select null from employee

) AS ABC

should do what you want it to.

A more elegant solution could be like

DECLARE @TBL TABLE
(
	EMPID INT,
	DESIGNATION VARCHAR(20),
	SALARY FLOAT
)

INSERT INTO @TBL (EMPID, DESIGNATION, SALARY)
SELECT 102, 'MANAGER', 55000
UNION ALL
SELECT 234, 'MANAGER', 45000
UNION ALL
SELECT 345, 'MANAGER', 35000
UNION ALL
SELECT 446, 'MANAGER', 25000

SELECT T.SALARY
FROM 
	(select 1 as ROWID
	UNION ALL
	select 2
	UNION ALL
	select 3 
	UNION ALL
	select 4 
	UNION ALL
	select 5 
	) A LEFT OUTER JOIN 
	(
	SELECT TOP 5 ROW_NUMBER() OVER (ORDER BY EMPID) AS ROWID, *
	FROM @TBL T
	) T ON T.ROWID = A.ROWID

这篇关于当select top（n）记录返回较少的记录时，如何将NULL作为值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！