我有两张工作和约会桌
我要获取以job\u reference='1%”开始的所有活动工单，以及最新的活动约会。创建时间和与之关联的约会类型，由约会排序。创建时间
一份工作可以有很多约会
如果与作业关联的所有约会都已删除\u flag=1，则结果集应返回tbl \u job.created-when作为最新的\u约会\u日期

CREATE TABLE [dbo].[tbl_job]
(

    [job_id] UNIQUEIDENTIFIER NOT NULL,
    [account_id] INT NOT NULL,
    [description] NVARCHAR(1024) NULL, 
    [deleted_flag] BIT NOT NULL,
    [created_when] DATETIME2 (6) NOT NULL,
    [job_reference] NVARCHAR(15) NOT NULL
)

CREATE TABLE [dbo].[tbl_appointment]
(

    [appointment_id] UNIQUEIDENTIFIER NOT NULL,
    [job_id] UNIQUEIDENTIFIER NOT NULL,
    [deleted_flag] BIT NOT NULL,
    [appointment_type] NVARCHAR(35) NOT NULL,
    [created_when] DATETIME2 (6) NOT NULL,
    [account_id] INT NULL,
)

insert into dbo.tbl_job (job_id,    account_id, [description],  deleted_flag,   created_when,   job_reference) values
('A29A6381-EF0D-47F6-BFC3-051679E343D0',    1,  'descr1',   0,  '2020-06-12 00:58:17.7221410',  1  )
,('4D8C1B04-9E00-41FA-BDB8-653C26712144',   1,  'descr2',   0,  '2020-06-10 00:58:17.7221410',  12 )
,('F8DC690E-74AB-46F4-90D3-55E032F21C99',   1,  'descr3',   0,  '2020-06-26 00:58:17.7221410',  123 )
,('C5D8AA45-FA45-41A4-877D-5B803C1BE61B',   1,  'descr4',   0,  '2020-06-27 00:58:17.7221410',  76 )

insert into dbo.tbl_appointment(appointment_id, job_id, deleted_flag,   appointment_type,   created_when,   account_id) Values
('9E24451F-5703-414F-ACF1-9304AFBEA8F1',    'A29A6381-EF0D-47F6-BFC3-051679E343D0', 0,  'job1_cat1',    '2020-06-12 00:58:17.7221410',  1)
,('A8121DC1-271E-4BD0-A6AA-D753CF4D310E',   'A29A6381-EF0D-47F6-BFC3-051679E343D0', 0,  'job1_cat2',    '2020-06-14 00:58:17.7221410',  1)
,('61ED5B48-DF95-4FC8-AF1D-1418C6DD9088',   '4D8C1B04-9E00-41FA-BDB8-653C26712144', 0,  'job2_cat1',    '2020-06-15 00:58:17.7221410',  1)
,('0e4fc735-96c3-4cab-8ade-796bae4639d1',   'F8DC690E-74AB-46F4-90D3-55E032F21C99', 1,  'job3_cat1',    '2020-06-28 00:58:17.7221410',  1)

预期结果集

job_id                                  job_reference       latest_appointment_date         appointment_type total_rows
F8DC690E-74AB-46F4-90D3-55E032F21C99    123                 2020-06-26 00:58:17.722141      NULL                3
4D8C1B04-9E00-41FA-BDB8-653C26712144    12                  2020-06-15 00:58:17.722141      job2_cat1           3
A29A6381-EF0D-47F6-BFC3-051679E343D0    1                   2020-06-14 00:58:17.722141      job1_cat2           3

下面的查询可以工作，但它不是一个有效的方法，因为表中有数百万行。我想用左连接或其他方法来替换外部应用程序，以提高效率

DECLARE @filtered_jobs TABLE
    (
         job_domain_id UNIQUEIDENTIFIER
        ,job_reference NVARCHAR(15)
        ,job_created_when DATETIME2(6)
        ,latest_appointment_date DATETIME2(6)
        ,appointment_type NVARCHAR(35)

    );
    declare @account_id int = 1
    declare     @job_reference nvarchar(35) = '1'
    declare     @offset int  = 0
    declare     @limit int = 10
    declare     @is_sort_ascending int = 0

            INSERT INTO @filtered_jobs (job_domain_id, job_reference, job_created_when, latest_appointment_date,appointment_type)
            SELECT   
                    j.job_id
                    ,j.job_reference
                    ,j.created_when
                    ,ap.created_when AS latest_appointment_date
                    ,ap.appointment_type
            FROM    dbo.tbl_job j
                    OUTER APPLY (
                        SELECT TOP (1) ap.appointment_type,ap.created_when,ap.deleted_flag
                        FROM    dbo.tbl_appointment ap 
                        WHERE   ap.job_id = j.job_id AND ap.deleted_flag = 0
                        ORDER BY ap.created_when desc
                    ) ap
            WHERE   j.account_id =  @account_id
                    AND j.job_reference LIKE (@job_reference + '%')
                    AND j.deleted_flag = 0

    SELECT 
             fj.job_domain_id
            ,fj.job_reference
            ,ISNULL(fj.latest_appointment_date,fj.job_created_when) AS latest_appointment_date
            ,fj.appointment_type
    FROM    @filtered_jobs fj
    ORDER BY 
            CASE WHEN @is_sort_ascending = 0 THEN ISNULL(fj.latest_appointment_date,fj.job_created_when) END DESC,
            CASE WHEN @is_sort_ascending = 1 THEN ISNULL(fj.latest_appointment_date,fj.job_created_when)  END ASC
            OFFSET @offset ROWS FETCH NEXT @limit ROWS ONLY;

    SELECT  COUNT(1) AS total_records 
    FROM    @filtered_jobs;