首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >排序依据和分组依据

问排序依据和分组依据
EN

Stack Overflow用户
提问于 2013-06-13 17:33:45
回答 1查看 72关注 0票数 1

我正在尝试显示我正在建设的商店的送货费用,数据库中有三个表1的服务,即皇家邮政,承运商...,一个为乐队。英国、欧洲、Worldwide1等。一个是费用(数量=重量)

我有一个包含三个表的数据库,当连接到下面的表中时

代码语言:javascript
复制
+------------------+-----+-----------+-------+---------+---------------+----------+-------+-------------+
|       name       | qty | serviceID | basis | bandID | initial_charge | chargeID | price | total_price |
+------------------+-----+-----------+-------+---------+---------------+----------+-------+-------------+
| Collect in store |   0 |         3 |       |       1 | 3             | 0.00     | 2     | 0.00        |
| Royal mail       |   0 |         1 |     2 |       4 | 2.00          | 3        | 0.00  | 2.00        |
| Royal mail       |   1 |         1 |     2 |       4 | 2.00          | 4        | 1.00  | 3.00        |
| APC              |   0 |         2 |     1 |       1 | 0.00          | 6        | 5.95  | 5.95        |
+------------------+-----+-----------+-------+---------+---------------+----------+-------+-------------+

基本上,我想要做的是(如您所见)皇家邮政有两个条目,因为在连接表中有多个条目。我想要做的是显示两个皇家邮件条目中最高的一个(我最初尝试按service_id分组),同时还维护具有不同服务id的其他两个服务

任何帮助都是很棒的,因为这让我发疯。我觉得我已经尝试了所有的组合!

在下面的示例中,物品的数量(重量)为3 3kg

代码语言:javascript
复制
SELECT
    `service`.`name`,
    `charge`.`qty`,
    `service`.`serviceID`,
    `band`.`bandID`,
    `band`.`initial_charge`,
    `charge`.`chargeID`,
    `charge`.`price`,
    `band`.`initial_charge` + `charge`.`price` AS `total_price` 
FROM
    `delivery_band` AS `band` 
LEFT JOIN
    `delivery_charge` AS  `charge`
        ON 
            `charge`.`bandID` =  `band`.`bandID` 
        AND
            `charge`.`qty` <  '3'
LEFT JOIN
    `delivery_service` AS  `service`
        ON
            `service`.`serviceID` =  `band`.`serviceID` 
WHERE
    FIND_IN_SET(  '225',  `band`.`accepted_countries` ) 
AND
(
    `band`.`min_qty` >=  '3'
OR
    `band`.`min_qty` =  '0'
)
AND
(
    `band`.`max_qty` <=  '3'
OR
    `band`.`max_qty` =  '0'
)

delivery_service

代码语言:javascript
复制
+-----------+------------------+
| serviceID |       name       |
+-----------+------------------+
|         1 | Royal mail       |
|         2 | APC              |
|         3 | Collect in store |
+-----------+------------------+

delivery_band

代码语言:javascript
复制
+--------+-----------+-----------------+----------------+---------+---------+-------------------------------------------------------+
| bandID | serviceID |      name       | initial_charge | min_qty | max_qty |                  accepted_countries                   |
+--------+-----------+-----------------+----------------+---------+---------+-------------------------------------------------------+
|      1 |         2 | UK Mainland     | 0.00           |       0 |       0 | 225                                                   |
|      2 |         2 | UK Offshore     | 14.00          |       0 |       0 | 240                                                   |
|      3 |         3 | Bradford Store  | 0.00           |       0 |       0 | 225                                                   |
|      4 |         1 | UK              | 2.00           |       0 |       0 | 225                                                   |
|      5 |         2 | World wide      | 15.00          |       0 |       0 | 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20... |
|      6 |         1 | World wide Mail | 5.00           |       0 |       0 | 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20... |
+--------+-----------+-----------------+----------------+---------+---------+-------------------------------------------------------+

delivery_charge

代码语言:javascript
复制
+----------+--------+-----+-------+
| chargeID | bandID | qty | price |
+----------+--------+-----+-------+
|        1 |      2 |   0 | 5.00  |
|        2 |      3 |   0 | 0.00  |
|        3 |      4 |   0 | 0.00  |
|        4 |      4 |   1 | 1.00  |
|        5 |      4 |   5 | 3.00  |
|        6 |      1 |   0 | 5.95  |
|        7 |      1 |  10 | 10.95 |
|        8 |      2 |  10 | 14.00 |
|        9 |      5 |   0 | 0.00  |
|       10 |      5 |   3 | 5.00  |
|       11 |      5 |   6 | 10.00 |
|       12 |      5 |   9 | 15.00 |
|       13 |      6 |   0 | 0.00  |
|       14 |      6 |   2 | 5.00  |
|       15 |      6 |   4 | 10.00 |
|       16 |      6 |   6 | 15.00 |
+----------+--------+-----+-------+

