Попытка захватить столбец из двух присоединяемых подзапросов и группировка не работает

КАК ВЫГЛЯДЯТ МОИ СТОЛЫ:

mysql> select * from customer limit 3;
+-------------+---------------+-----------------------+------+--------+-------------+-------+
| customer_id | customer_name | profession            | age  | salary | town        | state |
+-------------+---------------+-----------------------+------+--------+-------------+-------+
|           1 | Julio Sperski | Architect             |   70 |  52016 | Conroe      | TX    |
|           2 | Micah Inchley | Biological  scientist |   86 |  45355 | Omaha       | NE    |
|           3 | Brigg Denny   | Chemist               |   80 |  21754 | Bakersfield | CA    |
+-------------+---------------+-----------------------+------+--------+-------------+-------+
3 rows in set (0.00 sec)

mysql> select * from vehicle limit 3;
+------------+---------------+--------------------+--------+--------+---------+----------------+--------------------+-----------------------+
| vehicle_id | vehicle_plate | registration_state | color  | make   | model   | vehicle_type   | per_day_rental_fee | per_day_insurance_fee |
+------------+---------------+--------------------+--------+--------+---------+----------------+--------------------+-----------------------+
|          1 | W9FLYC7       | TX                 | black  | toyota | cruiser | mid size sedan |                 44 |                    27 |
|          2 | CA1CJIZ       | NE                 | silver | ford   | se      | suv            |                 96 |                    71 |
|          3 | HB5YI9A       | CA                 | silver | dodge  | mpv     | truck          |                 26 |                    28 |
+------------+---------------+--------------------+--------+--------+---------+----------------+--------------------+-----------------------+
3 rows in set (0.00 sec)

mysql> select * from rental limit 3;
+-----------+-------------+------------+-------------------+--------------------+-------------------------+----------------------------+
| rental_id | customer_id | vehicle_id | start_rental_date | return_rental_date | per_day_rental_fee_paid | per_day_insurance_fee_paid |
+-----------+-------------+------------+-------------------+--------------------+-------------------------+----------------------------+
|         1 |          32 |          4 | 3/4/2019          | 3/6/2019           | no                      | no                         |
|         2 |          42 |         39 | 3/23/2019         | 3/24/2019          | yes                     | yes                        |
|         3 |          33 |         14 | 10/18/2020        | 10/24/2020         | no                      | no                         |
+-----------+-------------+------------+-------------------+--------------------+-------------------------+----------------------------+
3 rows in set (0.00 sec)

Customer_id, Vehicle_id и Rental_id являются первичными ключами.

Customer_id и Vehicle_id — это внешние ключи, указывающие на таблицы клиентов и транспортных средств соответственно.

Итак, я пытаюсь захватить 3-й столбец и отобразить его. Запрос примерно такой:

Для каждого штата регистрации, в котором работает компания по аренде автомобилей, верните наибольшее количество клиентов из определенного штата клиентов. Например, если есть 5 клиентов из Техаса, но они арендуют автомобиль, зарегистрированный в Калифорнии, и есть только 2 клиента из Нью-Йорка, арендующие автомобили, зарегистрированные в Калифорнии, я бы вернул 5 из Техаса, арендующих в Калифорнии, и так далее, пока я не закончу все состояния регистрации.

ПЕРВЫЙ ЗАПРОС, Самое близкое, что я получил, это запрос:

SELECT registration_state, 
       Max(count) AS count 
FROM   (SELECT registration_state, 
               Count(a.state) AS count, 
               a.state 
        FROM   rental c 
               LEFT JOIN customer a 
                      ON a.customer_id = c.customer_id 
               LEFT JOIN vehicle b 
                      ON c.vehicle_id = b.vehicle_id 
        GROUP  BY registration_state, 
                  a.state)Z 
GROUP  BY registration_state 
ORDER  BY registration_state, 
          count DESC; 

+--------------------+-------+
| registration_state | count |
+--------------------+-------+
| AL                 |     1 |
| CA                 |     5 |
| DC                 |     1 |
| DE                 |     2 |
| FL                 |     3 |
| IL                 |     2 |
| IN                 |     1 |
| MD                 |     2 |
| MI                 |     1 |
| MN                 |     1 |
| MO                 |     1 |
| NE                 |     1 |
| NV                 |     2 |
| NY                 |     3 |
| OH                 |     2 |
| OR                 |     1 |
| PA                 |     1 |
| SC                 |     1 |
| TN                 |     3 |
| TX                 |     7 |
| WA                 |     1 |
+--------------------+-------+
21 rows in set (0.01 sec)

