Блог

Итак, я уже некоторое время борюсь с SQL-запросом quit.
Вот чего я хотел бы достичь :

Допустим, у нас есть 3 таблицы: фрукты, фермеры и продажи

Эти таблицы структурированы и заполнены следующим образом.

Фрукты :

 __|_fruit______|_taste_
 1| apple      | sweet
 2| banana     | sweet
 3| grapefruit | sour
  

Фермеры :

 __|_farmer___|_field_____
 1| ben      | apple
 2| mary     | banana
 3| mike     | grapefruit
 4| joanna   | apple
 5| clara    | grapefruit
  

Продажи :

 __|_fruit______|_amount__
 1| apple      | 50
 2| banana     | 25
 3| apple      | 30
 4| grapefruit | 40
 5| banana     | 45
 6| apple      | 30
  

Теперь я хотел бы иметь возможность посмотреть, какие и сколько из этих фруктов было продано. Результат, с которым я мог бы работать, выглядел бы примерно так

(ГДЕ фрукты.fruit = «яблоко») :

 fruits.fruit | farmers.farmer | sales.amount
apple        | ben            | 50
apple        | joanna         | 30
apple        | null           | 30
  

или вот так

(ГДЕ фрукты.fruit = «банан») :

 fruits.fruit | farmers.farmer | sales.amount
banana       | mary           | 25
banana       | null           | 45
  

или вот так

(ГДЕ фрукты.fruit = «грейпфрут») :

 fruits.fruit | farmers.farmer | sales.amount
grapefruit   | mike           | 40
grapefruit   | clara          | null
  

Я бы сохранил эти результаты в php и позже мог бы определить, какому фермеру нужно заплатить какую сумму денег, потому что фермерам следует платить соответствующую сумму в зависимости от проданной суммы.

Я хотел бы сделать это только с одним SQL-запросом и без таблицы перекрестных ссылок.
Мой подход к проблеме был следующим (что привело к большому сбою) :

 SELECT fruits.fruit, farmers.farmer, sales.amount
FROM fruits
JOIN farmers ON farmers.field = fruits.fruit
JOIN sales ON sales.fruit = fruits.fruit
WHERE fruits.fruit = "apple"
  

Последняя строка запросов должна измениться, чтобы получить ранее показанные результаты.

Разумно ли делать это без таблицы перекрестных ссылок?
Возможно ли это сделать?
Существует ли здесь совершенно другой подход, который был бы предпочтительнее?

Заранее спасибо

ПРАВКА # 1: добавлена отсутствующая запись в sales (6)
ПРАВКА # 2: MySQL — версия 5.7.17

Я думаю, вы ищете group by :

 SELECT fr.fruit, fa.farmer, sum(s.amount)
FROM sales JOIN
     fruits fr
     s.fruit = fr.fruit LEFT JOIN
     farmers fa
     ON fa.field = fr.fruit 
GROUP BY fr.fruit, fa.farmer;
  

Это будет работать для MySQL 8.0:

 with 
  farmers as (
    SELECT row_number() over ( order by null ) rn, farmer, field from farmers
    WHERE field = 'apple'
  ),    
  sales as (
    SELECT row_number() over ( order by null ) rn, fruit, amount from sales
    WHERE fruit = 'apple'
  )
select s.fruit, f.farmer, s.amount
from sales s left join farmers f
on f.rn = s.rn
  

Смотрите демонстрацию
Результаты:

 | fruit | farmer | amount |
| ----- | ------ | ------ |
| apple | ben    | 50     |
| apple | joanna | 30     |
| apple | null   | 30     |
  

Редактировать для MySQL 5.5:

 select s.fruit, f.farmer, s.amount
from (
  select (@row_number1:=@row_number1   1) AS rn, fruit, amount
  from sales, (select @row_number1:=0) t
  where sales.fruit = 'apple'
) s left join (
  select (@row_number2:=@row_number2   1) AS rn, farmer
  from farmers, (select @row_number2:=0) t
  where farmers.field = 'apple'
) f
on f.rn = s.rn
  

Смотрите демонстрацию