VPF::Полное объединение таблиц? - Форум программистов

ekaterina_tw

Дата 29.4.2009, 14:16 (ссылка)

(нет голосов)

Загрузка ...

Новичок

Профиль
Группа: Участник
Сообщений: 14
Регистрация: 25.1.2008

Репутация: нет
Всего: нет

Здравствуйте, помогите написать запрос. (MS SQL 2005)

такая задача:

есть таблица:

Код


клиент_1 | акции ВТБ    | 1
клиент_2 | aкции Сбер  |  2

В результате надо получить таблицу:

клиент_1 | акции_ВТБ    |  1
клиент_2 | aкции_Сбер |   2
клиент_1 | акции Сбер  |   0
клиент_2 | aкции ВТБ    |   0

Т.е. в результате для каждого клиента должны быть записи со всеми имеющимися разновидностями акций.

Буду благодарна за помощь.
Спасибо!

Zloxa

Дата 29.4.2009, 15:05 (ссылка)

(нет голосов)

Загрузка ...

Чо?

Профиль
Группа: Завсегдатай
Сообщений: 3473
Регистрация: 12.9.2008

Репутация: 53
Всего: 161

Код


SQL> with t as (
  2   select 'клиент_1' client, 'акции ВТБ' active, 1 cnt from dual
  3   union all select 'клиент_2','aкции Сбер',2 from dual
  4  )
  5  select clients.client
  6         ,actives.active
  7         ,nvl(t.cnt,0) cnt -- nvl заменить на is_null
  8  from (select distinct client from t) clients -- или справочник клиентов
  9  inner join (select distinct active from t) actives on 1=1 -- или справочник активов. Можно попробовать cross join, не знаю держит ли MS SQL его
 10  left join (select client,active,sum(cnt) cnt from t group by client,active) t
 11   on clients.client = t.client
 12     and actives.active = t.active
 13  ;
 
CLIENT   ACTIVE            CNT
-------- ---------- ----------
клиент_2 aкции Сбер          2
клиент_1 акции ВТБ           1
клиент_2 акции ВТБ           0
клиент_1 aкции Сбер          0

Это сообщение отредактировал(а) Zloxa - 29.4.2009, 16:22

--------------------

Достоверно известно, что 89% людей доверяют статистике взятой с потолка smile

ТоляМБА

Дата 29.4.2009, 15:07 (ссылка)

(нет голосов)

Загрузка ...

Котэ

Профиль
Группа: Завсегдатай
Сообщений: 1607
Регистрация: 15.12.2004

Репутация: 4
Всего: 252

...Удалил...
Потому что проверял на связях 1 клиент имеет акцию одного банка, а если акции нескольких банков - запрос выдаёт ерунду.

Сорри.

Это сообщение отредактировал(а) ТоляМБА - 29.4.2009, 15:33

DimW

Дата 29.4.2009, 15:09 (ссылка)

(нет голосов)

Загрузка ...

Эксперт

Профиль
Группа: Завсегдатай
Сообщений: 1330
Регистрация: 24.2.2005
Где: Орёл

Репутация: 4
Всего: 44

Код


SQL> create table test_stock
  2  ( client varchar2(50)
  3   ,stock varchar2(50)
  4   ,value number);
 
Table created
SQL> insert into test_stock values('client 1', 'stock 1', 10);
 
1 row inserted
SQL> insert into test_stock values('client 2', 'stock 2', 20);
 
1 row inserted
SQL> select t1.client
  2        ,t2.stock
  3        ,nvl((select t3.value from test_stock t3 where t3.client = t1.client and t3.stock = t2.stock), 0) value
  4    from test_stock t1
  5        ,test_stock t2;
 
CLIENT                                             STOCK                                                   VALUE
-------------------------------------------------- -------------------------------------------------- ----------
client 1                                           stock 1                                                    10
client 2                                           stock 1                                                     0
client 1                                           stock 2                                                     0
client 2                                           stock 2                                                    20
SQL> drop table test_stock;
 
Table dropped

