SQL Server: перекрестное применение - запись 0 для результатов NULL

T1 - это таблица компании и их (нескольких пользователей), T2 - таблица зарегистрированных пользователей. Я подсчитал для каждой компании в T1, сколько их пользователей находятся в T2, но нужно, чтобы c3 появился в таблице результатов с #regUser == 0:

T1:

company         user
c1               u1
c1               u2
c2               u2
c2               u3
c3               u4
c3               u1

T2:

user
u2
u3

Итоговая таблица должна выглядеть так:

company         #regUser
c1                 1
c2                 2
c3                 0

С помощью следующего кода я получаю результаты только для ненулевых компаний:

select t1s.company, count(1)
from (select * from t1) t1s
  cross apply (select *
               from t2 t2s
               where t2s.reguser = t1s.[user]) t12s
group by t1s.company

Спасибо


sql-server cross-apply
person shanlodh    schedule 21.03.2019    source источник
comment
Этот запрос слишком сложен. from (select * from t1) t1s, например, эквивалентно просто from t1. Почему бы вам не попробовать простое левое соединение двух таблиц?   -  person Panagiotis Kanavos    schedule 21.03.2019
comment
Почему это написано с подзапросами? Это можно было бы просто записать как select t1s.company, count(1) from t1 t1s JOIN t2 t2s ON t2s.reguser = t1s.[user] group by t1s.company   -  person Larnu    schedule 21.03.2019

Ответы (4)


arrow_upward
3
arrow_downward

просто используйте левое соединение

   select t1.company,count(t2.user)
     from t1 left join t2 on t1.user=t2.user
group by t1.company

подзапрос не требуется в соответствии с вашими требованиями

но если вы хотите использовать apply, вам понадобится запрос ниже

select t1s.company, count(t12s.Users)
from (select * from t1) t1s
  outer apply (select Users
               from t2 t2s
               where t2s.Users = t1s.[Users]) t12s
group by t1s.company

выход

company     #regUser
c1          1
c2          2
c3          0

удалить ссылку

person Zaynul Abadin Tuhin    schedule 21.03.2019

arrow_upward
2
arrow_downward

Вы можете использовать LEFT JOIN, чтобы получить всю информацию из левой таблицы с совпадающей информацией из правой таблицы. Используя GROUP BY, вы можете сгруппировать строки по компаниям и получить COUNT зарегистрированных пользователей для каждой компании:

SELECT t1.company, COUNT(t2.[user]) AS regUser 
FROM t1 LEFT JOIN t2 ON t1.[user] = t2.[user]
GROUP BY t1.company
ORDER BY t1.company ASC

Вы также можете использовать CROSS APPLY, чтобы решить эту проблему:

SELECT t1.company, SUM(CASE WHEN t1.[user] = t2.[user] THEN 1 ELSE 0 END) AS regUser 
FROM t1 CROSS APPLY t2
GROUP BY t1.company
ORDER BY t1.company ASC

демонстрация на dbfiddle.uk

person Sebastian Brosch    schedule 21.03.2019

arrow_upward
1
arrow_downward

Все, что вам нужно, это левое соединение:

select company,count(t2.[user])
from t1 left outer join t2 on t1.[user]=t2.[user]
group by company

Запрос вопроса слишком сложен. Например, from (select * from t1) t1s эквивалентно from t1 as t1s.

person Panagiotis Kanavos    schedule 21.03.2019

arrow_upward
0
arrow_downward

Просто LEFT JOIN с SUM()

CREATE TABLE T1(
  Company VARCHAR(20),
  Users VARCHAR(20)
);

CREATE TABLE T2(
  Users VARCHAR(20)
);

INSERT INTO T1 VALUES
('c1', 'u1'),
('c1', 'u2'),
('c2', 'u2'),
('c2', 'u3'),
('c3', 'u4'),
('c3', 'u1');

INSERT INTO T2 VALUES
('u2'),
('u3');

SELECT T1.Company,
       SUM(CASE WHEN T2.Users IS NULL THEN 0 ELSE 1 END) Cnt
FROM T1 LEFT JOIN T2
ON T1.Users = T2.Users
GROUP BY T1.Company;

Возврат:

+---------+-----+
| Company | Cnt |
+---------+-----+
| c1      |   1 |
| c2      |   2 |
| c3      |   0 |
+---------+-----+

Live Demo

person Ilyes    schedule 21.03.2019