如何通过其他具有速率min的id来获取id组 [英] how to get id group by other id having rate min

问题描述

我有以下结果

 compID OrderId rate 
 __________________________________________ 
 
 15 123 1000 
 12 123 2344 
 13 123 2456 
 14 123 2666 
 
 119 234 3245 
 120 234 3678 
 118 234 4566 
 23 234 6789 
 
 22 345 900 
 27 345 980 
 26 345 1567 
 25 345 1600 

现在我需要通过订单ID编译最低费率组



例如。



订单ID 123公司ID是15

订单ID 234公司ID是119

订单ID 345公司ID是22

解决方案

我认为这对你有用。

 选择 orderid，compid 来自 TableName 
 其中 rat 中的（选择 MIN（费率）来自 TableName  group   by  OrderId）

< blockquote>我已经为你测试了...

  SELECT  compID ，OrderId，rate 
  FROM （
  SELECT  *，ROW_NUMBER（） OVER （ PARTITION   BY  OrderId  ORDER   BY  rate） AS  rn 
  FROM  TestTable 
） AS  T1 
  WHERE  rn =  1  

如果y，请标记为已回答你没问题答案。

 选择 compID，OrderId，MIN（rate）< span class =code-keyword> from  TableName 
 
  GROUP   BY  compID，OrderId 

I have following result

compID OrderId  rate
__________________________________________

  15	123      1000
  12	123      2344
  13	123      2456
  14	123      2666

  119	234      3245
  120	234      3678
  118	234      4566
  23	234      6789

  22	345      900
  27	345      980
  26	345      1567
  25	345      1600

now I need compid having minimum rate group by order id

for eg.

for order id 123 company id is 15
for order id 234 company id is 119
for order id 345 company id is 22

解决方案

Hi,
I think this will work for you.

select  orderid,compid from TableName
where rate in(select MIN(rate) from TableName group by OrderId)

I have Tested for you ...

SELECT compID, OrderId, rate
FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY OrderId ORDER BY rate) AS rn
    FROM TestTable
) AS T1
WHERE rn = 1

Please mark as answered if you are ok with the answer.

select  compID ,OrderId , MIN(rate) from TableName

GROUP BY compID ,OrderId

这篇关于如何通过其他具有速率min的id来获取id组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！