22,207
社区成员
发帖
与我相关
我的任务
分享
SELECT dbo.App_Appointment.appointment_id ,
dbo.App_Appointment.appointment_state ,
CASE WHEN dbo.App_Appointment.appointment_state = 'N' THEN '未到'
ELSE '已到'
END AS appointment_state_text ,
CASE WHEN dbo.App_Appointment.appointment_visit = '' THEN 'N'
ELSE 'Y'
END AS appointment_visit_state ,
CASE WHEN dbo.App_Appointment.appointment_visit = '' THEN '未回访'
ELSE '已回访'
END AS appointment_visit_state_text ,
CASE WHEN dbo.App_Appointment.appointment_visit_arrive IS NULL
THEN 'N'
ELSE dbo.App_Appointment.appointment_visit_arrive
END AS appointment_visit_arrive ,
dbo.App_Appointment.appointment_user_name ,
dbo.App_Appointment.appointment_user_sex ,
dbo.App_Appointment.appointment_user_age ,
dbo.App_Appointment.appointment_user_phone ,
dbo.App_Appointment.appointment_user_qq ,
dbo.App_Appointment.appointment_specialist_number ,
dbo.App_Appointment.appointment_is_consume ,
CASE WHEN dbo.App_Appointment.appointment_is_consume = 'N' THEN '未消费'
ELSE '已消费'
END AS appointment_is_consume_text ,
dbo.App_Appointment.appointment_keywords ,
dbo.App_Appointment.appointment_advisory ,
dbo.App_Appointment.appointment_note ,
dbo.App_Appointment.appointment_ailment ,
dbo.App_Appointment.appointment_remark ,
dbo.App_Appointment.appointment_visit ,
dbo.App_Appointment.appointment_time_arrive ,
dbo.App_Appointment.appointment_time_create ,
dbo.App_Appointment.appointment_source_id ,
dbo.App_Appointment.appointment_area_id ,
dbo.App_Appointment.user_id ,
dbo.App_Source.source_name ,
dbo.App_Area.area_name ,
dbo.vw_Acc_User.user_real_name ,
dbo.vw_Acc_User.user_department_id ,
dbo.vw_Acc_User.department_name ,
dbo.App_Appointment.appointment_abteilung_id ,
dbo.Abt_Abteilung.abteilung_name ,
dbo.vw_Acc_User.user_hospital_id ,
dbo.vw_Acc_User.hospital_name ,
dbo.App_Appointment.appointment_to_qq ,
CASE WHEN dbo.App_Appointment.appointment_to_qq = 'N' THEN '未转'
ELSE '已转'
END AS appointment_to_qq_text ,
dbo.App_Appointment.appointment_to_phone ,
CASE WHEN dbo.App_Appointment.appointment_to_phone = 'N' THEN '未转'
ELSE '已转'
END AS appointment_to_phone_text ,
dbo.App_Appointment.appointment_doctor_id ,
dbo.Acc_User.user_real_name AS doctor_name ,
dbo.App_Appointment.appointment_evaluation ,
dbo.App_Appointment.appointment_clinic_number
FROM dbo.App_Appointment --该表字段提取很多,执行计划中 键查找的部分,也表明了这点,lz也可以考虑使用覆盖索引,用空间换时间
LEFT OUTER JOIN dbo.Acc_User ON dbo.App_Appointment.appointment_doctor_id = dbo.Acc_User.USER_ID --Acc_User 索引扫描,占了13% ,但上面输出只有一列user_real_name,lz可以建里一个覆盖索引 USER_ID和user_real_name
LEFT OUTER JOIN dbo.Abt_Abteilung ON dbo.App_Appointment.appointment_abteilung_id = dbo.Abt_Abteilung.abteilung_id
LEFT OUTER JOIN dbo.App_Source ON dbo.App_Appointment.appointment_source_id = dbo.App_Source.source_id
LEFT OUTER JOIN dbo.App_Area ON dbo.App_Appointment.appointment_area_id = dbo.App_Area.area_id
LEFT OUTER JOIN dbo.vw_Acc_User ON dbo.App_Appointment.user_id = dbo.vw_Acc_User.USER_ID --如果是视图的话,看看能否做成索引视图,如果不是注意user_id列的索引状况。
create index ix_test on vw_App_Appointment (user_department_id, appointment_id)