在一行上显示"*" [英] display a ‘*’ against a row

问题描述

我需要写一个查询来在最近雇用的雇员的行上显示一个"*".这是我的代码.还有其他方法可以优化实现相同目标吗?

I need to write a query to display a ‘*’ against the row of the most recently hired employee. Here''s my code. Is there any other way to achieve the same in an optimized manner?

SELECT  
	CASE WHEN HireDate =	
		(
		SELECT 
			Max(HireDate) 
		FROM 
			Employment
		) 
		THEN EmployeeName + ' ' + '*'
		
		ELSE 
			EmployeeName  
		END AS EmployeeName, 
	EmployeeNo , 
	HireDate
FROM 
	Employment

推荐答案

您正在执行的操作是为每一行选择max(hireddate)，而您只需要执行一次.

查询的选择扇区中的任何内容都将针对每一行进行评估
from部门中的任何内容仅评估一次.

您可以事先评估日期并进行比较.日期字段对于将索引作为排序非常有效
但是，还有另一种处理聚合的方法:公用表表达式(CTE)

您可以预定义仅在查询中使用时才评估的表.这样可以有效地订购和选择模板:

You are performing that select max(hireddate) for each and every row where you only need to do this once.

Anything in the select sector of a query will be evaluated for each row
Anything in the from sector is assessed only once.

You could asses the date before hand and compare to that. Date fields a pretty efficient for indexing as sorting
But there is another way for working with aggregates: Common Table Expressions (CTE)

you can predefine a table that is assessed only when it is used in the query. This makes for an efficient ordering and selecting template:

with my_cte as ( -- define the cte
select EmployeeId,Row_Number() over (order by HireDate desc) as row
from Employment
) -- must be followed by another cte or a select statement
SELECT  
	EmployeeName + -- You can swap your case select to make it look a little neater
            CASE row WHEN 1 then '*' else '' end AS EmployeeName, 
	EmployeeNo , 
	HireDate
FROM 
	Employment e
inner join my_cte on e.EmployeeId= my_cte.EmployeeId

我总是跑到CTE的原因是因为这些聚合可以发挥多大作用
例如:假设我们现在希望每个部门的最新雇员都有*号，我们可以像这样更改ROW_NUMBER的CTE:

The reason I would always run to a cte is because of how useful is can be with these kind of aggregates
For example: Say we now want the most recent employee hire from each department to have a * we can alter the cte ROW_NUMBER like so:

with my_cte as ( -- define the cte
select EmployeeId,Row_Number() over(PARTITION BY department order by HireDate desc) as row
from Employment
) 

此更改的作用是重置每个部门的行号，因此每个部门的最新雇员将显示*

或者，您可以在from区域中执行选择:

This change has the effect of resetting the row numbering for each department so each of the departments latest employee would show the *

Alternatively, you could perform your select in the from sector:

SELECT  
	EmployeeName + 
		CASE
			WHEN HireDate = aggregatevalue.date THEN  ' *'
			ELSE '' 
		END AS EmployeeName, 
	EmployeeNo , 
	HireDate
FROM 
	Employment,
		(
	SELECT 
		Max(HireDate) as date
	FROM 
		Employment
	) as aggregatevalue

没有表联接，但是聚合表将只有1行，因此不会有问题

there is no table join but the aggregate table will only have 1 row so it won''t be a problem

这篇关于在一行上显示"*"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！