sql - 如何使用 INNER JOIN 进行 GROUP BY

Question

我对 GROUP BY 和 INNER JOIN 有一个小问题。我尝试按用户在事件组中花费数小时，以查看 whitch 用户的效率更高。

在这一刻，我只得到错误

'列 'XXX' 在选择列表中无效，因为它不包含在聚合函数或 GROUP BY 子句中'

我知道 SELECT 语句中的所有字段在 GROUP BY 中都是必需的，但是如果我为所有这些字段分组，我不会收到我想要的。

我认为我的问题是因为我没有正确理解 GROUP BY 和 INNER JOIN，所以我试图学习很多网站，但至少在这一刻我看不到我的错误。

这是我的代码：

SELECT
  id_incident_project AS ID,
  i.title AS title,
  companyname as social_name,
  username as tech_name,
  ia.DESCRIPTION_TEXT as description,
  CONVERT(varchar, ia.ACTIONDATE, 101) as action_date,
  CAST(TIME as INT) as acting_time,
  CAST(actions_time as INT) AS total_time
FROM
  incident i
  INNER JOIN incident_0001 i1 ON i1.ID_INCIDENT = i.ID_INCIDENT
  INNER JOIN incident_action ia ON ia.ID_INCIDENT = i.ID_INCIDENT
  INNER JOIN agent a ON a.ID_AGENT = ia.ID_AGENT
  INNER JOIN username u on u.ID_USERNAME = a.ID_USERNAME
WHERE
  ia.ACTIONDATE BETWEEN '## START DATE (YYYYMMDD)##' AND '## END DATE (YYYYMMDD)##'
GROUP BY username

标签： sqlsql-server

Answer 1

您未分组的子句中的所有列SELECT都必须通过聚合函数。为了得到一些东西，你可以通过它们MAX：

SELECT
    MAX(id_incident_project) AS ID,
    MAX(i.title) AS title,
    MAX(companyname) as social_name,
    username as tech_name,
    MAX(ia.DESCRIPTION_TEXT) as description,
    MAX(CONVERT(varchar, ia.ACTIONDATE, 101)) as action_date,
    MAX(CAST(TIME as INT)) as acting_time,
    MAX(CAST(actions_time as INT)) AS total_time
FROM
    incident i
    INNER JOIN incident_0001 i1 ON i1.ID_INCIDENT = i.ID_INCIDENT
    INNER JOIN incident_action ia ON ia.ID_INCIDENT = i.ID_INCIDENT
    INNER JOIN agent a ON a.ID_AGENT = ia.ID_AGENT
    INNER JOIN username u on u.ID_USERNAME = a.ID_USERNAME
WHERE
    ia.ACTIONDATE BETWEEN '## START DATE (YYYYMMDD)##' AND '## END DATE (YYYYMMDD)##'
GROUP BY username

username但是，当所有连接完成后有多个相同的行时，这种方法将有可能输出来自不同源行的值。例如，如果用户名“JohnSmith”有（比如说）两次事件，一次发生在 2019-01-01，标题为“Zombie Sighting”，第二次发生在 2019-03-31，标题为“Aitch Dropping”，那么“最大”标题将是“僵尸瞄准”，“最大”日期为 2019 年 3 月 31 日，因此针对不同的事件。

为避免这种情况，您可以将 替换GROUP BY为分区，对事件进行排序，并以一致的方式username挑选一个：username

SELECT
    ID, title, social_name, tech_name, description,
    action_date, acting_time, total_time
FROM (
    SELECT
        id_incident_project) AS ID,
        i.title AS title,
        companyname as social_name,
        username as tech_name,
        ia.DESCRIPTION_TEXT) as description,
        CONVERT(varchar, ia.ACTIONDATE, 101) as action_date,
        CAST(TIME as INT) as acting_time,
        CAST(actions_time as INT) AS total_time,
        ROW_NUMBER () OVER (
            PARTITION BY username
            ORDER BY
                ia.ACTIONDATE DESC,   -- pick out latest
                i1.ID_INCIDENT DESC   -- tie breaker
        ) AS OrderNum
    FROM
        incident i
        INNER JOIN incident_0001 i1 ON i1.ID_INCIDENT = i.ID_INCIDENT
        INNER JOIN incident_action ia ON ia.ID_INCIDENT = i.ID_INCIDENT
        INNER JOIN agent a ON a.ID_AGENT = ia.ID_AGENT
        INNER JOIN username u on u.ID_USERNAME = a.ID_USERNAME
    WHERE
        ia.ACTIONDATE BETWEEN '## START DATE (YYYYMMDD)##' AND '## END DATE (YYYYMMDD)##'
) t
WHERE t.OrderNum = 1