LC Python 刷题笔记 有志者事竟成

1. /*
   
2. 
   
3. 2019-6-03
   
4. 
   
5. Table:
   
6. 
   
7. employee(employee_id, employee_name, Manager_id) // Department(employee_id,department_id) //
   
8. Commit(id,employee_id,date,timestamp) //
   
9. 
   
10. 要求输出：最近30天内，对于每个manager，有多少个commit是向他汇报的。
    
11. 
    
12. */
    
13. 
    
14. WITH CTE AS
    
15. (SELECT DISTINCT
    
16.   employee_id, employee_name
    
17. FROM employee
    
18. WHERE Manager_id IS NULL
    
19. )
    
20. 
    
21. SELECT
    
22. CTE.employee_name AS Manager,
    
23. ISNULL(count(id),0) AS Commit
    
24. FROM CTE
    
25. LEFT JOIN employee ON CTE.employee_id = employee.Manager_id
    
26. LEFT JOIN Commit ON employee.employee_id = Commit.employee_id
    
27. WHERE date BETWEEN DATEADD(DAY,-30,GETDATE()) AND GETDATE()
    
28. GROUP BY CTE.employee_name
    
29. ORDER BY 2 DESC

复制代码