我正在尝试显示我正在建设的商店的送货费用,数据库中有三个表1的服务,即皇家邮政,承运商...,一个为乐队。英国、欧洲、Worldwide1等。一个是费用(数量=重量)
我有一个包含三个表的数据库,当连接到下面的表中时
+------------------+-----+-----------+-------+---------+---------------+----------+-------+-------------+
| 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
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
+-----------+------------------+
| serviceID | name |
+-----------+------------------+
| 1 | Royal mail |
| 2 | APC |
| 3 | Collect in store |
+-----------+------------------+delivery_band
+--------+-----------+-----------------+----------------+---------+---------+-------------------------------------------------------+
| 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
+----------+--------+-----+-------+
| 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 |
+----------+--------+-----+-------+当我尝试将收费表添加为子查询,然后限制该查询时,所有收费表字段的查询结果都为空
如果我尝试执行以下查询:
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`我得到了下面的返回:
+------------------+-----+-----------+--------+----------------+----------+-------+-------------+
| 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,所以这些值似乎已经分离了
发布于 2013-06-13 21:01:44
我想要做的是显示两个皇家邮件条目中最高的一个
您可以使用MAX获取给定列的最大值,例如
SELECT … MAX(charge.price) … FROM …如果您绝对需要其他列(如charge.chargeID)来匹配,事情将变得复杂得多。所以要确保你真的需要它。有关这种查询背后的一般思想的详细信息,请仔细查看Select one value from a group based on order from other columns。通过@RichardTheKiwi改写this answer,我提出了以下查询:
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的行时,您只会得到最高的价格,所有其他列都与此相关联。
https://stackoverflow.com/questions/17083747
复制相似问题