Это сообщение отредактировал(а) DimW - 29.4.2009, 15:55

Zloxa

Дата 29.4.2009, 16:11 (ссылка)

(нет голосов)

Загрузка ...

Чо?

Профиль
Группа: Завсегдатай
Сообщений: 3473
Регистрация: 12.9.2008

Репутация: 53
Всего: 161

DimW,

Код


insert into test_stock values('client 2', 'stock 1', 30);

Цитата(ekaterina_tw @ 29.4.2009, 14:16

)

В результате для каждого клиента должны быть записи со всеми имеющимися разновидностями акций.

--------------------

Достоверно известно, что 89% людей доверяют статистике взятой с потолка smile

DimW

Дата 29.4.2009, 16:28 (ссылка)

(нет голосов)

Загрузка ...

Эксперт

Профиль
Группа: Завсегдатай
Сообщений: 1330
Регистрация: 24.2.2005
Где: Орёл

Репутация: 4
Всего: 44

да, косяк.

Код


SQL> create table test_stock
  2  ( client varchar2(50)
  3   ,stock varchar2(50)
  4   ,value number);
 
Table created
SQL> insert into test_stock values('client 1', 'stock 1', 10);
 
1 row inserted
SQL> insert into test_stock values('client 2', 'stock 2', 20);
 
1 row inserted
SQL> insert into test_stock values('client 2', 'stock 1', 30);
 
1 row inserted
SQL> insert into test_stock values('client 3', 'stock 2', 40);
 
1 row inserted
SQL> insert into test_stock values('client 3', 'stock 3', 50);
 
1 row inserted
SQL> insert into test_stock values('client 3', 'stock 3', 50);
 
1 row inserted
SQL> select distinct t1.client
  2        ,t2.stock
  3        ,nvl((select sum(t3.value) from test_stock t3 where t3.client = t1.client and t3.stock = t2.stock), 0) value
  4    from test_stock t1
  5        ,test_stock t2;
 
CLIENT                                             STOCK                                                   VALUE
-------------------------------------------------- -------------------------------------------------- ----------
client 1                                           stock 1                                                    10
client 1                                           stock 2                                                     0
client 1                                           stock 3                                                     0
client 2                                           stock 1                                                    30
client 2                                           stock 2                                                    20
client 2                                           stock 3                                                     0
client 3                                           stock 1                                                     0
client 3                                           stock 2                                                    40
client 3                                           stock 3                                                   100
 
9 rows selected
SQL> drop table test_stock;
 
Table dropped

Это сообщение отредактировал(а) DimW - 29.4.2009, 16:30

ekaterina_tw

Дата 29.4.2009, 16:42 (ссылка)

(нет голосов)

Загрузка ...

Новичок

Профиль
Группа: Участник
Сообщений: 14
Регистрация: 25.1.2008

Репутация: нет
Всего: нет

ТоляМБА,
спасибо преогромное! кажется сработало! урааааа

DimW,
это условие нужно для того, чтобы в результат не включались строки типа:

клент_1 | акции_Сбер | 0,
если акции_Сбер у клиента_1 уже есть, т.е. значение не 0.

Всем большое спасибо за предложенные варианты!

DimW

Дата 29.4.2009, 16:46 (ссылка)

(нет голосов)

Загрузка ...

Эксперт

Профиль
Группа: Завсегдатай
Сообщений: 1330
Регистрация: 24.2.2005
Где: Орёл

Репутация: 4
Всего: 44

Цитата(ekaterina_tw @ 29.4.2009, 16:42

)

DimW,
это условие нужно для того, чтобы в результат не включались строки типа:

клент_1 | акции_Сбер | 0,
если акции_Сбер у клиента_1 уже есть, т.е. значение не 0.

ну ну

ТоляМБА

Дата 29.4.2009, 16:52 (ссылка)

(нет голосов)

Загрузка ...

Котэ

Профиль
Группа: Завсегдатай
Сообщений: 1607
Регистрация: 15.12.2004

Репутация: 4
Всего: 252

