Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Достать несуществующие записи  [new]
Manonia
Member

Откуда: Иркутск
Сообщений: 112
Добрый день, подскажите с запросом. Есть 2 таблицы, в первой аккаунты с биржами, вторая содердит все дотупные биржи. На выходе нужно получить список аккаунтов и не включенных для них бирж.


AccountID ExchangeID
---------------------------
| 1 | 100 |
| 1 | 200 |
| 2 | 100 |
| 2 | 555 |
---------------------------

Доступные биржи:

ExchangeID
--------------
| 100 |
| 200 |
| 300 |
| 400 |
| 500 |
| 555 |
-------------

Результат:

AccountID ExchangeID
---------------------------
| 1 | 300 |
| 1 | 400 |
| 1 | 500 |
| 1 | 555 |
| 2 | 200 |
| 2 | 300 |
| 2 | 400 |
| 2 | 500 |
---------------------------
29 янв 15, 11:30    [17188032]     Ответить | Цитировать Сообщить модератору
 Re: Достать несуществующие записи  [new]
Добрый Э - Эх
Guest
cross join + not exists
29 янв 15, 11:36    [17188067]     Ответить | Цитировать Сообщить модератору
 Re: Достать несуществующие записи  [new]
iap
Member

Откуда: Москва
Сообщений: 47000
LEFT JOIN + IS NULL
29 янв 15, 11:45    [17188156]     Ответить | Цитировать Сообщить модератору
 Re: Достать несуществующие записи  [new]
Добрый Э - Эх
Guest
Добрый Э - Эх,

тупо в лоб:
with
  t1(AccountID,ExchangeID) as 
    (
      select 1,100 union
      select 1,200 union
      select 2,100 union
      select 2,555

    ),
  t2 (ExchangeID) as
    (
      select 100 union
      select 200 union
      select 300 union
      select 400 union
      select 500 union
      select 555
    )

select t1.AccountID, t2.ExchangeID
  from (select distinct t1.AccountID from t1) t1
 cross join
       t2
 where not exists
         (
           select null 
             from t1 t3 
            where t1.AccountID = t3.AccountID and t2.ExchangeID = t3.ExchangeID
         )
29 янв 15, 11:47    [17188171]     Ответить | Цитировать Сообщить модератору
 Re: Достать несуществующие записи  [new]
o-o
Guest
Добрый Э - Эх,

и inner сойдет:
declare @b table (AccountID  int,      ExchangeID  int);
insert into @b values       
( 1,               100    ),         
( 1,               200    ),       
( 2,               100    ),  
( 2,               555    );       

declare @ab table (ExchangeID  int);
insert into @ab values       
( 100           ),         
( 200           ),    
( 300           ),
( 400           ),       
( 500           ),  
( 555           )   


select distinct b.AccountID, ab.ExchangeID
from @b b join @ab ab on b.ExchangeID <> ab.ExchangeID
where not exists (select * from @b bb where bb.AccountID = b.AccountID and bb.ExchangeID = ab.ExchangeID)
order by 1
---
AccountID	ExchangeID
1	300
1	400
1	500
1	555
2	200
2	300
2	400
2	500


Manonia , а не лень палочки набирать, почему сразу не запятые и скобки,
думаете, так приятно их заменять?
29 янв 15, 11:47    [17188172]     Ответить | Цитировать Сообщить модератору
 Re: Достать несуществующие записи  [new]
Добрый Э - Эх
Guest
iap
LEFT JOIN + IS NULL
если быть точным, то PARTITION LEFT JOIN, но он в MS SQL Server-е недоступен.
29 янв 15, 11:48    [17188175]     Ответить | Цитировать Сообщить модератору
 Re: Достать несуществующие записи  [new]
Добрый Э - Эх
Guest
Manonia,

как вариант - всё тот же cross join + SET OPERATOR:
with
  t1(AccountID,ExchangeID) as 
    (
      select 1,100 union
      select 1,200 union
      select 2,100 union
      select 2,555

    ),
  t2 (ExchangeID) as
    (
      select 100 union
      select 200 union
      select 300 union
      select 400 union
      select 500 union
      select 555
    )

select t1.AccountID, t2.ExchangeID
  from (select distinct t1.AccountID from t1) t1
 cross join t2
except
select * from t1
       
29 янв 15, 11:58    [17188256]     Ответить | Цитировать Сообщить модератору
 Re: Достать несуществующие записи  [new]
Добрый Э - Эх
Guest
Добрый Э - Эх
Manonia,

как вариант - всё тот же cross join + SET OPERATOR:
with
  t1(AccountID,ExchangeID) as 
    (
      select 1,100 union
      select 1,200 union
      select 2,100 union
      select 2,555

    ),
  t2 (ExchangeID) as
    (
      select 100 union
      select 200 union
      select 300 union
      select 400 union
      select 500 union
      select 555
    )

select t1.AccountID, t2.ExchangeID
  from (select distinct t1.AccountID from t1) t1
 cross join t2
except
select * from t1
       

в принципе, можно и подзапрос не писать. except сам сделает финальный дистинкт:
select t1.AccountID, t2.ExchangeID
  from t1
 cross join t2
except
select * from t1
29 янв 15, 12:00    [17188270]     Ответить | Цитировать Сообщить модератору
 Re: Достать несуществующие записи  [new]
Manonia
Member

Откуда: Иркутск
Сообщений: 112
Спасибо, получилось!

А интересно, как получить вариант с третьей колонко IsEnabled? Т.е. получается у аккаунты должны быть указаны все биржи, не только отсутствующие, просто помечены флагом - есть или нет.
29 янв 15, 19:14    [17191609]     Ответить | Цитировать Сообщить модератору
 Re: Достать несуществующие записи  [new]
Добрый Э - Эх
Guest
Manonia,

всё так же. CROSS JOIN + case / isnull в селект-листе
29 янв 15, 19:24    [17191634]     Ответить | Цитировать Сообщить модератору
 Re: Достать несуществующие записи  [new]
Manonia
Member

Откуда: Иркутск
Сообщений: 112
И except нужен?
29 янв 15, 23:16    [17192435]     Ответить | Цитировать Сообщить модератору
 Re: Достать несуществующие записи  [new]
o-o
Guest
и except не нужен
declare @b table (AccountID  int,      ExchangeID  int);
insert into @b values       
( 1,               100    ),         
( 1,               200    ),       
( 2,               100    ),  
( 2,               555    );       

declare @ab table (ExchangeID  int);
insert into @ab values       
( 100           ),         
( 200           ),    
( 300           ),
( 400           ),       
( 500           ),  
( 555           );  


select distinct b.AccountID, ab.ExchangeID,
       case when not exists (select * from @b bb where bb.AccountID = b.AccountID and bb.ExchangeID = ab.ExchangeID)
            then 0
            else 1
       end as IsEnabled     
from @b b cross join @ab ab 
order by 1,2;

-- or

select distinct b.AccountID, ab.ExchangeID,
       case when bb.AccountID is null then 0
            else 1
       end as IsEnabled                     
from @b b cross join @ab ab 
          left  join @b bb on b.AccountID = bb.AccountID and bb.ExchangeID = ab.ExchangeID
order by 1,2;
29 янв 15, 23:40    [17192583]     Ответить | Цитировать Сообщить модератору
 Re: Достать несуществующие записи  [new]
Manonia
Member

Откуда: Иркутск
Сообщений: 112
o-o, зашла написать, что через left join получилось ) но всё равно спасибо )
29 янв 15, 23:47    [17192624]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить