T-SQL:最多两个其他列的SELECT相关列数据 [英] T-SQL: SELECT related column data for the max two other columns

问题描述

我有如下表数据，其中订单类型的报价为1，订单的类型为2.任何给定的po_num可以具有0到许多order_type 1，但只能具有0或1的order_type 2，或者所有上述所有.

I have table data like the following, where order type is 1 for a quote, and 2 for an order. Any given po_num can have 0 to many of order_type 1, but should only have only 0 or 1 of order_type 2, or all of the above.

我需要返回给定po_num的最大order_type的max order_num，其中order_num只是结果中的另一列(但很重要).

I need to return the max order_num of the max order_type of a given po_num, where the order_num is just an additional (but important) column in my result.

表格数据:

order_type  po_num  order_num
1           E0102   1013200
1           E0102   1013162
1           E0104   1012161 
2           E0104   1012150
1           E0104   1011449
2           E0107   1010034
2           E0108   1011994

所需结果:

order_type  po_num  order_num
1           E0102   1013200
2           E0104   1012950
2           E0107   1010034
2           E0108   1011994

我能得到的最接近的是它，它仍然包括order_type为1和订单类型2为order_no的max(order_no).

The closest I can get is this, which still includes the max(order_no) for both order_type of 1, and order_no of order type 2:

order_type  po_num  order_num
1           E0102   1013162
1           E0104   1012161
2           E0104   1012150
2           E0107   1010034
2           E0108   1011994

推荐答案

我认为您想要这样做:

select order_type
     , po_num
     , max(order_num)
  from orders o1
 where order_type = (
         select max(order_type)
           from orders o2
          WHERE o2.po_num = o1.po_num
      ) 
 group by po_num,order_type

在group by中包含order_type是一个 artifact ，这是必需的，因为表格的设计方式如此.

The inclusion of order_type in the group by is an artifact, it is required because of how the table is designed.

FWIW，报价和订单应分为两个表.当您遇到像这样的怪异SQL时，会遇到困难或有条件的唯一约束，这就是表设计问题.

FWIW, the quotes and orders should be split out into two tables. When you get weird SQL like this an difficult or conditional unique constraints it is a table design issue.

这篇关于T-SQL:最多两个其他列的SELECT相关列数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！