Цитата(ekaterina_tw @ 29.4.2009, 18:42

)

ТоляМБА, спасибо преогромное! кажется сработало! урааааа

ekaterina_tw, не-не-не, не вздумай делать этого! smile

Я же описал что проверял на

Цитата(ТоляМБА @ 29.4.2009, 17:07

)

на связях 1 клиент имеет акцию одного банка, а если акции нескольких банков - запрос выдаёт ерунду.

ekaterina_tw

Дата 29.4.2009, 21:40 (ссылка)

(нет голосов)

Загрузка ...

Новичок

Профиль
Группа: Участник
Сообщений: 14
Регистрация: 25.1.2008

Репутация: нет
Всего: нет

ну вот, а я проверяю весь день, вроде нормально работает :(

уже облагородила запрос и все написала, ни разу ерунду не выдал.
Пожалуйста покажите, в каком случае будет неправильный результат? Очень нужно, спасибо.

Добавлено через 2 минуты и 33 секунды
Как же это сделать, используя FULL OUTER JOIN?

Добавлено через 5 минут и 58 секунд
DimW,
может поясните свою мысль?
ваш запрос не поняла, содержимое таблицы мне не известно, Insert использовать не могу

ekaterina_tw

Дата 29.4.2009, 21:58 (ссылка)

(нет голосов)

Загрузка ...

Новичок

Профиль
Группа: Участник
Сообщений: 14
Регистрация: 25.1.2008

Репутация: нет
Всего: нет

Zloxa,
а что такое 'dual' в вашем запросе?

ekaterina_tw

Дата 29.4.2009, 22:18 (ссылка)

(нет голосов)

Загрузка ...

Новичок

Профиль
Группа: Участник
Сообщений: 14
Регистрация: 25.1.2008

Репутация: нет
Всего: нет

Цитата(ТоляМБА @ 29.4.2009, 16:52

)

Цитата(ТоляМБА @ 29.4.2009, 17:07 )
на связях 1 клиент имеет акцию одного банка, а если акции нескольких банков - запрос выдаёт ерунду.

давайте попробуем разобраться...
запрос выдает таблицу, в которой для одного клиента могут содержаться строки типа:
клиент_1, сток_1, 1
клиент_1, сток_1, 0
это устраняется оператором group by, или вы что-то другое получили в результате?

Добавлено через 5 минут и 6 секунд
Zloxa,
Ваш запрос возвращает таблицу с нулевыми строками, хотя может я его неправильно интерпритировала.

Код


with t as (
  2   select 'клиент_1' client, 'акции ВТБ' active, 1 cnt from dual
  3   union all select 'клиент_2','aкции Сбер',2 from dual
  4  )

при чем здесь ''клиент_1', 'клиент_2'? я же не знаю, какие строки содержатся в таблице, клиентов я для примера написала smile

ТоляМБА

Дата 29.4.2009, 22:27 (ссылка)

(нет голосов)

Загрузка ...

Котэ

Профиль
Группа: Завсегдатай
Сообщений: 1607
Регистрация: 15.12.2004

Репутация: 4
Всего: 252

Цитата(ekaterina_tw @ 29.4.2009, 23:40

)

Пожалуйста покажите, в каком случае будет неправильный результат?

При данных:
Klient    Bank    Akcii
Клиент1    Банк1    1
Клиент1    Банк4    14

Получим результат
Klient    Bank    Akcii
Клиент1    Банк1    1
Клиент1    Банк2    0
Клиент1    Банк3    0
Клиент1    Банк4    0
Клиент1    Банк4    14
что не есть правильно. Можно конечно сделать вот так:

Код

SELECT a.Klient, a.Bank, Sum(a.Akcii) AS Akcii1
FROM (SELECT BankKlient.Klient, BankKlient.Bank, BankKlient.Akcii
FROM BankKlient
UNION SELECT BankKlient.Klient,   BankKlient_1.Bank, 0 as Akcii
FROM BankKlient INNER JOIN BankKlient AS BankKlient_1 ON (BankKlient.Bank <> BankKlient_1.Bank) AND (BankKlient.Klient <> BankKlient_1.Klient)) AS a
GROUP BY a.Klient, a.Bank
ORDER BY 1, 2

но почему-то я уверен что будет он очень тормозить при большом количестве записей.

ekaterina_tw

Дата 29.4.2009, 22:35 (ссылка)

(нет голосов)

Загрузка ...

Новичок

Профиль
Группа: Участник
Сообщений: 14
Регистрация: 25.1.2008

Репутация: нет
Всего: нет

вы хотите сказать, что Bank2 и Bank3  в таблице не содержится, но строчки
Клиент1    Банк2    0
Клиент1    Банк3    0
создаются?
что такое Банк2, Банк3? откуда беруться эти значения? уверена, что в таблице они есть. Да?

ТоляМБА

Дата 29.4.2009, 22:38 (ссылка)

(нет голосов)

Загрузка ...

Котэ

Профиль
Группа: Завсегдатай
Сообщений: 1607
Регистрация: 15.12.2004

Репутация: 4
Всего: 252

Полная моя таблица для проверки:

Klient    Bank    Akcii
Клиент1    Банк1    1
Клиент1    Банк4    14
Клиент2    Банк2    2
Клиент3    Банк3    3
Клиент4    Банк4    4

Добавлено через 1 минуту
Я имел ввиду что при первом моем запросе строчка
Клиент1    Банк4    0
есть косяк.

ekaterina_tw

Дата 29.4.2009, 22:43 (ссылка)

(нет голосов)

Загрузка ...

Новичок

Профиль
Группа: Участник
Сообщений: 14
Регистрация: 25.1.2008

Репутация: нет
Всего: нет

спасибо.
если таблица такого вида

Klient    Bank    Akcii
Клиент1    Банк1    1
Клиент1    Банк4    14
Клиент2    Банк2    2
Клиент3    Банк3    3
Клиент4    Банк4    4

, то мне нужен результат:

Klient    Bank    Akcii
Клиент1    Банк1    1
Клиент1    Банк2    0
Клиент1    Банк3    0
Клиент1    Банк4    14

Клиент2    Банк1    0
Клиент2    Банк2    2
Клиент2    Банк3    0
Клиент2    Банк4    0

Клиент3    Банк1    0
Клиент3    Банк2    0
Клиент3    Банк3    3
Клиент3    Банк4    0

Клиент4    Банк1    0
Клиент4    Банк2    0
Клиент4    Банк3    0
Клиент4    Банк4    4

По-моему первый вариант, который Вы написали, как раз это и выдает.

Добавлено через 2 минуты и 35 секунд

Цитата(ТоляМБА @ 29.4.2009, 22:38

)

Я имел ввиду что при первом моем запросе строчка
Клиент1 Банк4 0
есть косяк.

ага, понятно.
я имею ввиду, что при использовании вашего запроса внутри SELECT sum(Akcii) GROUP BY Банк
результат получается верным. Разве нет?

ТоляМБА

Дата 29.4.2009, 22:47 (ссылка)

(нет голосов)

Загрузка ...

Котэ

Профиль
Группа: Завсегдатай
Сообщений: 1607
Регистрация: 15.12.2004

Репутация: 4
Всего: 252

Цитата(ekaterina_tw @ 30.4.2009, 00:43

)

По-моему первый вариант, который Вы написали, как раз это и выдает.

Первый запрос выдаст:

Klient    Bank    Akcii
Клиент1    Банк1    1
Клиент1    Банк2    0
Клиент1    Банк3    0
Клиент1    Банк4    0
Клиент1    Банк4    14

Клиент2    Банк1    0
Клиент2    Банк2    2
Клиент2    Банк3    0
Клиент2    Банк4    0

Клиент3    Банк1    0
Клиент3    Банк2    0
Клиент3    Банк3    3
Клиент3    Банк4    0

Клиент4    Банк1    0
Клиент4    Банк2    0
Клиент4    Банк3    0
Клиент4    Банк4    4

красным выделен косяк.

ekaterina_tw

Дата 29.4.2009, 22:51 (ссылка)

(нет голосов)

Загрузка ...

Новичок

Профиль
Группа: Участник
Сообщений: 14
Регистрация: 25.1.2008

Репутация: нет
Всего: нет

ага, а GROUP BY прекрасно с этим косяком справится.

ТоляМБА

Дата 29.4.2009, 22:51 (ссылка)

(нет голосов)

Загрузка ...

Котэ

Профиль
Группа: Завсегдатай
Сообщений: 1607
Регистрация: 15.12.2004

Репутация: 4
Всего: 252

Цитата(ekaterina_tw @ 29.4.2009, 23:40

)

Как же это сделать, используя FULL OUTER JOIN?

Нету у меня под рукой MS-SQL, писал на акцессе, а он FULL OUTER JOIN не поддерживает smile

Добавлено через 2 минуты и 13 секунд

Цитата(ekaterina_tw @ 30.4.2009, 00:51

)

ага, а GROUP BY прекрасно с этим косяком справится

Нет, с ним справился SUM, а GROUP BY из-за того что используется агрегированая ф-ия.

DimW

Дата 30.4.2009, 08:51 (ссылка)

(нет голосов)

Загрузка ...

Эксперт

Профиль
Группа: Завсегдатай
Сообщений: 1330
Регистрация: 24.2.2005
Где: Орёл

Репутация: 4
Всего: 44

Цитата(ekaterina_tw @ 29.4.2009, 21:40

)

может поясните свою мысль?
ваш запрос не поняла, содержимое таблицы мне не известно, Insert использовать не могу

1) я создаю таблицу.
2) наполняю ее данными.
3) выполняю запрос который должен решить вашу проблему, с данными которые на втором шаге я вставил в таблицу.

