我只需要选择我的查询的最小值

Question

我在SQL Server Management Studio中创建了一个视图，该视图引入了某些数据，我只需要选择序列最小的行。例如，在屏幕截图中看到作业编号"50773-4"，我只需要看到SEQ编号为2的行。我尝试按最小值分组，但无济于事。任何帮助都将不胜感激。

SELECT
    TOP (100) PERCENT dbo.Job_Operation.Job,
    MIN(dbo.Job_Operation.Sequence) AS SEQ,
    dbo.Job_Operation.Work_Center,
    dbo.Work_Center.Department
FROM
    dbo.Job_Operation
    INNER JOIN dbo.Job ON dbo.Job_Operation.Job = dbo.Job.Job
    INNER JOIN dbo.User_Values ON dbo.Job.User_Values = dbo.User_Values.User_Values
    INNER JOIN dbo.Work_Center ON dbo.Job_Operation.Work_Center = dbo.Work_Center.Work_Center
GROUP BY
    dbo.Job_Operation.Job,
    dbo.User_Values.Numeric2,
    dbo.Work_Center.UVText4,
    dbo.Job.Status,
    dbo.Job_Operation.Status,
    dbo.User_Values.Decimal1,
    dbo.Job_Operation.Work_Center,
    dbo.Work_Center.Department
HAVING
    (dbo.Work_Center.UVText4 = 'Machining')
ORDER BY
    dbo.User_Values.Decimal1 DESC,
    SEQ

在此处输入图像描述

​

Answer 1

这里有一种方法：

SELECT
    TOP (100) PERCENT Job,
    Sequence AS SEQ,
    Work_Center,
    Department
FROM
( select  dbo.Job_Operation.Job,
    dbo.Job_Operation.Sequence,
    dbo.Job_Operation.Work_Center,
    dbo.Work_Center.Department,
    dbo.User_Values.Decimal1 , 
    ROW_NUMBER() over (partition by dbo.Job_Operation.Job,
                                    dbo.User_Values.Numeric2,
                                    dbo.Work_Center.UVText4,
                                    dbo.Job.Status,
                                    dbo.Job_Operation.Status,
                                    dbo.User_Values.Decimal1,
                                    dbo.Job_Operation.Work_Center,
                                    dbo.Work_Center.Department 
                                 Order by   dbo.Job_Operation.Sequence asc) rn
FROM
    dbo.Job_Operation
    INNER JOIN dbo.Job ON dbo.Job_Operation.Job = dbo.Job.Job
    INNER JOIN dbo.User_Values ON dbo.Job.User_Values = dbo.User_Values.User_Values
    INNER JOIN dbo.Work_Center ON dbo.Job_Operation.Work_Center = dbo.Work_Center.Work_Center
) tt
WHERE rn = 1
and UVText4 = 'Machining'

Answer 2

您可以执行以下操作：

with
q as (
  SELECT
    dbo.Job_Operation.Job,
    MIN(dbo.Job_Operation.Sequence) AS SEQ,
    dbo.Job_Operation.Work_Center,
    dbo.Work_Center.Department,
    dbo.User_Values.Decimal1
  FROM
    dbo.Job_Operation
    INNER JOIN dbo.Job ON dbo.Job_Operation.Job = dbo.Job.Job
    INNER JOIN dbo.User_Values 
      ON dbo.Job.User_Values = dbo.User_Values.User_Values
    INNER JOIN dbo.Work_Center 
      ON dbo.Job_Operation.Work_Center = dbo.Work_Center.Work_Center
  GROUP BY
    dbo.Job_Operation.Job,
    dbo.User_Values.Numeric2,
    dbo.Work_Center.UVText4,
    dbo.Job.Status,
    dbo.Job_Operation.Status,
    dbo.User_Values.Decimal1,
    dbo.Job_Operation.Work_Center,
    dbo.Work_Center.Department
  HAVING
    (dbo.Work_Center.UVText4 = 'Machining')
),
r as (
  select *,
    row_number() over(partition by job order by seq) as rn
  from q
)
select job, seq, work_center, department
from r 
where rn = 1
order by Decimal1 DESC

Answer 3

我会尝试使用RANK()窗口函数。也许：

SELECT column1, 
          column2,
          rank() OVER (PARTITION BY job ORDER BY seq) AS seq_by_job

然后将其用作嵌套语句，并仅根据最小排名进行过滤(即WHERE nested_statement.seq_by_job = 1)