当前位置： 文江博客话题详情

SQL如何将列抛出无效

发布于 2025-02-10 21:00:23 字数 10249 浏览 1 评论 0原文

在尝试使用GPSGATE服务器的报告系统时，我尝试了它只是为我提供第一次和最后一天的旅行。

该系统真的太晦涩难懂了，无法为此获得任何帮助，但是我偶然发现了SQL错误，我认为这是不可能的，也许如果我理解我可以修复SQL的原因，所以报告了吗？

问题是我会收到以下错误：

列“ DistancedEtailed.userid”在选择列表中无效，因为它不包含在汇总函数中，或者由子句中的组中包含。

。

问题是：在任何地方都没有的组。

该错误如何发生？在不使用条款的情况下，是否还有其他可以构成分组的事物？

整个SQL非常不可读取，因为报告系统使用了许多子查询。

请不要真正试图理解整个事情的作用，因为它的阅读是可怕的。我只需要在这里发生任何分组的提示：

SELECT
PeriodStart,
PeriodEnd,
ApplicationID,
SamplingStart AS StartTime,
SamplingEnd AS StopTime,
DistanceDetailed.Name
FROM
(
 SELECT
  CAST('2022-06-23T22:00:00' AS DATETIME) AS PeriodStart,
  CAST('2022-06-24T22:00:00' AS DATETIME) AS PeriodEnd,
  32 AS ApplicationID,
  Users.Username,
  Users.Name,
  DATEDIFF(
    SECOND,
    Distance01.SamplingStart,
    Distance01.SamplingEnd
  ) AS Duration,
      Distance01.SamplingStart,
      Distance01.SamplingEnd,
      Distance01.UserID,
      Distance01.StartAddress,
      Distance01.StopAddress,
      Distance01.DistanceGps,
      Distance01.DistanceOdo,
      Distance01.MaxSpeed,
      Distance01.AvgSpeed,
      Distance01.FatPointState,
      Distance01.Fuel
    FROM
    (
      SELECT
       data_time_stamp_start AS 'SamplingStart',
       data_time_stamp_end AS 'SamplingEnd',
       int_01 AS 'UserID',
       txt_01 AS 'StartAddress',
       txt_02 AS 'StopAddress',
       dbl_01 AS 'DistanceGps',
       dbl_02 AS 'DistanceOdo',
       dbl_03 AS 'MaxSpeed',
       dbl_04 AS 'AvgSpeed',
       dbl_05 AS 'Fuel',
       txt_03 AS 'FatPointState'
      FROM
      reportv3_data_store
      LEFT JOIN reportv3_data_store_ext01 ON reportv3_data_store.report_data_store_id = reportv3_data_store_ext01.report_data_store_id
     WHERE
      report_data_provider_id = 1
      AND int_01 IN (258, 259)
      AND application_id = 32
      AND data_time_stamp_start BETWEEN '2022-06-23T22:00:00'
      AND '2022-06-24T22:00:00'
      AND data_time_stamp_end BETWEEN '2022-06-23T22:00:00'
      AND DATEADD(DAY, 1, '2022-06-24T22:00:00')
      AND (
        true_break = 1
        OR (
          true_break = 0
          AND data_time_stamp_start != data_time_stamp_end
        )
      )
  ) AS Distance01
  JOIN (
    SELECT
      DISTINCT u.user_id AS UserID,
      u.username as Username,
      u.name as Name,
      u.description as Description,
      u.Surname as Surname,
      email as Email,
      driver_id as IButton
    FROM
      users u
      JOIN user_groups ug ON u.user_id = ug.user_id
      JOIN groups g ON g.group_id = ug.group_id
      AND g.application_id = 32
  ) AS Users ON Users.UserID = Distance01.UserID
WHERE
  Distance01.UserID IN (
    SELECT
      UserID
    FROM
      (
        SELECT
          tu.user_id AS UserID,
          t.tag_id AS TagID,
          t.tag_name AS Name,
          t.tag_description AS Description
        FROM
          tag t
          JOIN tag_users tu ON tu.tag_id = t.tag_id
        WHERE
          bo_type = 'Franson.Directory.UserTagWriter'
          AND t.application_id = 32
      ) AS TagUsers
    WHERE
      TagUsers.TagID IN (297)
  )
  AND Distance01.SamplingStart >= '2022-06-23T22:00:00'
  AND Distance01.SamplingEnd <= '2022-06-24T22:00:00'
  AND Distance01.FatPointState != 'sleep'
) AS DistanceDetailed
 JOIN (
  SELECT
    MIN(SamplingStart) AS StartOfDay,
    DistanceDetailed.UserID,
    DATEPART(DAY, DATEADD(SECOND, 7200, SamplingStart)) AS DayOfMonth
  FROM
  (
    SELECT
      CAST('2022-06-23T22:00:00' AS DATETIME) AS PeriodStart,
      CAST('2022-06-24T22:00:00' AS DATETIME) AS PeriodEnd,
      32 AS ApplicationID,
      Users.Username,
      Users.Name,
      DATEDIFF(
        SECOND,
        Distance01.SamplingStart,
        Distance01.SamplingEnd
      ) AS Duration,
      Distance01.SamplingStart,
      Distance01.SamplingEnd,
      Distance01.UserID,
      Distance01.StartAddress,
      Distance01.StopAddress,
      Distance01.DistanceGps,
      Distance01.DistanceOdo,
      Distance01.MaxSpeed,
      Distance01.AvgSpeed,
      Distance01.FatPointState,
      Distance01.Fuel
    FROM
      (
        SELECT
          data_time_stamp_start AS 'SamplingStart',
          data_time_stamp_end AS 'SamplingEnd',
          int_01 AS 'UserID',
          txt_01 AS 'StartAddress',
          txt_02 AS 'StopAddress',
          dbl_01 AS 'DistanceGps',
          dbl_02 AS 'DistanceOdo',
          dbl_03 AS 'MaxSpeed',
          dbl_04 AS 'AvgSpeed',
          dbl_05 AS 'Fuel',
          txt_03 AS 'FatPointState'
        FROM
          reportv3_data_store
          LEFT JOIN reportv3_data_store_ext01 ON reportv3_data_store.report_data_store_id = reportv3_data_store_ext01.report_data_store_id
        WHERE
          report_data_provider_id = 1
          AND int_01 IN (
            258,
            259
          )
          AND application_id = 32
          AND data_time_stamp_start BETWEEN '2022-06-23T22:00:00'
          AND '2022-06-24T22:00:00'
          AND data_time_stamp_end BETWEEN '2022-06-23T22:00:00'
          AND DATEADD(DAY, 1, '2022-06-24T22:00:00')
          AND (
            true_break = 1
            OR (
              true_break = 0
              AND data_time_stamp_start != data_time_stamp_end
            )
          )
      ) AS Distance01
      JOIN (
        SELECT
          DISTINCT u.user_id AS UserID,
          u.username as Username,
          u.name as Name,
          u.description as Description,
          u.Surname as Surname,
          email as Email,
          driver_id as IButton
        FROM
          users u
          JOIN user_groups ug ON u.user_id = ug.user_id
          JOIN groups g ON g.group_id = ug.group_id
          AND g.application_id = 32
      ) AS Users ON Users.UserID = Distance01.UserID
    WHERE
      Distance01.UserID IN (
        SELECT
          UserID
        FROM
          (
            SELECT
              tu.user_id AS UserID,
              t.tag_id AS TagID,
              t.tag_name AS Name,
              t.tag_description AS Description
            FROM
              tag t
              JOIN tag_users tu ON tu.tag_id = t.tag_id
            WHERE
              bo_type = 'Franson.Directory.UserTagWriter'
              AND t.application_id = 32
          ) AS TagUsers
        WHERE
          TagUsers.TagID IN (297)
      )
      AND Distance01.SamplingStart >= '2022-06-23T22:00:00'
      AND Distance01.SamplingEnd <= '2022-06-24T22:00:00'
      AND Distance01.FatPointState != 'sleep'
  ) AS DistanceDetailed
) AS StartTag ON StartTag.UserID = DistanceDetailed.UserID
 AND DistanceDetailed.FatPointState = 'run'
 AND DATEPART(DAY, DATEADD(SECOND, 7200, SamplingStart)) = DATEPART(DAY, DATEADD(SECOND, 7200, StartTag.StartOfDay))
