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

Откуда:
Сообщений: 3953
Есть веб-сервис, который не дает прямого доступа к БД, а предоставляет его через обертку.
Текст SQL-запроса преобразовывается в соответствии с определенными правилами, передается в веб-сервис, тот преобразовывает его обратно, выполняет и возвращает результат.
Из-за этого я могу использовать только основные возможности SQL, даже left join сделать не могу.

Есть следующие таблицы:
-- Список абонентов
create table subscriber (id int, username varchar, disabled boolean);
insert into subscriber (id, username) values (1, 'user1');
insert into subscriber (id, username) values (2, 'user2');
insert into subscriber (id, username) values (3, 'user3');

-- Список пакетов услуг
create table package (id int, name varchar, disabled boolean);
insert into package (id, name) values (1, 'p1');
insert into package (id, name) values (2, 'p2');
insert into package (id, name) values (3, 'p3');
insert into package (id, name) values (4, 'p4');
insert into package (id, name) values (11, 'Package 1');
insert into package (id, name) values (12, 'Package 2');
insert into package (id, name) values (13, 'Package 3');

-- Список услуг
create table service (id int, name varchar);
insert into service (id, name) values (1,'s1');
insert into service (id, name) values (2,'s2');
insert into service (id, name) values (3,'s3');
insert into service (id, name) values (4,'s4');
insert into service (id, name) values (5,'s5');
insert into service (id, name) values (6,'s6');
insert into service (id, name) values (7,'s7');
insert into service (id, name) values (8,'s8');
insert into service (id, name) values (9,'s9');

-- Распределение услуг по пакетам, одна услуга может входить в несколько пакетов
create table package_service (package_id int, service_id int);

/* Пакеты p1-p4 используются в технологических целях.
Обычно услуга принадлежит только одному из пакетов p1-p4,
однако это не правило, в каких-то случаях услуга может
принадлежать одновременно p1 и p3, например.*/
insert into package_service (package_id, service_id) values (1,1);
insert into package_service (package_id, service_id) values (1,2);
insert into package_service (package_id, service_id) values (1,3);
insert into package_service (package_id, service_id) values (2,4);
insert into package_service (package_id, service_id) values (3,5);
insert into package_service (package_id, service_id) values (3,6);
insert into package_service (package_id, service_id) values (3,7);
insert into package_service (package_id, service_id) values (4,8);
insert into package_service (package_id, service_id) values (4,9);

/* Пакеты "Package 1" - "Package 3" используются во фронтенде.
Для подключения пакетов абонентов как правило используются именно они.
Возможна ситуация, когда абоненту подключен пакет (например) p1,
однако это всегда временное явление и в скором времени этот
пакет будет отключен.
Можно считать, что в нормальном режиме абонентам подключаются
только пакеты "Package 1" - "Package 3".*/
insert into package_service (package_id, service_id) values (11,1);
insert into package_service (package_id, service_id) values (11,2);
insert into package_service (package_id, service_id) values (11,3);
insert into package_service (package_id, service_id) values (11,4);
insert into package_service (package_id, service_id) values (12,5);
insert into package_service (package_id, service_id) values (12,6);
insert into package_service (package_id, service_id) values (12,7);

-- Подключенные абонентам пакеты, у одного абонента может быть несколько пакетов
create table subscriber_package (subscriber_id int, package_id int);
insert into subscriber_package (subscriber_id, package_id) values (1,11);
insert into subscriber_package (subscriber_id, package_id) values (2,12);
insert into subscriber_package (subscriber_id, package_id) values (3,11);
insert into subscriber_package (subscriber_id, package_id) values (3,12);
insert into subscriber_package (subscriber_id, package_id) values (3,13);


Мне нужно из этих данных получить примерно такую таблицу (последний столбец не нужен, он для пояснений):
ПакетыКоличествоПояснения
Технологические3
--p11Услуги s1, s2, s3
--p22Услуги s4
--p32Услуги s5, s6, s7
--p41Услуги s8, s9
Обычные3
--Package 12user1, user3
--Package 22user2, user3
--Package 33user3


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

Пока что я получил только такой запрос:
select distinct subscriber.username, all_p.name
from subscriber, subscriber_package, package, package_media, media, package_media all_pm, package all_p
where 0=0
and subscriber.id = subscriber_package.subscriber_id
and subscriber_package.package_id = package.id
and package.id = package_media.package_id
and package_media.media_id = media.id
and all_pm.media_id = media.id
and all_p.id = all_pm.package_id
order by subscriber.username, package.name, media.url

Можно ли обойтись без distinct?

