Блог

Главная - Вопросы по программированию - Подсчет итогов по двум отдельным таблицам SQL

Подсчет итогов по двум отдельным таблицам SQL

#sql #sql-server #sql-server-2012

#sql #sql-сервер #sql-server-2012

Вопрос:

У меня есть две (идентичные для этой цели) таблицы

Список задач

 ItemBatch | Item
   abc       Pickup sticks
   abc       Tie my shoe
   xyz       Suck my thumb
   abc       Climb a tree

Список выполненных действий

 ItemBatch | Item
   abc      Tie my shoe

Я пытаюсь получить подсчет от каждого из них для каждого [ItemBatch], но не уверен, действительно ли это возможно в одном запросе?

Отдельно я бы просто сделал

 select count(*) from ToDoList where ItemBatch = 'abc' /* repeat for each ItemBatch */

select count(*) from DoneList where ItemBatch = 'abc' /* repeat for each ItemBatch */

и присваивайте переменным во внешнем интерфейсе, чтобы я мог получить следующее:

 ItemBatch | Total | DoneList | DoneList - ToDoList
   abc       3        1           2
   xyz       1        0           1

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

 select count(cc.[ItemBatch]), count(cq.[ItemBatch])
from ToDoList cc
left join DoneList cq on cc.[ItemBatch] = cq.[ItemBatch]

Быстро понял, что это не сработает…

Затем я подумал о

 select
(select count(*) from ToDoList where [ItemBatch] = 'abc' ) as Total,
(select count(*) from DoneList where [ItemBatch] = 'abc' ) as DoneList

но я не могу понять, как вывести все строки для каждой ItemBatch за один раз, group by не работает для этого последнего утверждения:

 select
    (select [ItemBatch], count([ItemBatch]) from ToDoList group by [ItemBatch) as Total,
    (select [ItemBatch], count([ItemBatch]) from DoneList group by [ItemBatch]) as DoneList

Ответ №1:

Вы должны попытаться использовать OUTER APPLY в вашем случае из-за вычисления в SELECT инструкции.

Оператор ВНЕШНЕГО ПРИМЕНЕНИЯ возвращает все строки из левого табличного выражения независимо от его соответствия с правым табличным выражением. Для тех строк, для которых нет соответствующих совпадений в правильном табличном выражении, оно содержит значения NULL в столбцах правильного табличного выражения.

Итак, ваше утверждение должно быть:

 SELECT 
        cc.ItemBatch, 
        COUNT(*) as Total,
        ISNULL(dl.DoneListTotal, 0) as DoneList,
        COUNT(*) - ISNULL(dl.DoneListTotal, 0) as [DoneList - ToDoList]
FROM ToDoList cc
     OUTER APPLY (SELECT COUNT(*) DoneListTotal
                  FROM DoneList cq
                  WHERE cc.ItemBatch = cq.ItemBatch) dl
GROUP BY cc.ItemBatch, dl.DoneListTotal;

Комментарии:

1. Мне пришлось добавить запятую и dl.DoneListTotal как часть GROUP BY , но в остальном это сработало отлично, спасибо!!

Ответ №2:

Ваш LEFT JOIN метод будет работать, если вы используете правильные JOIN условия:

 select count(cc.[ItemBatch]), count(cq.[ItemBatch])
from ToDoList cc left join
     DoneList cq
     on cc.[ItemBatch] = cq.[ItemBatch] AND cc.Item = cq.Item;

За ItemBatch вы бы добавили GROUP BY :

 select ItemBatch, count(*), count(cq.ItemBatch)
from ToDoList cc left join
     DoneList cq
     on cc.ItemBatch = cq.ItemBatch AND cc.Item = cq.Item
group by cc.ItemBatch

Комментарии:

1. «… левое соединение … условия правильного соединения:» вот хороший пример того, чтобы не использовать «правильный» как синоним правильного.

Ответ №3:

Используется UNION ALL для получения всех ItemBatch значений из 2 таблиц с 2 дополнительными столбцами ToDo и Done для указания, к какой таблице они принадлежат, и для агрегирования:

 SELECT ItemBatch,
       SUM(Todo) ToDo,
       SUM(Done) Done,
       SUM(Todo) - SUM(Done) Difference
FROM (
  SELECT ItemBatch, 1 ToDo, 0 Done FROM ToDoList
  UNION ALL
  SELECT ItemBatch, 0, 1 FROM DoneList 
) t
GROUP BY ItemBatch

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

 > ItemBatch | ToDo | Done | Difference
> :-------- | ---: | ---: | ---------:
> abc       |    3 |    1 |          2
> xyz       |    1 |    0 |          1
Метки: