在一行上显示"*" [英] 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屋!