________________________
Мы смотрим с оптимизмом...
...в оптический прицел.
20 ноя 12, 15:59    [13501456]     Ответить | Цитировать Сообщить модератору
 Re: Помогите получить итоговую выборку  [new]
Glory
Member

Откуда:
Сообщений: 104751
Alibek B.
даже left join сделать не могу.

Тогда огласите спискок, что можно использовать
20 ноя 12, 16:03    [13501515]     Ответить | Цитировать Сообщить модератору
 Re: Помогите получить итоговую выборку  [new]
Alibek B.
Member

Откуда:
Сообщений: 3953
select
from
where

В select теоретически могу использовать distinct, но то ли в реализации API баг, то ли на самом деле это не поддерживается, но не работает.
В from могу использовать альясы.
В where могу использовать in, not in, like, not like (помимо обычных операций сравнения).
20 ноя 12, 16:35    [13501854]     Ответить | Цитировать Сообщить модератору
 Re: Помогите получить итоговую выборку  [new]
Glory
Member

Откуда:
Сообщений: 104751
Подзапросы ?Функции ?

Сообщение было отредактировано: 20 ноя 12, 16:36
20 ноя 12, 16:36    [13501871]     Ответить | Цитировать Сообщить модератору
 Re: Помогите получить итоговую выборку  [new]
Alibek B.
Member

Откуда:
Сообщений: 3953
Подзапросы в виде field in (select id from ...) — без проблем.
Однако exists не поддерживается.
Функции некоторые поддерживаются, но нет нормального документирования. Лучше считать, что их нет.
20 ноя 12, 16:42    [13501941]     Ответить | Цитировать Сообщить модератору
 Re: Помогите получить итоговую выборку  [new]
Lavrov_Yura
Member

Откуда:
Сообщений: 34
Если я правильно понял несоответствие в столбце количества, то самое простое это
select package.name,count(*) from package join package_service on package_id=id group by service_id
union --или в две таблицы
select package.name,count(*) from package join subscriber_package on package_id=id group by subscriber_id

если у вас конечно работает count и group by
20 ноя 12, 17:02    [13502126]     Ответить | Цитировать Сообщить модератору
 Re: Помогите получить итоговую выборку  [new]
Alibek B.
Member

Откуда:
Сообщений: 3953
Нет, число услуг мне вообще не важно.
То что я в столбце "Пояснения" написал "Услуги s1, s2, s3" — это просто перечислены услуги, которые входят в данный пакет, чтобы было проще считать абонентов.
20 ноя 12, 17:55    [13502618]     Ответить | Цитировать Сообщить модератору
 Re: Помогите получить итоговую выборку  [new]
Alibek B.
Member

Откуда:
Сообщений: 3953
Вопрос решился, в синтаксисе distinct был нюанс, теперь он учтен.
Если кому поможет, конечный запрос (приведенный для JSON) такой:
    {
      'distinct' => [{'subscriber'=>'id'}, {'all_p'=>'id'}],
      'data' =>
      [
        {'subscriber'=>'id', 'as'=>'id'},
        {'subscriber'=>'username', 'as'=>'username'},
        {'subscriber'=>'disabled', 'as'=>'disabled'},
        {'all_p'=>'id', 'as'=>'package_id'},
        {'all_p'=>'name', 'as'=>'package_name'},
        {'all_p'=>'paid', 'as'=>'package_paid'},
        {'all_p'=>'adult', 'as'=>'package_adult'},
      ],
      'from' => ['subscriber', 'subscriber_package', 'package', 'package_media', 'media', {'table'=>'package_media', 'as'=>'all_pm'}, {'table'=>'package', 'as'=>'all_p'}],
      'where' =>
      {
        'and' =>
        [
          {'not_in' => [{'subscriber'=>'id'}, {'select' => $subquery}]},
          {'eq' => [{        'subscriber'=>'id'}         , {'subscriber_package'=>'subscriber_id'} ]},
          {'eq' => [{'subscriber_package'=>'package_id'} , {           'package'=>'id'}            ]},
          {'eq' => [{'subscriber_package'=>'enabled'}    , 'true'                                  ]},
          {'eq' => [{           'package'=>'id'}         , {     'package_media'=>'package_id'}    ]},
          {'eq' => [{     'package_media'=>'media_id'}   , {             'media'=>'id'}            ]},
          {'eq' => [{            'all_pm'=>'media_id'}   , {             'media'=>'id'}            ]},
          {'eq' => [{             'all_p'=>'id'}         , {            'all_pm'=>'package_id'}    ]},
        ]
      },
    };
20 ноя 12, 18:01    [13502671]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить