sql - sql - 对于Liquibase createView,是否可以存储子搜索的值?

我尝试在SQL Server中使用liquiBase yaml changelog文件创建一个视图,其中每行看起来像:


employee_id | provider_id | days_remaining | employed_dates



还有三个表

  • EMPLOYEE - employee_id ,first_name ,last_name
  • JOB - job_id ,employee_id ,start_date ,end_date ,provider_id
  • ASSIGNMENT - assignment_id ,job_id ,employee_id ,start_date ,end_date ,provider_id

  • provider_idjob_idprovider_id它有此employee_id的最新end_date
  • days_remaining是上一个的最后一个分配结束日的天数
  • employed_dates是用于上述provider_idemployee_id的所有assignment_id的连接

到目前为止我的代码:


databaseChangeLog:


 - changeSet:


 id: 100


 author: 100


 dbms:"mssql"


 changes:


 - createView:


 schemaName: ${schemaName}


 viewName: employee_history_view


 replaceIfExists: true


 selectQuery: >


 SELECT EMPLOYEE.employee_id,


 (


 SELECT TOP 1 provider_id from JOB


 where JOB.employee_id = employee_id


 order by end_date DESC


 ) as provider_id,


 employee.first_name,


 employee.last_name,


 DATEDIFF(DAY,


 (


 SELECT max(ASSIGNMENT.end_Date)


 from ASSIGNMENT


 where ASSIGNMENT.employee_id = EMPLOYEE.employee_id


 and ASSIGNMENT.provider_id = (


 SELECT TOP 1 provider_id from JOB


 where JOB.employee_id = employee_id


 order by end_date DESC


 )


 and end_date < getDate()


 ),


 getdate() ) as days_remaining,


 (


 '[' + SUBSTRING (


 (


 SELECT TOP 100 ', {"start":"'+CONVERT(CHAR(10),ASSIGNMENT.start_date,120)+'","end":"'+CONVERT(CHAR(10),ASSIGNMENT.end_date,120)+'"}'


 from ASSIGNMENT


 where ASSIGNMENT.employee_id = EMPLOYEE.employee_id


 and ASSIGNMENT.provider_id = (


 SELECT TOP 1 provider_id from JOB


 where JOB.employee_id = EMPLOYEE.employee_id


 order by end_date DESC


 )


 order by program_type DESC


 for xml path ('')


 ), 2, 8000) + ']'


 ) as employed_dates


 FROM ${employeeSchema}.EMPLOYEE



我有两个问题:

是否可以避免重复计算provider_id (以下代码)三遍?


SELECT TOP 1 provider_id 


FROM JOB 


WHERE JOB.employee_id = employee_id 


ORDER BY end_date DESC 



时间:


 INNER JOIN (


 SELECT DISTINCT employee_id, provider_id from JOB


 ) as DT


 ON EMPLOYEE.employee_id = DT.employee_id



...