JOIN (
 SELECT
  MAX(SamplingStart) AS EndOfDay,
  DistanceDetailed.UserID,
  DATEPART(DAY, DATEADD(SECOND, 7200, SamplingStart)) AS DayOfMonth
 FROM
  (
    SELECT
      CAST('2022-06-23T22:00:00' AS DATETIME) AS PeriodStart,
      CAST('2022-06-24T22:00:00' AS DATETIME) AS PeriodEnd,
      32 AS ApplicationID,
      Users.Username,
      Users.Name,
      DATEDIFF(
        SECOND,
        Distance01.SamplingStart,
        Distance01.SamplingEnd
      ) AS Duration,         
      Distance01.SamplingStart,
      Distance01.SamplingEnd,
      Distance01.UserID,
      Distance01.StartAddress,
      Distance01.StopAddress,
      Distance01.DistanceGps,
      Distance01.DistanceOdo,
      Distance01.MaxSpeed,
      Distance01.AvgSpeed,
      Distance01.FatPointState,
      Distance01.Fuel
    FROM
      (
        SELECT
          data_time_stamp_start AS 'SamplingStart',
          data_time_stamp_end AS 'SamplingEnd',
          int_01 AS 'UserID',
          txt_01 AS 'StartAddress',
          txt_02 AS 'StopAddress',
          dbl_01 AS 'DistanceGps',
          dbl_02 AS 'DistanceOdo',
          dbl_03 AS 'MaxSpeed',
          dbl_04 AS 'AvgSpeed',
          dbl_05 AS 'Fuel',
          txt_03 AS 'FatPointState'
        FROM
          reportv3_data_store
          LEFT JOIN reportv3_data_store_ext01 ON reportv3_data_store.report_data_store_id = reportv3_data_store_ext01.report_data_store_id
        WHERE
          report_data_provider_id = 1
          AND int_01 IN (
            258,
            259
          )
          AND application_id = 32
          AND data_time_stamp_start BETWEEN '2022-06-23T22:00:00'
          AND '2022-06-24T22:00:00'
          AND data_time_stamp_end BETWEEN '2022-06-23T22:00:00'
          AND DATEADD(DAY, 1, '2022-06-24T22:00:00')
          AND (
            true_break = 1
            OR (
              true_break = 0
              AND data_time_stamp_start != data_time_stamp_end
            )
          )
      ) AS Distance01
      JOIN (
        SELECT
          DISTINCT u.user_id AS UserID,
          u.username as Username,
          u.name as Name,
          u.description as Description,
          u.Surname as Surname,
          email as Email,
          driver_id as IButton
        FROM
          users u
          JOIN user_groups ug ON u.user_id = ug.user_id
          JOIN groups g ON g.group_id = ug.group_id
          AND g.application_id = 32
      ) AS Users ON Users.UserID = Distance01.UserID
    WHERE
      Distance01.UserID IN (
        SELECT
          UserID
        FROM
          (
            SELECT
              tu.user_id AS UserID,
              t.tag_id AS TagID,
              t.tag_name AS Name,
              t.tag_description AS Description
            FROM
              tag t
              JOIN tag_users tu ON tu.tag_id = t.tag_id
            WHERE
              bo_type = 'Franson.Directory.UserTagWriter'
              AND t.application_id = 32
          ) AS TagUsers
        WHERE
          TagUsers.TagID IN (297)
      )
      AND Distance01.SamplingStart >= '2022-06-23T22:00:00'
      AND Distance01.SamplingEnd <= '2022-06-24T22:00:00'
      AND Distance01.FatPointState != 'sleep'
  ) AS DistanceDetailed
) AS EndeTag ON EndeTag.UserID = DistanceDetailed.UserID
AND DistanceDetailed.FatPointState = 'run'
AND DATEPART(DAY, DATEADD(SECOND, 7200, SamplingStart)) = DATEPART(DAY, DATEADD(SECOND, 7200, EndeTag.EndOfDay))
ORDER BY
  DATEPART(DAY, DATEADD(SECOND, 7200, SamplingStart)),
  DistanceDetailed.UserID
原文

When trying to use a reporting system of a GpsGate server I tried it to get me only the first and the last trip of a day per user.

The system is too obscure really to get any help for that but I stumbled upon an SQL Error which I thought impossible and maybe if I understand the reason I can fix the SQL so the report?!

The thing is I get the following error:

Column 'DistanceDetailed.UserID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

The thing is: There is no GROUP BY anywhere.

How can this error occur? Are there other things that can constitute grouping without using GROUP BY clause?

The whole SQL is very unreadable as the reporting system uses many sub-queries.

Please do not really try to understand what the whole thing does as it is just horrible to read. I just need a tip where any grouping will occur here:

SELECT
PeriodStart,
PeriodEnd,
ApplicationID,
SamplingStart AS StartTime,
SamplingEnd AS StopTime,
DistanceDetailed.Name
FROM
(
 SELECT
  CAST('2022-06-23T22:00:00' AS DATETIME) AS PeriodStart,
  CAST('2022-06-24T22:00:00' AS DATETIME) AS PeriodEnd,
  32 AS ApplicationID,
  Users.Username,
  Users.Name,
  DATEDIFF(
    SECOND,
    Distance01.SamplingStart,
    Distance01.SamplingEnd
  ) AS Duration,
      Distance01.SamplingStart,
      Distance01.SamplingEnd,
      Distance01.UserID,
      Distance01.StartAddress,
      Distance01.StopAddress,
      Distance01.DistanceGps,
      Distance01.DistanceOdo,
      Distance01.MaxSpeed,
      Distance01.AvgSpeed,
      Distance01.FatPointState,
      Distance01.Fuel
    FROM
    (
      SELECT
       data_time_stamp_start AS 'SamplingStart',
       data_time_stamp_end AS 'SamplingEnd',
       int_01 AS 'UserID',
       txt_01 AS 'StartAddress',
       txt_02 AS 'StopAddress',
       dbl_01 AS 'DistanceGps',
       dbl_02 AS 'DistanceOdo',
       dbl_03 AS 'MaxSpeed',
       dbl_04 AS 'AvgSpeed',
       dbl_05 AS 'Fuel',
       txt_03 AS 'FatPointState'
      FROM
      reportv3_data_store
      LEFT JOIN reportv3_data_store_ext01 ON reportv3_data_store.report_data_store_id = reportv3_data_store_ext01.report_data_store_id
     WHERE
      report_data_provider_id = 1
      AND int_01 IN (258, 259)
      AND application_id = 32
      AND data_time_stamp_start BETWEEN '2022-06-23T22:00:00'
      AND '2022-06-24T22:00:00'
      AND data_time_stamp_end BETWEEN '2022-06-23T22:00:00'
      AND DATEADD(DAY, 1, '2022-06-24T22:00:00')
      AND (
        true_break = 1
        OR (
          true_break = 0
          AND data_time_stamp_start != data_time_stamp_end
        )
      )
  ) AS Distance01
  JOIN (
    SELECT
      DISTINCT u.user_id AS UserID,
      u.username as Username,
      u.name as Name,
      u.description as Description,
      u.Surname as Surname,
      email as Email,
      driver_id as IButton
    FROM
      users u
      JOIN user_groups ug ON u.user_id = ug.user_id
      JOIN groups g ON g.group_id = ug.group_id
      AND g.application_id = 32
  ) AS Users ON Users.UserID = Distance01.UserID
WHERE
  Distance01.UserID IN (
    SELECT
      UserID
    FROM
      (
        SELECT
          tu.user_id AS UserID,
          t.tag_id AS TagID,
          t.tag_name AS Name,
          t.tag_description AS Description
        FROM
          tag t
          JOIN tag_users tu ON tu.tag_id = t.tag_id
        WHERE
          bo_type = 'Franson.Directory.UserTagWriter'
          AND t.application_id = 32
      ) AS TagUsers
    WHERE
      TagUsers.TagID IN (297)
  )
  AND Distance01.SamplingStart >= '2022-06-23T22:00:00'
  AND Distance01.SamplingEnd <= '2022-06-24T22:00:00'
  AND Distance01.FatPointState != 'sleep'
) AS DistanceDetailed
 JOIN (
  SELECT
    MIN(SamplingStart) AS StartOfDay,
    DistanceDetailed.UserID,
    DATEPART(DAY, DATEADD(SECOND, 7200, SamplingStart)) AS DayOfMonth
  FROM
  (
    SELECT
      CAST('2022-06-23T22:00:00' AS DATETIME) AS PeriodStart,
      CAST('2022-06-24T22:00:00' AS DATETIME) AS PeriodEnd,
      32 AS ApplicationID,
      Users.Username,
      Users.Name,
      DATEDIFF(
        SECOND,
        Distance01.SamplingStart,
        Distance01.SamplingEnd
      ) AS Duration,
      Distance01.SamplingStart,
      Distance01.SamplingEnd,
      Distance01.UserID,
      Distance01.StartAddress,
      Distance01.StopAddress,
      Distance01.DistanceGps,
      Distance01.DistanceOdo,
      Distance01.MaxSpeed,
      Distance01.AvgSpeed,
      Distance01.FatPointState,
      Distance01.Fuel
    FROM
      (
        SELECT
          data_time_stamp_start AS 'SamplingStart',
          data_time_stamp_end AS 'SamplingEnd',
          int_01 AS 'UserID',
          txt_01 AS 'StartAddress',
          txt_02 AS 'StopAddress',
          dbl_01 AS 'DistanceGps',
          dbl_02 AS 'DistanceOdo',
          dbl_03 AS 'MaxSpeed',
          dbl_04 AS 'AvgSpeed',
          dbl_05 AS 'Fuel',
          txt_03 AS 'FatPointState'
        FROM
          reportv3_data_store
          LEFT JOIN reportv3_data_store_ext01 ON reportv3_data_store.report_data_store_id = reportv3_data_store_ext01.report_data_store_id
        WHERE
          report_data_provider_id = 1
          AND int_01 IN (
            258,
            259
          )
          AND application_id = 32
          AND data_time_stamp_start BETWEEN '2022-06-23T22:00:00'
          AND '2022-06-24T22:00:00'
          AND data_time_stamp_end BETWEEN '2022-06-23T22:00:00'
          AND DATEADD(DAY, 1, '2022-06-24T22:00:00')
          AND (
            true_break = 1
            OR (
              true_break = 0
              AND data_time_stamp_start != data_time_stamp_end
            )
          )
      ) AS Distance01
      JOIN (
        SELECT
          DISTINCT u.user_id AS UserID,
          u.username as Username,
          u.name as Name,
          u.description as Description,
          u.Surname as Surname,
          email as Email,
          driver_id as IButton
        FROM
          users u
          JOIN user_groups ug ON u.user_id = ug.user_id
          JOIN groups g ON g.group_id = ug.group_id
          AND g.application_id = 32
      ) AS Users ON Users.UserID = Distance01.UserID
    WHERE
      Distance01.UserID IN (
        SELECT
          UserID
        FROM
          (
            SELECT
              tu.user_id AS UserID,
              t.tag_id AS TagID,
              t.tag_name AS Name,
              t.tag_description AS Description
            FROM
              tag t
              JOIN tag_users tu ON tu.tag_id = t.tag_id
            WHERE
              bo_type = 'Franson.Directory.UserTagWriter'
              AND t.application_id = 32
          ) AS TagUsers
        WHERE
          TagUsers.TagID IN (297)
      )
      AND Distance01.SamplingStart >= '2022-06-23T22:00:00'
      AND Distance01.SamplingEnd <= '2022-06-24T22:00:00'
      AND Distance01.FatPointState != 'sleep'
  ) AS DistanceDetailed
) AS StartTag ON StartTag.UserID = DistanceDetailed.UserID
 AND DistanceDetailed.FatPointState = 'run'
 AND DATEPART(DAY, DATEADD(SECOND, 7200, SamplingStart)) = DATEPART(DAY, DATEADD(SECOND, 7200, StartTag.StartOfDay))
JOIN (
 SELECT
  MAX(SamplingStart) AS EndOfDay,
  DistanceDetailed.UserID,
  DATEPART(DAY, DATEADD(SECOND, 7200, SamplingStart)) AS DayOfMonth
 FROM
  (
    SELECT
      CAST('2022-06-23T22:00:00' AS DATETIME) AS PeriodStart,
      CAST('2022-06-24T22:00:00' AS DATETIME) AS PeriodEnd,
      32 AS ApplicationID,
      Users.Username,
      Users.Name,
      DATEDIFF(
        SECOND,
        Distance01.SamplingStart,
        Distance01.SamplingEnd
      ) AS Duration,         
      Distance01.SamplingStart,
      Distance01.SamplingEnd,
      Distance01.UserID,
      Distance01.StartAddress,
      Distance01.StopAddress,
      Distance01.DistanceGps,
      Distance01.DistanceOdo,
      Distance01.MaxSpeed,
      Distance01.AvgSpeed,
      Distance01.FatPointState,
      Distance01.Fuel
    FROM
      (
        SELECT
          data_time_stamp_start AS 'SamplingStart',
          data_time_stamp_end AS 'SamplingEnd',
          int_01 AS 'UserID',
          txt_01 AS 'StartAddress',
          txt_02 AS 'StopAddress',
          dbl_01 AS 'DistanceGps',
          dbl_02 AS 'DistanceOdo',
          dbl_03 AS 'MaxSpeed',
          dbl_04 AS 'AvgSpeed',
          dbl_05 AS 'Fuel',
          txt_03 AS 'FatPointState'
        FROM
          reportv3_data_store
          LEFT JOIN reportv3_data_store_ext01 ON reportv3_data_store.report_data_store_id = reportv3_data_store_ext01.report_data_store_id
        WHERE
          report_data_provider_id = 1
          AND int_01 IN (
            258,
            259
          )
          AND application_id = 32
          AND data_time_stamp_start BETWEEN '2022-06-23T22:00:00'
          AND '2022-06-24T22:00:00'
          AND data_time_stamp_end BETWEEN '2022-06-23T22:00:00'
          AND DATEADD(DAY, 1, '2022-06-24T22:00:00')
          AND (
            true_break = 1
            OR (
              true_break = 0
              AND data_time_stamp_start != data_time_stamp_end
            )
          )
      ) AS Distance01
      JOIN (
        SELECT
          DISTINCT u.user_id AS UserID,
          u.username as Username,
          u.name as Name,
          u.description as Description,
          u.Surname as Surname,
          email as Email,
          driver_id as IButton
        FROM
          users u
          JOIN user_groups ug ON u.user_id = ug.user_id
          JOIN groups g ON g.group_id = ug.group_id
          AND g.application_id = 32
      ) AS Users ON Users.UserID = Distance01.UserID
    WHERE
      Distance01.UserID IN (
        SELECT
          UserID
        FROM
          (
            SELECT
              tu.user_id AS UserID,
              t.tag_id AS TagID,
              t.tag_name AS Name,
              t.tag_description AS Description
            FROM
              tag t
              JOIN tag_users tu ON tu.tag_id = t.tag_id
            WHERE
              bo_type = 'Franson.Directory.UserTagWriter'
              AND t.application_id = 32
          ) AS TagUsers
        WHERE
          TagUsers.TagID IN (297)
      )
      AND Distance01.SamplingStart >= '2022-06-23T22:00:00'
      AND Distance01.SamplingEnd <= '2022-06-24T22:00:00'
      AND Distance01.FatPointState != 'sleep'
  ) AS DistanceDetailed
) AS EndeTag ON EndeTag.UserID = DistanceDetailed.UserID
AND DistanceDetailed.FatPointState = 'run'
AND DATEPART(DAY, DATEADD(SECOND, 7200, SamplingStart)) = DATEPART(DAY, DATEADD(SECOND, 7200, EndeTag.EndOfDay))
ORDER BY
  DATEPART(DAY, DATEADD(SECOND, 7200, SamplingStart)),
  DistanceDetailed.UserID

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。

