groupby鉴于sql返回聚合错误 [英] groupby in view of sql returns aggregate error

问题描述

I am trying to create a view with this query as you can see here:

SELECT        dbo.Lines.LineNumber, dbo.Lines.DocumentNumber, dbo.Joints.JointNumber, dbo.Joints.JointSize, dbo.Joints.ShopField, dbo.Joints.WPS, dbo.WeldDetails.StateStep2 AS WeldState, dbo.Welds.WeldNumber, 
                         dbo.FitUps.FitUpNumber, MAX(dbo.WeldDetails.Id) AS WeldDetailId, MAX(dbo.FitUpDetails.Id) AS FitupDetailId, dbo.Joints.Id AS JointId, dbo.Ends.Name, dbo.Joints.THK, dbo.FitUpDetails.StateStep2 AS FitupState,
                          dbo.Joints.Revision, dbo.Joints.Note
FROM            dbo.FitUps INNER JOIN
                         dbo.Welds INNER JOIN
                         dbo.Joints INNER JOIN
                         dbo.WeldDetails ON dbo.Joints.Id = dbo.WeldDetails.JointId INNER JOIN
                         dbo.FitUpDetails ON dbo.Joints.Id = dbo.FitUpDetails.JointId ON dbo.Welds.Id = dbo.WeldDetails.WeldId ON dbo.FitUps.Id = dbo.FitUpDetails.FitUpId INNER JOIN
                         dbo.Lines ON dbo.Joints.LineId = dbo.Lines.Id INNER JOIN
                         dbo.Ends ON dbo.Joints.EndId = dbo.Ends.Id
GROUP BY dbo.Joints.Id

但是，当我想保存视图我得到这个错误：

But when i want to save the view i get this error :

以下是我的一部分数据：

Here is a part of my data :

每个联合ID都可以有多个 fitupdetailid 和 welddetailid 在我看来，我只想显示 fitupdetailid 和 welddetailid 的最大值我的联合。

Every joint id can have multi fitupdetailid and welddetailid in my view i want just show the maximum value of fitupdetailid and welddetailid of my joint.

推荐答案

我用mor重写了您的查询e可读连接结构，而不是你的GUI吐出来的。这应该为你运行并修复你的错误。结果是否是你想要的，取决于你的数据。您可能还想重新排序分组，以分层次将其分组。但所有这些列都需要以一种或另一种形式分组。

I rewrote your query with a more readable join structure than what your GUI spit out. This should run for you and fix your error. Whether the results are what you want or not depends on your data. You may also want to re-order the grouping to group how you want, hierarchically. But all of those columns will need to be in the grouping in one form or another.

SELECT
    dbo.Lines.LineNumber, 
    dbo.Lines.DocumentNumber, 
    dbo.Joints.JointNumber, 
    dbo.Joints.JointSize, 
    dbo.Joints.ShopField, 
    dbo.Joints.WPS, 
    dbo.WeldDetails.StateStep2 AS WeldState, 
    dbo.Welds.WeldNumber, 
    dbo.FitUps.FitUpNumber, 
    MAX(dbo.WeldDetails.Id) AS WeldDetailId, 
    MAX(dbo.FitUpDetails.Id) AS FitupDetailId, 
    dbo.Joints.Id AS JointId, 
    dbo.Ends.Name, 
    dbo.Joints.THK, 
    dbo.FitUpDetails.StateStep2 AS FitupState,
    dbo.Joints.Revision, 
    dbo.Joints.Note
FROM
    dbo.FitUps 
    INNER JOIN dbo.FitUpDetails ON dbo.FitUps.Id = dbo.FitUpDetails.FitUpId 
    INNER JOIN dbo.Joints ON dbo.Joints.Id = dbo.FitUpDetails.JointId 
    INNER JOIN dbo.WeldDetails ON dbo.Joints.Id = dbo.WeldDetails.JointId
    INNER JOIN dbo.Welds ON dbo.Welds.Id = dbo.WeldDetails.WeldId
    INNER JOIN dbo.Lines ON dbo.Joints.LineId = dbo.Lines.Id 
    INNER JOIN dbo.Ends ON dbo.Joints.EndId = dbo.Ends.Id
GROUP BY
    dbo.Lines.LineNumber, 
    dbo.Lines.DocumentNumber, 
    dbo.Joints.JointNumber, 
    dbo.Joints.JointSize, 
    dbo.Joints.ShopField, 
    dbo.Joints.WPS, 
    dbo.WeldDetails.StateStep2,
    dbo.Welds.WeldNumber, 
    dbo.FitUps.FitUpNumber, 
    dbo.Joints.Id,
    dbo.Ends.Name, 
    dbo.Joints.THK, 
    dbo.FitUpDetails.StateStep2,
    dbo.Joints.Revision, 
    dbo.Joints.Note

这篇关于groupby鉴于sql返回聚合错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！