Однако он отображает только Registration_state и количество тех штатов клиентов, которые арендуют больше всего в том штате, в котором компания по аренде автомобилей арендует автомобили, но без отображения состояния клиента, я хочу, чтобы состояние клиента отображалось.

ВТОРОЙ ЗАПРОС. Следующий запрос генерирует и возвращает подсчет суммы арендной платы из каждого состояния клиента для каждого состояния регистрации:

SELECT registration_state, 
       Count(d.state) AS count, 
       d.state 
FROM   rental f 
       LEFT JOIN customer d 
              ON d.customer_id = f.customer_id 
       LEFT JOIN vehicle e 
              ON f.vehicle_id = e.vehicle_id 
GROUP  BY registration_state, 
          d.state 
ORDER  BY registration_state, 
          count DESC; 

+--------------------+-------+-------+
| registration_state | count | state |
+--------------------+-------+-------+
| AL                 |     1 | NY    |
| CA                 |     5 | CA    |
| CA                 |     4 | MO    |
| CA                 |     2 | IN    |
| CA                 |     2 | TN    |
| CA                 |     2 | TX    |
| CA                 |     2 | OH    |
| CA                 |     1 | FL    |
| CA                 |     1 | AL    |
| CA                 |     1 | MI    |
| CA                 |     1 | NE    |
| CA                 |     1 | WA    |
| DC                 |     1 | CA    |
| DC                 |     1 | IL    |
| DC                 |     1 | TX    |
| DE                 |     2 | NY    |
| DE                 |     1 | FL    |
| FL                 |     3 | NY    |
| FL                 |     1 | AL    |
| FL                 |     1 | OH    |
| FL                 |     1 | CA    |
| FL                 |     1 | FL    |
| FL                 |     1 | MI    |
| FL                 |     1 | TX    |
| IL                 |     2 | OR    |
| IL                 |     1 | TX    |
| IL                 |     1 | CA    |
| IN                 |     1 | NV    |
| IN                 |     1 | CA    |
| MD                 |     2 | WA    |
| MD                 |     1 | OH    |
| MD                 |     1 | MD    |
| MI                 |     1 | PA    |
| MN                 |     1 | PA    |
| MN                 |     1 | TN    |
| MN                 |     1 | FL    |
| MO                 |     1 | NY    |
| MO                 |     1 | SC    |
| MO                 |     1 | OH    |
| MO                 |     1 | OR    |
| MO                 |     1 | CA    |
| MO                 |     1 | FL    |
| MO                 |     1 | TX    |
| NE                 |     1 | CA    |
| NV                 |     2 | FL    |
| NY                 |     3 | TX    |
| NY                 |     1 | CA    |
| NY                 |     1 | FL    |
| NY                 |     1 | MO    |
| NY                 |     1 | NY    |
| OH                 |     2 | OH    |
| OH                 |     1 | TN    |
| OH                 |     1 | NE    |
| OH                 |     1 | PA    |
| OH                 |     1 | DC    |
| OH                 |     1 | NY    |
| OR                 |     1 | IN    |
| OR                 |     1 | CA    |
| PA                 |     1 | MO    |
| PA                 |     1 | DC    |
| SC                 |     1 | FL    |
| SC                 |     1 | NY    |
| TN                 |     3 | TX    |
| TN                 |     2 | CA    |
| TN                 |     1 | FL    |
| TN                 |     1 | PA    |
| TN                 |     1 | MI    |
| TN                 |     1 | OH    |
| TN                 |     1 | OR    |
| TN                 |     1 | MO    |
| TX                 |     7 | NY    |
| TX                 |     4 | TX    |
| TX                 |     3 | FL    |
| TX                 |     2 | MO    |
| TX                 |     2 | MD    |
| TX                 |     2 | DC    |
| TX                 |     1 | IN    |
| TX                 |     1 | OH    |
| TX                 |     1 | CA    |
| TX                 |     1 | NV    |
| TX                 |     1 | OR    |
| TX                 |     1 | IL    |
| WA                 |     1 | TN    |
| WA                 |     1 | MI    |
+--------------------+-------+-------+
84 rows in set (0.00 sec)

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

Я пытаюсь сделать так, чтобы это выглядело так:

+--------------------+-------+-------+
| registration_state | count | state |
+--------------------+-------+-------+
| AL                 |     1 | NY    |
| CA                 |     5 | CA    |
| DC                 |     1 | CA    |
| DE                 |     2 | NY    |
| FL                 |     3 | NY    |
| IL                 |     2 | OR    |
| IN                 |     1 | NV    |
| MD                 |     2 | WA    |
| MI                 |     1 | PA    |
| MN                 |     1 | PA    |
| MO                 |     1 | NY    |
| NE                 |     1 | CA    |
| NV                 |     2 | FL    |
| NY                 |     3 | TX    |
| OH                 |     2 | OH    |
| OR                 |     1 | IN    |
| PA                 |     1 | MO    |
| SC                 |     1 | FL    |
| TN                 |     3 | TX    |
| TX                 |     7 | NY    |
| WA                 |     1 | TN    |
+--------------------+-------+-------+

Я пробовал присоединиться так:

SELECT    registration_state, 
          MAX(count)
FROM      ( 
                    SELECT    registration_state, 
                              Count(d.state) AS count, 
                              d.state 
                    FROM      rental f 
                    LEFT JOIN customer d 
                    ON        d.customer_id = f.customer_id 
                    LEFT JOIN vehicle e 
                    ON        f.vehicle_id = e.vehicle_id 
                    GROUP BY  registration_state, 
                              d.state)W 
RIGHT JOIN 
          ( 
                    SELECT    registration_state AS registration_state2, 
                              count(a.state)     AS count2, 
                              a.state 
                    FROM      rental c 
                    LEFT JOIN customer a 
                    ON        a.customer_id = c.customer_id 
                    LEFT JOIN vehicle b 
                    ON        c.vehicle_id = b.vehicle_id 
                    GROUP BY  registration_state, 
                              a.state )z 
ON        z.count2=W.count 
AND      z.registration_state2=W.registration_state
GROUP BY registration_state
ORDER BY registration_state;

Но он просто возвращает то же, что и первый запрос, если я добавляю состояние из любого из подзапросов в оператор выбора в первой строке, а также группирую по указанному состоянию в конце, я получаю второй запрос.

Есть ли какие-либо предложения о том, как заставить его вернуться так, как я хочу?

mysql sql count inner-join greatest-n-per-group

18.12.2020 18:58

Освоение архитектуры микросервисов с Laravel: Лучшие практики, преимущества и советы для...

В последние годы архитектура микросервисов приобрела популярность как способ построения масштабируемых и гибких приложений. Laravel , популярный PHP...

Как построить CRUD-приложение в Laravel

Laravel - это популярный PHP-фреймворк, который позволяет быстро и легко создавать веб-приложения. Одной из наиболее распространенных задач в...

Освоение PHP и управление базами данных: Создание собственной СУБД - часть II

В предыдущем посте мы создали функциональность вставки и чтения для нашей динамической СУБД. В этом посте мы собираемся реализовать функции обновления...

Документирование API с помощью Swagger на Springboot

В предыдущей статье мы уже узнали, как создать Rest API с помощью Springboot и MySql .

Роли и разрешения пользователей без пакета Laravel 9

Этот пост изначально был опубликован на techsolutionstuff.com .

Как установить LAMP Stack - Security 5/5 на виртуальную машину Azure Linux VM

В предыдущей статье мы завершили установку базы данных, для тех, кто не знает.

Перейти к ответу Данный вопрос помечен как решенный

Ответы 1

Ответ принят как подходящий

Если вы используете MySQL 8.0, вы можете использовать оконные функции, чтобы получить состояние клиента с наибольшим количеством арендных платежей для каждого состояния регистрации:

select registration_state, state, cnt
from (
    select v.registration_state, c.state, count(*) as cnt,
        rank() over(partition by v.registration_state order by count(*) desc) rn
    from rental r
    inner join customer c on c.customer_id = r.customer_id 
    inner join vehicle  v on r.vehicle_id = v.vehicle_id 
    group  by v.registration_state, c.state 
) t
where rn = 1
order  by registration_state;

В более ранних версиях это немного сложнее. Один вариант использует коррелированный подзапрос, ограничивающий количество строк, в предложении having:

select v.registration_state, c.state, count(*) as cnt
from rental r
inner join customer c on c.customer_id = r.customer_id 
inner join vehicle  v on r.vehicle_id = v.vehicle_id 
group  by v.registration_state, c.state 
having count(*) = (
    select count(*)
    from rental r1
    inner join customer c1 on c1.customer_id = r1.customer_id 
    inner join vehicle  v1 on r1.vehicle_id = v1.vehicle_id 
    where c1.state = c.state and v1.registration_state = v.registration_state
    group by v1.registration_state, c1.state 
    order by count(*) desc limit 1
)

Обратите внимание, что оба запроса допускают верхние связи, если таковые имеются.

А, я вижу, так что вместо того, чтобы группировать в конце, как я сделал, оконная функция в этом подзапросе вместо этого считает каждую строку и возвращает результат для каждой строки, и вы получаете 1-й результат с рангом как rn. Спасибо!

— 18.12.2020 21:17

18.12.2020 20:53