1. ( ) : f1incode.com

2. , , . 3. : ! 4. : 5. !? ? . 6. ? 7. ? .. 8. , ! 9. : Diagnosis Search Page Patient:Disease:Doctor:SearchHouseDBDoctorDiseaseDr. HouseLumpusIvanov Petr Loading 5 Records / Pages 3UIPatient Loading 123 Petrov IvanDr. HouseLumpusselect * from ( select patient_name, doctor_name, disease_name, row_number() over(order by patient_name) as row_index from list_diagnosis() where doctor_name = 'House ) d where row_index between 1 and 2 select count(*) from list_diagnosis() where doctor_name = 'House' 10. 1) , UIa) ; b) ; c) .UI2) API , . DB 11. ? ? 12. 1) . . 2) , , . 3) . 4) , . 5) . 13. 14. 15. daily standup 16. (A) (B) A except BB except A .(A except B) union (B except A) , B B . . 17. SQL with source as ( select patient_name, desease_name, doctor_name from list_diagnosis_old() ), target as ( select patient_name, desease_name, doctor_name from list_diagnosis_new()) (select * from source except select * from target) union all (select * from target except select * from source) 18. list_diagnosis_new(@hospital_id) , . , select hospital_id from HOSPITAL select hospital_id, doctor_id from HOSPITAL cross join DOCTOR select 1 as doctor_id union all select 3 union all select 4 union all select 6 , . 19. .select p.hospital_id, patient_name, desease_name, doctor_name from , ( select hospital_id from HOSPITAL ) pcross apply list_diagnosis(p.hospital_id) 20. with source as ( select hospital_id, patient_name, desease_name, f.doctor_name from HOSPITAL h cross apply list_diagnosis_old(hospital_id) f ), target as ( select hospital_id, patient_name, desease_name, f.doctor_name from HOSPITAL h cross apply list_diagnosis_new(hospital_id) f ) (select * from source except select * from target) union all (select * from target except select * from source) 21. , . , . 22. 5 !!! 23. . 24. 25. , ! 26. 1) . 2) . 3) ., , . . . 27. with source as ( select hospital_id, patient_name, desease_name, f.doctor_name from (select top 1 hospital_id from HOSPITAL order by hospital_id) cross apply list_diagnosis_old(hospital_id) f ), target as ( select hospital_id, patient_name, desease_name, f.doctor_namefrom (select top 1 hospital_id from HOSPITAL order by hospital_id) cross apply list_diagnosis_new(hospital_id) f ) (select * from source except select * from target) union all (select * from target except select * from source) 28. with source as ( select hospital_id, patient_name, desease_name, f.doctor_namefrom (select top 100 hospital_id from HOSPITAL order by hospital_id cross apply list_diagnosis_old(hospital_id) f ), target as ( select hospital_id, patient_name, desease_name, f.doctor_namefrom (select top 100 hospital_id from HOSPITAL order by hospital_id cross apply list_diagnosis_new(hospital_id) f ) (select * from source except select * from target) union all (select * from target except select * from source) 29. with source as ( select hospital_id, patient_name, desease_name, f.doctor_name from HOSPITAL h cross apply list_diagnosis_old(h.hospital_id) f ), target as ( select hospital_id, patient_name, desease_name, f.doctor_name from HOSPITAL h cross apply list_diagnosis_new(h.hospital_id) f ) (select * from source except select * from target) union all (select * from target except select * from source) 30. , . , ? , . . . , , . 31. . . . 32. 33. : ! , . 34. BUG . Diagnosis Search Page Patient:UIDisease:Doctor:SearchHouseDoctorDiseaseDr. HouseLumpusIvanov Petr Records 5 / Pages 3DBPatient 123 Petrov IvanDr. HouseLumpusselect * from ( select patient_name, doctor_name, disease_name, row_number() over(order by patient_name) as row_index from list_diagnosis(@hospital_id) where doctor_name = 'House ) d where row_index between 1 and 2 select count(*) from list_diagnosis(@hospital_id) where doctor_name = 'House' 35. . , . (A) (B)A except BB except A SQL except , .. . union union all except all, SQL Server-e. 36. except all A A 1, 2, 1, BA except BB except A BA except BB except A2, 1, 1, , . 37. except all select hospital_id, patient_name, desease_name, doctor_name, row_number() over ( partition by hospital_id, patient_name, desease_name, f.doctor_name order by hospital_id, patient_name, desease_name, f.doctor_name ) number from list_diagnosis_old(hospital_id) row_number() partition by order by 38. with source as ( select hospital_id, patient_name, desease_name, doctor_name, row_number() over ( partition by hospital_id, patient_name, desease_name, f.doctor_name order by hospital_id, patient_name, desease_name, f.doctor_name ) number from HOSPITAL h cross apply list_diagnosis_old(hospital_id) ), target as , ( ... ) (select * from source except select * from target) union all (select * from target except select * from source) 39. 40. . 41. : BUG . .. , count(*) , . select * from ( select patient_name, doctor_name, disease_name, row_number() over(order by patient_name) as row_index from list_diagnosis(@hospital_id) where doctor_name = 'House )d where row_index between 1 and 2DBselect count(*) from list_diagnosis(@hospital_id) where doctor_name = 'House'.. , . 42. 43. ! 44. : 45. : 1) , count(*) . 2) .: 1) - SQL Server-? 2) - , - . 46. ! 47. select p.patient_name, desease_name, doctor_name from PATIENT p cross apply ( . select top 1 desease_name, doctor_name from DIAGNOSIS g , - inner join DOCTOR d on g.doctor_id = d.doctor_id , 2 where g.patient_id = p.patient_id order by g.create_time . ) d1) SQL Server . 2) , , , . 3) , .. . 48. select p.patient_name, desease_name, doctor_name from PATIENT p cross apply ( select top 1 desease_name, doctor_name from DIAGNOSIS g inner join DOCTOR d on g.doctor_id = d.doctor_id where g.patient_id = p.patient_id order by g.create_time ) d, order by, : order by g.create_time, g.diagnosis_id order by g.diagnosis_id 49. 50. 51. 1) 1) . . 2) 2) , . : . 3) : a) , , ; b) , ETL, , . ; , , . 52. 1) , except union. 2) except all . 3) . 4) . 5) . 53. 1) 1) , except , union. except all. . 2) 2) . 3) . 3) , . 4) . !!!