Блог

Я имею в виду базу данных Chinook, которую я использую для изучения SQLite. Этот запрос извлекает количество счетов-фактур для каждого идентификатора клиента, как я и хотел:

     select i.customerid, count(i.invoiceid)
    from invoices as i
    group by i.customerid
 

ВОЗВРАТ:

  ------------ -------------------- 
| CustomerId | count(i.invoiceid) |
 ------------ -------------------- 
|          1 |                  7 |
|          2 |                  7 |
|          3 |                  7 |
...
 

Но когда я строил более сложный запрос, я заметил кое-что, что не могу объяснить:

     select i.customerid, count(i.invoiceid)
    from invoices as i, customers as c
    group by i.customerid
 

ВОЗВРАТ:

  ------------ -------------------- 
| CustomerId | count(i.invoiceid) |
 ------------ -------------------- 
|          1 |                413 |
|          2 |                413 |
|          3 |                413 |
...
 

Получается 413 = 7 * 59, а 59 — это число различных идентификаторов клиентов.
Должно быть, здесь есть какое-то фундаментальное поведение SQL, которое я неправильно понимаю, потому что я не ожидал бы никакой разницы, добавив «клиенты как c» в предложение «от», поскольку я его еще не использую. Может ли кто-нибудь просветить меня здесь о том, что происходит?

Никогда не используйте запятые в FROM предложении. Используйте только правильный, явный, стандартный, читаемый JOIN синтаксис.

Ваш запрос создает декартово произведение строк в двух таблицах. Затем ваша агрегация подсчитывает количество строк для каждого клиента в декартовом продукте.

Тебе нужно что-то вроде этого:

 select i.customerid, count(i.invoiceid)
from invoices i join
     customers c
     on i.customerid = c.customerid
group by i.customerid
 

Вы выполняете перекрестное соединение, которое является декартовым произведением строк ваших 2 таблиц. Вы были правы относительно происхождения значения 413.
При перекрестном соединении, если таблица A содержит 5 строк, а таблица B содержит 7 строк, это приведет к результату 5 * 7 = 35 rows .

При соединении таблиц вам необходимо добавить условие соединения, которое будет фильтровать несвязанные строки (перекрестные соединения редко являются тем, что вам нужно).:

 SELECT i.customerid, count(i.invoiceid)
FROM invoices as i, customers as c
WHERE i.customerid = c.id    -- join condition
GROUP BY i.customerid
 

Но рекомендуемый синтаксис для соединения является явным (без запятой).:

 SELECT i.customerid, count(i.invoiceid)
FROM invoices as i
    JOIN customers as c       -- explicit join
    ON i.customerid = c.id    -- join condition
GROUP BY i.customerid
 

Но это будет выполняться INNER JOIN по умолчанию , для чего требуется, чтобы строки из invoices таблицы совпадали по крайней мере с 1 строкой из customers , и наоборот.
Если вы все еще хотите отображать клиентов с 0 счетами-фактурами, вам нужно использовать LEFT JOIN для сохранения строк из левой таблицы (первой в FROM предложении), даже если они не совпадают с правой таблицей:

 SELECT i.customerid, count(i.invoiceid)
FROM invoices as i
    LEFT JOIN customers as c  -- keep customers without invoices
    ON i.customerid = c.id    -- join condition, unchanged
GROUP BY i.customerid