当我尝试将收费表添加为子查询,然后限制该查询时,所有收费表字段的查询结果都为空

如果我尝试执行以下查询:

代码语言:javascript
复制
SELECT
    `service`.`name`,
    `charge`.`qty`,
    `service`.`serviceID`,
    `band`.`bandID`,
    `band`.`initial_charge`,
    `charge`.`chargeID`,
    MAX( `charge`.`price` ) AS `price`,
    `band`.`initial_charge` + `charge`.`price` AS `total_price` 
FROM
    `delivery_band` AS `band` 
LEFT JOIN
    `delivery_charge` AS  `charge`
        ON 
            `charge`.`bandID` =  `band`.`bandID` 
        AND
            `charge`.`qty` <  '3'
LEFT JOIN
    `delivery_service` AS  `service`
        ON
            `service`.`serviceID` =  `band`.`serviceID` 
WHERE
    FIND_IN_SET(  '225',  `band`.`accepted_countries` ) 
AND
(
    `band`.`min_qty` >=  '3'
OR
    `band`.`min_qty` =  '0'
)
AND
(
    `band`.`max_qty` <=  '3'
OR
    `band`.`max_qty` =  '0'
)
GROUP BY
    `service`.`serviceID`

我得到了下面的返回:

代码语言:javascript
复制
+------------------+-----+-----------+--------+----------------+----------+-------+-------------+
|       name       | qty | serviceID | bandID | initial_charge | chargeID | price | total_price |
+------------------+-----+-----------+--------+----------------+----------+-------+-------------+
| Royal mail       |   0 |         1 |      4 | 2.00           |        3 | 1.00  | 2.00        |
| APC              |   0 |         2 |      1 | 0.00           |        6 | 5.95  | 5.95        |
| Collect in store |   0 |         3 |      3 | 0.00           |        2 | 0.00  | 0.00        |
+------------------+-----+-----------+--------+----------------+----------+-------+-------------+

这在原则上看起来很好,直到您意识到chargeID =3的价格是0.00,而表中显示的价格是1.00,所以这些值似乎已经分离了

EN

回答 1

Stack Overflow用户

回答已采纳

发布于 2013-06-13 21:01:44

我想要做的是显示两个皇家邮件条目中最高的一个

您可以使用MAX获取给定列的最大值,例如

代码语言:javascript
复制
SELECT … MAX(charge.price) … FROM …

如果您绝对需要其他列(如charge.chargeID)来匹配,事情将变得复杂得多。所以要确保你真的需要它。有关这种查询背后的一般思想的详细信息,请仔细查看Select one value from a group based on order from other columns。通过@RichardTheKiwi改写this answer,我提出了以下查询:

代码语言:javascript
复制
SELECT s.name,
       c.qty,
       s.serviceID,
       b.bandID,
       b.initial_charge,
       c.chargeID,
       c.price,
       b.initial_charge + c.price AS total_price
FROM delivery_band AS b,
     delivery_service AS s,
    (SELECT chargeID, price, qty,
            @rowctr := IF(bandId = @lastBand, @rowctr+1, 1) AS rowNumber,
            @lastBand := bandId AS bandId
     FROM (SELECT @rowctr:=0, @lastBand:=null) init,
          delivery_charge
     WHERE qty < 3
     ORDER BY bandId, price DESC
    ) AS c
WHERE FIND_IN_SET(225, b.accepted_countries)
  AND (b.min_qty >= 3 OR B.min_qty = 0)
  AND (b.max_qty <= 3 OR B.max_qty = 0)
  AND s.serviceID = b.serviceID
  AND c.bandID = b.bandID
  AND c.rowNumber = 1

有关相应的输出,请参见this fiddle。请注意,我只做内部查询,而不是左边的查询,因为这对有问题的查询来说似乎足够了,并且保持了更多的可读性,这样你就可以专注于重要的部分,即涉及rowNumber的部分。其思想是,子查询为同一波段的项目生成行号,并为下一波段重新设置它们。当您只选择rowNumber为1的行时,您只会得到最高的价格,所有其他列都与此相关联。

票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/17083747

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档