doudouchan5830 2015-05-06 11:48
浏览 171
已采纳

每组的最小值和最大值

I have two table

location:

  • location_id
  • address

itransfile:

  • id
  • transactionNumber
  • location_id
  • itemName
  • quantity

I want to get maximum and minimum sold items by locations.

HighestItemName     HighQauntity    LowestItemName      LowQuantity     LocationName

Chicken Burger      50              Tako                5               Gulshan
Chicken Burger      100             Tikka               10              Nipa
Pasta               150             Cheese Burger       12              Liyari
Pizza               200             Chicken Burger      3               F.B.Area

The query I've done so far:

SELECT t.itemName as HighestItemName, sum(t.quantity) as HighQuantity, l.address LocationName
    from itransfile as t join locations as l
    on t.location_id = l.location_id 
    where t.location_id IN(1,2,3,4)
    group by t.location_id

I don't know how will get max and min items from every group.

Sample Data:

ID  TransNumber ItemName        Quantity location_id
1   1234        Chicken Burger  3           1
2   1234        Cheese Burger   1           1
3   1235        Sandwich        4           2
4   1332        Salad           1           4
5   14537       Tikka           1           3   
6   1236        Roll            3           2
7   1333        Biryani         2           4


location_id     address
1               Gulshan
2               Nipa
3               Liyari
4               F.B.Area
  • 写回答

1条回答 默认 最新

  • duanpi7578 2015-05-06 12:34
    关注

    This is what you may be looking for if you need it in one query (SQLFiddle):

    select
      l.address,
      imax.itemName max_item, max_min.max_q,
      imin.itemName min_item, max_min.min_q
    FROM
      (select
        i.location_id, MAX(i.quantity) max_q, MIN(i.quantity) min_q
      FROM
        itransfile i
      GROUP BY
        i.location_id) as max_min
      LEFT JOIN itransfile imax ON (max_min.max_q = imax.quantity)
      LEFT JOIN itransfile imin ON (max_min.min_q = imin.quantity)
      LEFT JOIN location l ON (max_min.location_id = l.location_id)
    GROUP BY
      l.location_id
    

    It looks for min/max values and then looks up the item name and location address. The GROUP_CONCAT makes sure that when there are more items with the same min/max quantity, you get all of them.

    Alternatively you can get rid of the GROUP BY and GROUP_CONCAT and get all the items in rows if you need to further process them.

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥15 求差集那个函数有问题,有无佬可以解决
  • ¥15 【提问】基于Invest的水源涵养
  • ¥20 微信网友居然可以通过vx号找到我绑的手机号
  • ¥15 寻一个支付宝扫码远程授权登录的软件助手app
  • ¥15 解riccati方程组
  • ¥15 display:none;样式在嵌套结构中的已设置了display样式的元素上不起作用?
  • ¥15 使用rabbitMQ 消息队列作为url源进行多线程爬取时,总有几个url没有处理的问题。
  • ¥15 Ubuntu在安装序列比对软件STAR时出现报错如何解决
  • ¥50 树莓派安卓APK系统签名
  • ¥65 汇编语言除法溢出问题