评论（1）

薄情伤 2025-02-17 21:00:23

由于您具有非窗口的聚合函数，并且在同一范围内具有非聚集列，因此必须按子句具有组。因此，您省略了该组的缺陷，否则您的其他列应该被汇总，以便您不需要按子句按子句。

例如，此查询

select max(OrderDate) OrderDate, SalesOrderID
from Sales.SalesOrderHeader

在同一错误中失败。某些引擎允许它，并将其视为全局聚合物，SQL Server要求您使用窗口汇总：

select max(OrderDate) over () OrderDate, SalesOrderID
from Sales.SalesOrderHeader

尝试使用报告系统

，这似乎是报告系统中的错误。

Because you have non-windowed aggregate functions, and in the same scope have non-aggregated columns you must have a GROUP BY clause. Your omission of the GROUP BY is therefore the flaw, or your other columns should be being aggregated, so that you don't need a GROUP BY clause.

For instance this query

select max(OrderDate) OrderDate, SalesOrderID
from Sales.SalesOrderHeader

fails with the same error. Aparently some engines allow this, and treat it as a global aggregate, which SQL Server requires you to use windowing aggregate for:

select max(OrderDate) over () OrderDate, SalesOrderID
from Sales.SalesOrderHeader

When trying to use a reporting system

So this appears to be a bug in the reporting system.

回复 原文
~没有更多了~

关于作者

-小熊_

暂无简介

文章
评论
27 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

lylex099819

文章 0 评论 0

yg

文章 0 评论 0

mb_PT8LkUS5

文章 0 评论 0

埋情葬爱

文章 0 评论 0

佚名

文章 0 评论 0

奢望

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文