Логічно створювати шаблони на основі таблиці перетинів

Я не знаю, як описати це питання, але я намагаюся дати пропозиції про те, які коди приписувати тарифам. Я роблю це в Oracle.

Ось моя структура бази даних:

CODE (
    CODEID *PK NCHAR(10)
)

CODETARIFF (
    TARIFFNO NCHAR(15) *PK *FK
    CODEID NCHAR(10) *PK *FK
)

TARIFF (
    TARIFFNO NCHAR(15) *PK
)

Тому я намагаюся логічно зробити шаблони для того, які коди повинні бути призначені для тарифів. Я уявляю собі щось подібне: "6 тарифів також мають ці 2 коди, пов'язані з ними"

Я спробував це, але кількість, які повертаються для кожного коду, насправді не показують шаблон, вони лише дійсно показують випадки, коли цей один код відображається з двома я вказав.

SELECT COUNT(*), CodeID
FROM CodeTariff
  INNER JOIN (
    SELECT TariffNo, COUNT(*) 
    FROM CodeTariff
    WHERE CodeID IN ('ABC', 'DEF') 
    GROUP BY TariffNo
    HAVING COUNT(*) > 1) SQ 
  ON CodeTariff.TariffNo = SQ.TariffNo 
WHERE CodeID NOT IN ('ABC', 'DEF')
GROUP BY CodeTariff.CodeID
ORDER BY COUNT(*) DESC;

На жаль, це незрозуміло.

Я не знаю, чи це можливо, але я шукаю вивід, як це:

Дані:     TariffCode

TariffNo        CodeID

1111            ABC
1111            DEF
2222            ABC
2222            DEF
2222            GHI
2222            JKL
3333            ABC
3333            DEF
3333            GHI
3333            JKL

Вихід: (при наданні тарифу 1111)

CodesToAdd      Count

GHI, JKL        2

Щоб я міг показати:

2 інших тарифах мають пов'язані з ними коди GHI та JKL. Хочете додати ці коди до тарифу 1111?

0
Можливо, деякі зразкові дані з очікуваним результатом можуть зробити це більш чітким?
додано Автор Tom H, джерело
Чи це правильно? : Послідовність подій: (1) користувач створює новий тариф; (2) користувач позначає тариф з деякими існуючими кодами ( 'ABC' і 'DEF' ); (3) додаток дає користувачеві кілька пропозицій щодо інших кодів, яким він хоче позначити тариф. Логіка цих пропозицій полягає в тому, що додаток знаходить вже існуючі тарифи, позначені як 'ABC' , так і 'DEF' , і бачить, які інші коди найчастіше зустрічаються серед вже існуючих тарифів. Якщо це правильно, то ваш запит виглядає правильно для мене. . . для кожного коду він показує, скільки існуючих тарифів позначено цим.
додано Автор ruakh, джерело
Я думаю, що бачу. Причина, яку ви хочете запропонувати 'GHI' і 'JKL' , не те, що вони кожен використовуються для двох тарифів, які мають 'ABC' і 'DEF' , але оскільки два з них використовуються разом на два тарифа, які мають 'ABC' і 'DEF' ?
додано Автор ruakh, джерело
Так що б ви хотіли показати, якщо тариф № 3333 також позначено як MNO?
додано Автор ruakh, джерело
Так, але немає нічого, що говорить про те, що певну кількість тарифів позначено одним або декількома кодами, немає реальних зв'язків. Я думаю, поточний запит має сенс, хоча.
додано Автор tedski, джерело
Так, це правильно
додано Автор tedski, джерело

2 Відповіді

Спробуйте ці магії:

SELECT     Code, COUNT(*) AS Count
FROM         (SELECT     dbo.TariffCode.Tariff, dbo.TariffCode.Code
                   FROM          dbo.TariffCode LEFT OUTER JOIN
                                              (SELECT     TariffCode_2.Tariff, TariffCode_2.Code
                                                FROM          dbo.TariffCode AS TariffCode_2 INNER JOIN
                                                                           (SELECT     Tariff, Code
                                                                             FROM          dbo.TariffCode AS TariffCode_1
                                                                             WHERE      (Tariff = '1111')) AS TariffsWithSharedCodes ON TariffCode_2.Code = TariffsWithSharedCodes.Code AND 
                                                                       TariffCode_2.Tariff <> '1111') AS MutualCodes ON dbo.TariffCode.Tariff = MutualCodes.Tariff AND 
                                          dbo.TariffCode.Code = MutualCodes.Code
                   WHERE      (MutualCodes.Code IS NULL) AND (dbo.TariffCode.Tariff <> '1111')) AS MissingCodes
GROUP BY Code
ORDER BY Count DESC, Code

Це T-SQL, вибачте, але ви отримаєте ідею

0
додано

Сподіваюся, що сценарій нижче допоможе вам. Він отримає всі можливі тарифи не тільки за «1111»:

with temp as (
  select tariffno, tariffno2, codeid 
  from (
    select distinct c1.tariffno, c2.tariffno as tariffno2, c2.codeid
    from tariffcode c1
    join tariffcode c2 on c1.tariffno != c2.tariffno and c1.codeid != c2.codeid 
  ) c1 
  where 
    not exists (select 1 from tariffcode where tariffno = c1.tariffno and codeid = c1.codeid)
)
select tariffno, codeid, count(*) as cnt from temp group by tariffno, codeid;
0
додано