时间戳

首页 > 解决方案 > 通过变量记录集

sql - 通过变量记录集

问题描述

我有一个工作正常但需要一些调整的记录集。

这就是我所拥有的：

SELECT
    *
FROM
    dbo.profiles
    LEFT JOIN
    dbo.self_assess
        ON dbo.profiles.profile_id = dbo.self_assess.self_assess_profile_id
            And dbo.self_assess.sa_year = 3
    LEFT JOIN
    dbo.lesson_observation
        ON dbo.profiles.profile_id = dbo.lesson_observation.lesson_obsv_profile_id
            And dbo.lesson_observation.lo_year = 3
    LEFT JOIN
    dbo.staffappraisal_head_dept
        ON dbo.profiles.profile_id = dbo.staffappraisal_head_dept.sa_hd_profile_id
            And dbo.staffappraisal_head_dept.sahd_year = 3
    LEFT JOIN
    dbo.staffappraisal_main_meeting
        ON dbo.profiles.profile_id = dbo.staffappraisal_main_meeting.sa_mainappraisal_teacherid
            And dbo.staffappraisal_main_meeting.samm_year = 3
    LEFT JOIN
    dbo.staffappraisal_mainapprs_followup
        ON dbo.profiles.profile_id = dbo.staffappraisal_mainapprs_followup.sa_ma_fup_teachers_id
            And dbo.staffappraisal_mainapprs_followup.samafu_year = 3
WHERE
    lesson_obsv_by_id = VarLesson
    OR sa_hd_hod_id = VarHod
    OR sa_mainappraisal_appraiser_id = VarMainApp
    OR sa_ma_fup_appraiser_id = VarMainFollow
    OR self_assess_hod_id = VarSelf
    AND lo_year = varyear1
    AND sa_year = varyear2
    AND sahd_year = varyear3
    AND samm_year = varyear4
    AND samafu_year = varyear5

我想要的是（3）

dbo.lesson_observation.lo_year = 3

等于可以调用的变量ACTIVEYEAR

问候

维托

标签： sqlrecordset

解决方案

你是这个意思吗？

DECLARE @ActiveYear INT = 3

SELECT
    *
FROM
    dbo.profiles
    LEFT JOIN
    dbo.self_assess
        ON dbo.profiles.profile_id = dbo.self_assess.self_assess_profile_id
            And dbo.self_assess.sa_year = @ActiveYear
    LEFT JOIN
    dbo.lesson_observation
        ON dbo.profiles.profile_id = dbo.lesson_observation.lesson_obsv_profile_id
            And dbo.lesson_observation.lo_year = @ActiveYear
    LEFT JOIN
    dbo.staffappraisal_head_dept
        ON dbo.profiles.profile_id = dbo.staffappraisal_head_dept.sa_hd_profile_id
            And dbo.staffappraisal_head_dept.sahd_year = @ActiveYear
    LEFT JOIN
    dbo.staffappraisal_main_meeting
        ON dbo.profiles.profile_id = dbo.staffappraisal_main_meeting.sa_mainappraisal_teacherid
            And dbo.staffappraisal_main_meeting.samm_year = @ActiveYear
    LEFT JOIN
    dbo.staffappraisal_mainapprs_followup
        ON dbo.profiles.profile_id = dbo.staffappraisal_mainapprs_followup.sa_ma_fup_teachers_id
            And dbo.staffappraisal_mainapprs_followup.samafu_year = @ActiveYear
WHERE
    lesson_obsv_by_id = VarLesson
    OR sa_hd_hod_id = VarHod
    OR sa_mainappraisal_appraiser_id = VarMainApp
    OR sa_ma_fup_appraiser_id = VarMainFollow
    OR self_assess_hod_id = VarSelf
    AND lo_year = varyear1
    AND sa_year = varyear2
    AND sahd_year = varyear3
    AND samm_year = varyear4
    AND samafu_year = varyear5

推荐阅读