我正在尝试获取一个多级查询,以给出在我看来很简单的答案。我有 2500 行针对邮政编码的数据。每行包含邮政编码、人员姓名首字母、主要成本和次要成本。我想根据主要成本获得邮政编码的最低成本 - 这很容易 - 但是如果有多个人对同一邮政编码具有相同的主要成本,我需要返回次要成本最低的人。例:
postcode, initials, p_cost, s_cost
AB12 , DS , 100 , 0
AB12 , JH , 100 , 40
AB12 , SS , 120 , 0
因此,我想从中取出DS,因为它们与JH一起具有最低的主要成本,但它们的次要成本也是两者中最低的。这是我到目前为止拥有的 SQL:
SELECT initials, min(s_cost) FROM
(SELECT * FROM MyTable
WHERE postcode = 'AB12'
and p_cost =
(select min(p_cost) from MyTable
WHERE postcode = 'AB12'));
我也试过:
SELECT * FROM MyTable
WHERE postcode = 'AB12'
and p_cost = (select min(p_cost)
from MyTable
WHERE postcode = 'AB12'
and s_cost = (select min(s_cost)
from MyTable
WHERE postcode = 'AB12'));
有人可以让我摆脱这里的痛苦吗,这应该很简单,但距离我上次写这篇文章已经 15 年了,SQL 很生气,这真的很困扰我。
谢谢大家的帮助:-)
您是否尝试过使用连接条件作为过滤器将表连接到自身?
select mt1.*
from mytable mt1
left join mytable mt2
on mt1.postcode = mt2.postcode
and ( mt1.p_cost < mt2.p_cost
or (mt1.p_cost = mt2.p_cost and mt1.s_cost < mt2.s_cost)
)
where mt1.postcode = 'AB12'
顺便说一下,这段代码未经测试。
也许你可以试试这个。
Select *
from MyTable
where postcode = 'AB12'
order by p_cost asc, s_cost asc
limit 1;
首先您需要计算每个邮政编码的最低p_cost是多少
SELECT postcode, MIN(primary_cost) as min_primary_cost
FROM MyTable
WHERE postcode = 'AB12'
现在您需要计算secondary_cost的最小值是多少,但基于主要成本
SELECT primary_cost, MIN(secondary_cost) as min_primary_cost
FROM MyTable
WHERE postcode = 'AB12'
GROUP BY primary_cost
现在将两者连接在一起
SQL 演示
SELECT *
FROM YourTable
JOIN ( SELECT postcode, MIN(primary_cost) as min_primary_cost
FROM YourTable
WHERE postcode = 'AB12'
) as p_cost
ON YourTable.postcode = p_cost.postcode
AND YourTable.primary_cost = p_cost.min_primary_cost
JOIN ( SELECT primary_cost, MIN(secondary_cost) as min_secondary_cost
FROM YourTable
WHERE postcode = 'AB12'
GROUP BY primary_cost
) as s_cost
ON YourTable.primary_cost = s_cost.primary_cost
AND YourTable.secondary_cost= s_cost.min_secondary_cost
WHERE postcode = 'AB12'