代码之家 › 专栏 › 技术社区 › Pat

联接表中最小值的显示名称

ms-access sql

1

Pat · 技术社区 · 7 年前

      Projects                    Comparisons                Assets
ProjectID ProjectName    CompareID ProjectID AssetID    AssetID AssetName
    1          A           101        1       201         201      ZZZ
    2          B           102        1       202         202      YYY
    3          C           103        2       203         203      XXX
                           104        3       204         204      WWW
                           105        1       205         205      VVV
                           106        2       206         206      UUU
                           107        1       207         207      TTT

(比较和资产实际上有额外的信息,这使得它们必须是单独的表,即使这个示例使它看起来像我可以把所有的表都放在一个表中。)

ProjectName:  A    (Lowest CompareID is 101)    AssetName:  ZZZ
ProjectName:  B    (Lowest CompareID is 103)    AssetName:  XXX
ProjectName:  C    (Lowest CompareID is 104)    AssetName:  WWW

在我的表单中,我已经将控制源列为Projects.ProjectID,行源列为ProjectID

SELECT Projects.ProjectID, Min(Comparisons.CompareID)
FROM Comparisons INNER JOIN Projects ON Comparisons.ProjectID = Projects.ProjectID
GROUP BY Projects.ProjectID;

我不知道如何合并资产。如果我将Assets.AssetName添加到SELECT行,那么我需要在命令的其他地方包含此字段。如果它是groupby的一部分,那么每个项目都有多个条目,这是我不想要的。所以现在是一系列的尝试和错误都没有成功。请帮助,聪明人!!

3 回复 | 直到 7 年前

1

0

D-Shih 7 年前

您可以尝试编写一个子查询get Min(CompareID) 和 MIN(AssetID) 从 Comparisons 那就坐桌子吧 JOIN .

SELECT Projects.ProjectName, 
       CompareIDMin,
       Assets.AssetName
FROM (
   select ProjectID,Min(CompareID) AS CompareIDMin,MIN(AssetID) AS AssetIDMin
   FROM Comparisons
   GROUP BY ProjectID
) as c
INNER JOIN Projects ON c.ProjectID = Projects.ProjectID
INNER JOIN Assets ON Assets.AssetID = c.AssetIDMin

SQLFIDDLE

2

0

Ajinkya Chalke 7 年前

你试过这个吗?除了加入,您还可以将其用作WHERE子句中的子查询,这可能是更好的选择。

SELECT C.ProjectID, C.CompareID, C.AssetID
FROM Comparisons C 
JOIN (SELECT ProjectID, Min(CompareID) as CompareID FROM Comparisons GROUP BY ProjectID) S
ON C.ProjectID = S.ProjectID and C.CompareID = S.CompareID

3

0

Pat 7 年前

在一根弦上用Min算出了,我不知道这是可能的。

SELECT Projects.ProjectID, Min(Comparisons.CompareID), Min(Assets.AssetName)
FROM Assets INNER JOIN (Comparisons INNER JOIN Projects ON Comparisons.ProjectID = Projects.ProjectID) ON Assets.AssetID = Comparisons.AssetID
GROUP BY Projects.ProjectID