Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / IBM DB2, WebSphere, IMS, U2, etc Новый топик    Ответить
 Помогите с запросом  [new]
Semen Popov
Member

Откуда: Сыктывкар
Сообщений: 793
Здравствуйте всем!

Помогите реализовать запрос. Есть таблица (ACOUNTS) с полями ID человека (MAN_ID), Номер счета (ACCOUNT). У одного человека может быть несколько счетов в этой таблице. Нужен скрипт, который покажет счета, которые принадлежат одновременно разным людям (людей тоже показать). Чувствую, что это просто, но пока ничего не приходит в голову. Спасибо

С уважением, Семен Попов
24 дек 14, 11:00    [17044424]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
vitabios
Member

Откуда: Тула
Сообщений: 678
Semen Popov,
А можно подробнее структуру этой таблицы?
24 дек 14, 11:57    [17044809]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
londinium
Member

Откуда: Киев
Сообщений: 1181
Не уверен, что правильно понял
Если у вас структура данных такая: Contragents(контрагенты), Accounts(счета), LinkContragentAccount(связь счетов и контрагентов), то можно попробовать так
WITH DoubleAccounts AS
(
  SELECT L.Account_ID
 FROM LinkContragentAccount
GROUP BY L.Account_ID
HAVING COUNT(Account_ID)>1
)

SELECT * FROM LinkContragentAccount L WHERE EXISTS
(
  SELECT 1 FROM DoubleAccounts D WHERE L.Account_ID=D.Account_ID 
)
24 дек 14, 12:10    [17044901]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
n&n
Guest
Semen Popov,

такое?

> db2 "with accounts(man_id, account) as (values (100,'account_1'),(101,'account_2'),(102, 'account_1'),(103,'account_3'),(104,'account_3'))
select account, substr(listagg(cast(man_id as char(5)),','),1,50) as persons from accounts group by account having count(man_id)>1"

ACCOUNT   PERSONS
--------- --------------------------------------------------
account_1 100  ,102
account_3 103  ,104

  2 record(s) selected.
24 дек 14, 14:53    [17046277]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
Semen Popov
Member

Откуда: Сыктывкар
Сообщений: 793
vitabios, я думаю, вы знаете таблицу PAYSUM.RECIPIENT_FILE очень известного нам приложения.

londinium, немного не то. Структура таблицы такова, что в ней содержится ID человека(char(30)) и сам номер счета (varchar(40)). По одному ID человека в таблице может быть несколько записей повторяющихся с одним и тем же счетом, а может быть и записи с разными счетами у одного ID человека. Но интересуют счета, если они в этой таблице принадлежат разным людям, то есть один и тот же счет может сидеть в таблице с разным ID человека.

n&n, это уже ближе. Но тут будет проблема, если в таблице несколько одинаковых записей по MAN_ID и ACCOUNT, а такое допускается. Например, когда запись (100, 'Account_1') сидит в таблице дважды, и тогда запрос выдаст счет Account_1, даже если этот счет не принадлежит другому человеку.
24 дек 14, 15:46    [17046770]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
Евгений Хабаров
Member

Откуда: Москва
Сообщений: 773
Semen Popov,

Что-то типа такого?
select distinct account,man_id from accounts 
where account in ( select account from accounts group by account having count(man_id)>1) 
order by account
24 дек 14, 15:54    [17046837]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
londinium
Member

Откуда: Киев
Сообщений: 1181
автор
я думаю, вы знаете таблицу PAYSUM.RECIPIENT_FILE очень известного нам приложения.

Не томите, расскажите, что за приложение.
24 дек 14, 16:01    [17046917]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
n&n
Guest
Semen Popov
Но тут будет проблема, если в таблице несколько одинаковых записей по MAN_ID и ACCOUNT


with accounts0(man_id, account) as 
(values (100,'account_1'),[b](101,'account_2'),(101,'account_2')[/b],(102, 'account_1'),(103,'account_3'),(104,'account_3')), 
accounts1(sint) as (select distinct man_id||'#'||account from accounts0), 
accounts2(man_id, account) as (select substr(sint,1,posstr(sint,'#')-1) as man_id, substr(sint,posstr(sint,'#')+1,length(sint)) 
   from accounts1) 
