SQL MIN()返回多个值? [英] SQL MIN() returns multiple values?

问题描述

我正在使用SQL Server 2005，并通过Web Developer 2010查询，并且min函数似乎返回多个值(对于返回的每个ID，请参见下文).理想情况下，我希望它为每个ID返回一个.

I am using SQL server 2005, querying with Web Developer 2010, and the min function appears to be returning more than one value (for each ID returned, see below). Ideally I would like it to just return the one for each ID.

SELECT     Production.WorksOrderOperations.WorksOrderNumber,
           MIN(Production.WorksOrderOperations.OperationNumber) AS Expr1, 
           Production.Resources.ResourceCode,
           Production.Resources.ResourceDescription,
           Production.WorksOrderExcel_ExcelExport_View.PartNumber,
           Production.WorksOrderOperations.PlannedQuantity,
           Production.WorksOrderOperations.PlannedSetTime, 
           Production.WorksOrderOperations.PlannedRunTime
FROM       Production.WorksOrderOperations
INNER JOIN Production.Resources
           ON Production.WorksOrderOperations.ResourceID = Production.Resources.ResourceID
INNER JOIN Production.WorksOrderExcel_ExcelExport_View
           ON Production.WorksOrderOperations.WorksOrderNumber = Production.WorksOrderExcel_ExcelExport_View.WorksOrderNumber
WHERE      Production.WorksOrderOperations.WorksOrderNumber IN
             ( SELECT   WorksOrderNumber
               FROM     Production.WorksOrderExcel_ExcelExport_View AS WorksOrderExcel_ExcelExport_View_1
               WHERE    (WorksOrderSuffixStatus = 'Proposed'))
           AND Production.Resources.ResourceCode IN ('1303', '1604')
GROUP BY   Production.WorksOrderOperations.WorksOrderNumber,
           Production.Resources.ResourceCode,
           Production.Resources.ResourceDescription,
           Production.WorksOrderExcel_ExcelExport_View.PartNumber,
           Production.WorksOrderOperations.PlannedQuantity,
           Production.WorksOrderOperations.PlannedSetTime,
           Production.WorksOrderOperations.PlannedRunTime

如果可以解决的话，我要从多个表中选择某些列，这些表中的WorksOrderNumber也包含在子查询中，以及许多其他条件.

If you can get your head around it, I am selecting certain columns from multiple tables where the WorksOrderNumber is also contained within a subquery, and numerous other conditions.

结果集看起来像这样，模糊了不相关的数据.

Result set looks a little like this, have blurred out irrelevant data.

http://i.stack.imgur.com/5UFIp.png (不会让我嵌入图片).

http://i.stack.imgur.com/5UFIp.png (Wouldn't let me embed image).

突出显示的行不应该存在，我无法明确地将它们过滤掉，因为此结果集每天都会更新，并且可能会在不同的记录中发生.

The highlighted rows are NOT supposed to be there, I cannot explicitly filter them out, as this result set will be updated daily and it is likely to happen with a different record.

我尝试过转换并将OperationNumber转换为许多其他数据类型，varchar类型返回'100'而不是'30'.还尝试过搜索搜索引擎，似乎没有人遇到同样的问题.

I have tried casting and converting the OperationNumber to numerous other data types, varchar type returns '100' instead of the '30'. Also tried searching search engines, no one seems to have the same problem.

我没有构造表(它们已经过严格的标准化)，因此无法重构它们.

I did not structure the tables (they're horribly normalised), and it is not possible to restructure them.

感谢任何想法，非常感谢.

Any ideas appreciated, many thanks.

推荐答案

MIN函数返回组中的最小值. 如果您想要每个ID的最小值，则只需要对ID进行分组即可. 我假设通过"ID"表示您是指Production.WorksOrderOperations.WorksOrderNumber.

The MIN function returns the minimum within the group. If you want the minimum for each ID you need to get group on just ID. I assume that by "ID" you are referring to Production.WorksOrderOperations.WorksOrderNumber.

您可以在SQL中将其添加为表":

You can add this as a "table" in your SQL:

(SELECT Production.WorksOrderOperations.WorksOrderNumber,
       MIN(Production.WorksOrderOperations.OperationNumber) 
  FROM Production.WorksOrderOperations
 GROUP BY  Production.WorksOrderOperations.WorksOrderNumber)

这篇关于SQL MIN()返回多个值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！