Цитата(ekaterina_tw @ 29.4.2009, 22:18

)

при чем здесь ''клиент_1', 'клиент_2'? я же не знаю, какие строки содержатся в таблице

это тестовые данные, представленны для наглядности.

Zloxa

Дата 30.4.2009, 09:25 (ссылка)

(нет голосов)

Загрузка ...

Чо?

Профиль
Группа: Завсегдатай
Сообщений: 3473
Регистрация: 12.9.2008

Репутация: 53
Всего: 161

Цитата(ekaterina_tw @ 29.4.2009, 21:40

)

Как же это сделать, используя FULL OUTER JOIN?

Здесь full outer не нужен.
Тема озаглавлена не правильно.

Цитата(ekaterina_tw @ 29.4.2009, 21:58

)

что такое 'dual'

dual эта таблица, которая содержит одну запись
Используется для того, чтобы иммитировать исходные данные.
Сам запрос начинается с пятой строки.
Что такое "возвращает нулевые строки", мне не понятно

--------------------

Достоверно известно, что 89% людей доверяют статистике взятой с потолка smile

ekaterina_tw

Дата 30.4.2009, 14:45 (ссылка)

(нет голосов)

Загрузка ...

Новичок

Профиль
Группа: Участник
Сообщений: 14
Регистрация: 25.1.2008

Репутация: нет
Всего: нет

DimW,
спасибо, действительно все верно. Отличный вариант!

Zloxa,
спасибо! Тоже все работает отлично!

DimW

Дата 30.4.2009, 14:54 (ссылка)

(нет голосов)

Загрузка ...

Эксперт

Профиль
Группа: Завсегдатай
Сообщений: 1330
Регистрация: 24.2.2005
Где: Орёл

Репутация: 4
Всего: 44

Цитата(ekaterina_tw @ 30.4.2009, 14:45

)

DimW,
спасибо, действительно все верно. Отличный вариант!

по поводу отличного я сомниваюсь, всетаки два фулскана и как минимум одна сортировка. хотя в mssql может все не так как я думаю.

0 Пользователей читают эту тему (0 Гостей и 0 Скрытых Пользователей)
0 Пользователей:
« Предыдущая тема \| Составление SQL-запросов \| Следующая тема »