select account, substr(listagg(cast(man_id as char(5)),','),1,50) as persons 
   from accounts2 group by account having count(man_id)>1

ACCOUNT               PERSONS
--------------------- --------------------------------------------------
account_1             100  ,102
account_3             103  ,104

  2 record(s) selected.


хотя можно и не так громоздко и коряво
24 дек 14, 16:17    [17047064]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
Semen Popov
Member

Откуда: Сыктывкар
Сообщений: 793
londinium
автор
я думаю, вы знаете таблицу PAYSUM.RECIPIENT_FILE очень известного нам приложения.

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

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

n&n, громоздковато. Но попробую взять вашу идею
24 дек 14, 17:28    [17047593]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
Добрый Э - Эх
Guest
Semen Popov,
select * 
  from (
          select t.*, 
                 count(distinct MAN_ID) over(partition by ACCOUNT) as cnt
            from ACOUNTS
          ) v
 where cnt > 1
24 дек 14, 17:32    [17047625]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
Добрый Э - Эх
Guest
Добрый Э - Эх
Semen Popov,
select * 
  from (
         select t.*, 
                count(distinct MAN_ID) over(partition by ACCOUNT) as cnt
           from ACCOUNTS t
       ) v
 where cnt > 1
24 дек 14, 17:35    [17047643]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
knudsen
Member

Откуда: Москва
Сообщений: 311
столько можно способов придумать...

/*
CREATE TABLE TMP.A( A1 INTEGER, A2 INTEGER);

-- A1 = MAN_ID
-- A2 = ACCOUNT

INSERT INTO TMP.A VALUES(1, 1);
INSERT INTO TMP.A VALUES(1, 1);
INSERT INTO TMP.A VALUES(1, 2);
INSERT INTO TMP.A VALUES(2, 1);
INSERT INTO TMP.A VALUES(2, 3);
INSERT INTO TMP.A VALUES(3, 3);
*/

WITH 
S13 (A1, A2)  AS (SELECT  DISTINCT A1, A2 FROM TMP.A), 
S24 (A2, CNT) AS (SELECT A2,COUNT(*) CNT FROM S13 GROUP BY A2 HAVING COUNT(*)>1)
SELECT S13.* FROM S13  INNER JOIN S24 ON S24.A2 = S13.A2;


результат:
A1	A2
1	1
2	1
2	3
3	3
24 дек 14, 18:30    [17047989]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
Mark Barinstein
Member

Откуда: Москва
Сообщений: 4946
Добрый Э - Эх
Добрый Э - Эх
Semen Popov,
select * 
  from (
         select t.*, 
                count(distinct MAN_ID) over(partition by ACCOUNT) as cnt
           from ACCOUNTS t
       ) v
 where cnt > 1

DISTINCT нельзя с OLAP функциями.

+ как-то так
with ACCOUNTS (MAN_ID, ACCOUNT) AS (VALUES
  (1, '1')
, (1, '2')
, (1, '2')
, (2, '2')
, (2, '3')
, (3, '4')
, (3, '4')
)
SELECT ACCOUNT, VARCHAR(LISTAGG(MAN_ID, ','), 10) MAN_IDS
FROM (
SELECT DISTINCT A.ACCOUNT, A.MAN_ID
FROM ACCOUNTS A
JOIN (
SELECT ACCOUNT
FROM ACCOUNTS
GROUP BY ACCOUNT
HAVING COUNT(DISTINCT MAN_ID) > 1
) G ON G.ACCOUNT=A.ACCOUNT
) 
GROUP BY ACCOUNT

ACCOUNT MAN_IDS   
------- ----------
2       1,2       
24 дек 14, 18:51    [17048112]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
Добрый Э - Эх
Guest
Mark Barinstein
DISTINCT нельзя с OLAP функциями.
что ли в IBM пошли а разрез со стандартами ANSI-SQL ?
В Оракле можно, в MS SQL Server можно, а в тут нельзя?
24 дек 14, 19:09    [17048209]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
Добрый Э - Эх
Guest
Добрый Э - Эх
Mark Barinstein
DISTINCT нельзя с OLAP функциями.
что ли в IBM пошли а разрез со стандартами ANSI-SQL ?
В Оракле можно, в MS SQL Server можно, а в тут нельзя?

раз прямолинейно и безхитростно решить не получается, то можно сделать небольшой финт ушами:
select * 
  from (
         select t.*, 
                max(MAN_ID) over(partition by ACCOUNT) as x_max,
                min(MAN_ID) over(partition by ACCOUNT) as x_min
           from ACCOUNTS t
       ) v
 where x_max != x_min
24 дек 14, 19:43    [17048312]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
Semen Popov
Member

Откуда: Сыктывкар
Сообщений: 793
Ребята, забыл предупредить, что DB2 WSE 9.7.6.
На функцию LISTAGG (из скрипта Марка и n&n) ругается
SQL0440N  Не найдено доступной подпрограммы "LISTAGG" типа "FUNCTION" с 
совместимыми аргументами.  SQLSTATE=42884


Еще раз про структуру таблицы ACCOUNTS - MAN_ID char(30), ACCOUNT varchar(40).

Всем спасибо. Будем пробовать.
24 дек 14, 22:19    [17048771]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
Евгений Хабаров
Member

Откуда: Москва
Сообщений: 773
Semen Popov
Евгений Хабаров, не подходит. Выводит и те записи счетов, которые относятся только к одному гражданину.


Если у вас дублирующие записи по одному и тому же человеку, то тогда чуть сложнее
select distinct account,man_id from accounts
where account in 
( 
  select account from 
  (
    select distinct account,man_id from accounts
  ) 
  group by account having count(man_id)>1
) 
order by account
25 дек 14, 10:55    [17049982]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
Mark Barinstein
Member

Откуда: Москва
Сообщений: 4946
Добрый Э - Эх
Mark Barinstein
DISTINCT нельзя с OLAP функциями.
что ли в IBM пошли а разрез со стандартами ANSI-SQL ?
В Оракле можно, в MS SQL Server можно, а в тут нельзя?

Да, похоже на то.

Добиться count distinct можно, но несколько сложнее:
select DISTINCT ACCOUNT, MAN_ID
from (
select ACCOUNT, MAN_ID, MAX(DENSE_RANK() OVER (PARTITION BY ACCOUNT ORDER BY MAN_ID)) OVER (PARTITION BY ACCOUNT) CNT_DISTINCT
from ACCOUNTS
)
where CNT_DISTINCT>1
order by ACCOUNT

Semen Popov
Ребята, забыл предупредить, что DB2 WSE 9.7.6.
На функцию LISTAGG (из скрипта Марка и n&n) ругается

SQL0440N  Не найдено доступной подпрограммы "LISTAGG" типа "FUNCTION" с 
совместимыми аргументами.  SQLSTATE=42884
Это просто удобная ф-ция для склеивания связанных значений. Если ее нет, можно пользоваться запросом выше.
Ф-ция появилясь в 9.7.4, и если ее нет, то вы, вероятно, при накате фикспака не сделали:
db2updv97 -d mydb
поэтому у вас она и не появилась.
25 дек 14, 12:02    [17050385]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
Semen Popov
Member

Откуда: Сыктывкар
Сообщений: 793
Всем спасибо. Все получилось
25 дек 14, 18:24    [17053317]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
vitabios
Member

Откуда: Тула
Сообщений: 678
Semen Popov,

Эх жаль не успел посмотреть. Не было на работе несколько дней. В понедельник обязательно посмотрю , самому интересно разобраться.
27 дек 14, 09:23    [17060455]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
vitabios
Member

Откуда: Тула
Сообщений: 678
Semen Popov,

Приложение я сразу догадался какое, только в тот день таблицу не нашел.
27 дек 14, 09:25    [17060458]     Ответить | Цитировать Сообщить модератору
Все форумы / IBM DB2, WebSphere, IMS, U2, etc Ответить