很难理解sql查询

sgtfey8w 于 2021-08-13 发布在 Java

关注(0)|答案(1)|浏览(265)

我遇到了这个问题，很难理解它的作用

SELECT DISTINCT
    EMPLOYEE.EMPLOYEE_ID,
    EMPLOYEE.LAST_NAME,
    EMPLOYEE.FIRST_NAME,
    COUNT(*)
FROM EMPLOYEE 
JOIN ENTRY ON EMPLOYEE.EMPLOYEE_ID = ENTRY.EMPLOYEE_ID
JOIN TICKET ON ENTRY.TICKET_ID = TICKET.TICKET_ID 
WHERE ENTRY.ACTIVITY_ID = 'ADVTS' AND EMPLOYEE.DEPARTMENT_ID ='SLS'
GROUP BY EMPLOYEE.EMPLOYEE_ID,  EMPLOYEE.LAST_NAME,
    EMPLOYEE.FIRST_NAME,ENTRY.ENTRY_ID
HAVING COUNT(ENTRY.ENTRY_ID) >= 
(SELECT CAST(1.25 * COUNT(ENTRY.ACTIVITY_ID)/COUNT(DISTINCT EMPLOYEE.EMPLOYEE_ID) AS float) 
FROM 
  EMPLOYEE 
JOIN 
  ENTRY ON EMPLOYEE.EMPLOYEE_ID = ENTRY.EMPLOYEE_ID
WHERE 
  ENTRY.ACTIVITY_ID = 'ADVTS' AND EMPLOYEE.DEPARTMENT_ID = 'SLS')

据我所知，它列出了 EMPLOYEE 是谁干的
ADVTS ACTIVITY 来自
DEPARTMENT SLS 这使得 ENTRY 这至少和平均分录一样多
DEPARTMENT SLS 为了 ADVTS 目的
感谢所有花时间帮忙的人
在注解后编辑成功结果：

SELECT 
    EMPLOYEE.EMPLOYEE_ID,
    EMPLOYEE.LAST_NAME,
    EMPLOYEE.FIRST_NAME
    FROM EMPLOYEE
JOIN 
  ENTRY ON ENTRY.EMPLOYEE_ID = EMPLOYEE.EMPLOYEE_ID
GROUP 
   BY EMPLOYEE.EMPLOYEE_ID, EMPLOYEE.LAST_NAME,
    EMPLOYEE.FIRST_NAME
HAVING 
  COUNT(ENTRY.ENTRY_ID) >= 
(SELECT 
  CAST(1.25 * 
  COUNT(ENTRY.ACTIVITY_ID)/COUNT(DISTINCT EMPLOYEE.EMPLOYEE_ID)AS float) 
FROM 
  EMPLOYEE JOIN ENTRY ON EMPLOYEE.EMPLOYEE_ID = ENTRY.EMPLOYEE_ID
WHERE 
  ENTRY.ACTIVITY_ID = 'ADVTS' AND EMPLOYEE.DEPARTMENT_ID = 'SLS')

输出：

EMPLOYEE_ID| LAST_NAME| FIRST_NAME
7          | Salesman | Efficient

sql sql-server sql-server-2019

来源：https://stackoverflow.com/questions/62113835/having-hard-time-understanding-sql-query

1条答案

按热度按时间

vd8tlhqk1#

假设 TICKET_ID 是独一无二的 Ticket 而且从来都不是 NULL 在 ENTRY ，然后你就可以摆脱它了 JOIN .
然后我假设查询的目的是返回计数大于总体平均值1.25的员工。这需要更多（合理的）假设，但更简单的表述是：

SELECT e.*
FROM (SELECT EM.EMPLOYEE_ID, EM.LAST_NAME, EM.FIRST_NAME, COUNT(*) AS CNT,   
             SUM(COUNT(*)) OVER () * 1.0 / COUNT(*) OVER () as AVG_CNT    
      FROM EMPLOYEE EM JOIN
           ENTRY EM
           ON EM.EMPLOYEE_ID = EN.EMPLOYEE_ID 
      WHERE EN.ACTIVITY_ID = 'ADVTS' AND EM.DEPARTMENT_ID = 'SLS'
      GROUP BY EM.EMPLOYEE_ID, EM.LAST_NAME, EM.FIRST_NAME
     ) e
WHERE cnt >= 1.25 * avg_cnt

赞(0）回复(0）举报 2021-08-13

我来回答

很难理解sql查询

1条答案

相关问题

热门标签

最新问答