SQL SELECT: объединение и группировка данных между тремя таблицами с использованием подзапросов

Примерно так, если я правильно вас понял

SELECT
    MAX(CREATIONTIME),
    A.AID
FROM
    A
INNER JOIN
    B
    ON 
    A.AID = B.AID
INNER JOIN
    C
    ON 
    B.CID = C.CID
WHERE
    A.STATUS = 'OK'
GROUP BY
    A.AID

ИЗМЕНИТЬ:

Теперь я проверил следующее в SQL Server (я бы ожидал того же результата в Oracle), и он возвращает CID для записи C с максимальным CREATIONTIME, где STATUS для связанной записи в A id 'OK'.

SELECT C.CID
FROM 
C C
INNER JOIN
B B
ON 
C.CID = B.CID
INNER JOIN
(
    SELECT
        MAX(C.CREATIONTIME) CREATIONTIME,
        A.AID
    FROM
        A A
    INNER JOIN
        B B
        ON 
        A.AID = B.AID
    INNER JOIN
        C C
        ON 
        B.CID = C.CID
    WHERE
        A.STATUS = 'OK'
    GROUP BY
        A.AID
) ABC
ON B.AID = ABC.AID
AND C.CREATIONTIME = ABC.CREATIONTIME

Продемонстрировано со следующим T-SQL

DECLARE @A TABLE(AID INT IDENTITY(1,1), STATUS VARCHAR(10))
DECLARE @B TABLE(BID INT IDENTITY(1,1), AID INT, CID INT)
DECLARE @C TABLE(CID INT IDENTITY(1,1), CREATIONTIME DATETIME)

INSERT INTO @A VALUES ('OK')
INSERT INTO @A VALUES ('OK')
INSERT INTO @A VALUES ('NOT OK')
INSERT INTO @A VALUES ('OK')
INSERT INTO @A VALUES ('NOT OK')

INSERT INTO @C VALUES ('10 MAR 2008')
INSERT INTO @C VALUES ('13 MAR 2008')
INSERT INTO @C VALUES ('15 MAR 2008')
INSERT INTO @C VALUES ('17 MAR 2008')
INSERT INTO @C VALUES ('21 MAR 2008')

INSERT INTO @B VALUES (1,1)
INSERT INTO @B VALUES (1,2)
INSERT INTO @B VALUES (1,3)
INSERT INTO @B VALUES (2,2)
INSERT INTO @B VALUES (2,3)
INSERT INTO @B VALUES (2,4)
INSERT INTO @B VALUES (3,3)
INSERT INTO @B VALUES (3,4)
INSERT INTO @B VALUES (3,5)
INSERT INTO @B VALUES (4,5)
INSERT INTO @B VALUES (4,1)
INSERT INTO @B VALUES (4,2)


SELECT C.CID
FROM 
@C C
INNER JOIN
@B B
ON 
C.CID = B.CID
INNER JOIN
(
SELECT
    MAX(C.CREATIONTIME) CREATIONTIME,
    A.AID
FROM
    @A A
INNER JOIN
    @B B
    ON 
    A.AID = B.AID
INNER JOIN
    @C C
    ON 
    B.CID = C.CID
WHERE
    A.STATUS = 'OK'
GROUP BY
    A.AID
) ABC
ON B.AID = ABC.AID
AND C.CREATIONTIME = ABC.CREATIONTIME

Результаты в следующем

CID
-----------
3
4
5

РЕДАКТИРОВАТЬ 2:

В ответ на ваш комментарий о том, что каждое из утверждений дает разные результаты, я провел здесь несколько разных ответов через SQL Server 2005, используя мои тестовые данные выше (я ценю, что вы используете Oracle). Вот результаты

--Expected results for CIDs would be

--CID
-----------
--3
--4
--5

--As indicated in the comments next to the insert statements

DECLARE @A TABLE(AID INT IDENTITY(1,1), STATUS VARCHAR(10))
DECLARE @B TABLE(BID INT IDENTITY(1,1), AID INT, CID INT)
DECLARE @C TABLE(CID INT IDENTITY(1,1), CREATIONTIME DATETIME)

INSERT INTO @A VALUES ('OK') -- AID 1
INSERT INTO @A VALUES ('OK') -- AID 2
INSERT INTO @A VALUES ('NOT OK')
INSERT INTO @A VALUES ('OK') -- AID 4
INSERT INTO @A VALUES ('NOT OK')

INSERT INTO @C VALUES ('10 MAR 2008')
INSERT INTO @C VALUES ('13 MAR 2008')
INSERT INTO @C VALUES ('15 MAR 2008')
INSERT INTO @C VALUES ('17 MAR 2008')
INSERT INTO @C VALUES ('21 MAR 2008')

INSERT INTO @B VALUES (1,1)
INSERT INTO @B VALUES (1,2)
INSERT INTO @B VALUES (1,3) -- Will be CID 3 For AID 1
INSERT INTO @B VALUES (2,2)
INSERT INTO @B VALUES (2,3)
INSERT INTO @B VALUES (2,4) -- Will be CID 4 For AID 2
INSERT INTO @B VALUES (3,3)
INSERT INTO @B VALUES (3,4)
INSERT INTO @B VALUES (3,5)
INSERT INTO @B VALUES (4,5) -- Will be CID 5 FOR AID 4
INSERT INTO @B VALUES (4,1)
INSERT INTO @B VALUES (4,2)

-- Russ Cam
SELECT C.CID, ABC.CREATIONTIME
FROM 
@C C
INNER JOIN
@B B
ON 
C.CID = B.CID
INNER JOIN
(
SELECT
    MAX(C.CREATIONTIME) CREATIONTIME,
    A.AID
FROM
    @A A
INNER JOIN
    @B B
    ON 
    A.AID = B.AID
INNER JOIN
    @C C
    ON 
    B.CID = C.CID
WHERE
    A.STATUS = 'OK'
GROUP BY
    A.AID
) ABC
ON B.AID = ABC.AID
AND C.CREATIONTIME = ABC.CREATIONTIME

-- Tyrannosaurs
select   A.AID,  
         max(AggC.CREATIONTIME)  
from    @A A,  
         @B B,  
         (  select  C.CID,  
             max(C.CREATIONTIME) CREATIONTIME  
            from @C C  
            group by CID
          ) AggC  
where    A.AID = B.AID  
and    B.CID = AggC.CID  
and    A.Status = 'OK'  
group by A.AID

-- jmucchiello
SELECT c.cid, max(c.creationtime)
FROM @B b, @C c
WHERE b.cid = c.cid
 AND b.aid IN (SELECT a.aid FROM @A a WHERE status = 'OK')
GROUP BY c.cid

-- hainstech
SELECT agg.aid, agg.cid
FROM (
    SELECT a.aid
        ,c.cid
        ,max(c.creationtime) as maxcCreationTime
    FROM @C c INNER JOIN @B b ON b.cid = c.cid
        INNER JOIN @A a on a.aid = b.aid
    WHERE a.status = 'OK'
    GROUP BY a.aid, c.cid
) as agg

--chris
SELECT A.AID, C.CID, C.CREATIONTIME
FROM @A A, @B B, @C C
WHERE A.STATUS = 'OK'
AND A.AID = B.AID
AND B.CID = C.CID
AND C.CREATIONTIME = 
(SELECT MAX(C2.CREATIONTIME) 
FROM @C C2, @B B2 
WHERE B2.AID = A.AID
AND C2.CID = B2.CID);

Результаты приведены ниже

--Russ Cam - Correct CIDs (I have added in the CREATIONTIME for reference)
CID         CREATIONTIME
----------- -----------------------
3           2008-03-15 00:00:00.000
4           2008-03-17 00:00:00.000
5           2008-03-21 00:00:00.000

--Tyrannosaurs - No CIDs in the resultset
AID         
----------- -----------------------
1           2008-03-15 00:00:00.000
2           2008-03-17 00:00:00.000
4           2008-03-21 00:00:00.000


--jmucchiello - Incorrect CIDs in the resultset
cid         
----------- -----------------------
1           2008-03-10 00:00:00.000
2           2008-03-13 00:00:00.000
3           2008-03-15 00:00:00.000
4           2008-03-17 00:00:00.000
5           2008-03-21 00:00:00.000

--hainstech - Too many CIDs in the resultset, which CID has the MAX(CREATIONTIME) for each AID?
aid         cid
----------- -----------
1           1
1           2
1           3
2           2
2           3
2           4
4           1
4           2
4           5

--chris - Correct CIDs, it is the same SQL as mine
AID         CID         CREATIONTIME
----------- ----------- -----------------------
1           3           2008-03-15 00:00:00.000
2           4           2008-03-17 00:00:00.000
4           5           2008-03-21 00:00:00.000

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

Выберите поле, которое вы ищете, используя объединение всех трех таблиц, а затем ограничьте результаты теми, где CREATIONDATE является самым последним.

SELECT A.AID, C.CID, C.CREATIONTIME
FROM A A, B B, C C
WHERE A.STATUS = 'OK'
AND A.AID = B.AID
AND B.CID = C.CID
AND C.CREATIONTIME = 
(SELECT MAX(C2.CREATIONTIME) 
FROM C C2, B B2 
WHERE B2.AID = A.AID
AND C2.CID = B2.CID);

РЕДАКТИРОВАТЬ: мой предыдущий ответ был ерундой. Теперь это полная переработка

На самом деле это проблема, которая беспокоила меня на протяжении всей моей жизни с SQL. Решение, которое я вам дам, чертовски запутано, но оно работает, и я был бы признателен, если бы кто-нибудь сказал: «Да, это чертовски запутано, но это единственный способ сделать это», или сказал: «Нет, сделайте это... ".

Я думаю, беспокойство возникает из-за совмещения двух свиданий. То, как это происходит здесь, не проблема, поскольку они будут точно соответствовать (у них точно такие же корневые данные), но это все равно кажется неправильным...

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

1) Первый — вернуть набор результатов [AID], [наиболее раннее время создания], указывающий вам самое раннее время создания для каждого AID.

2) Затем вы можете использовать lastCreationTime, чтобы получить нужный CID.

Итак, для части (1) я бы лично создал представление, чтобы сделать это просто для того, чтобы все было аккуратно. Это позволяет вам протестировать эту часть и заставить ее работать, прежде чем объединять ее с другими вещами.

create view LatestCreationTimes
as
select b.AID,
       max(c.CreationTime) LatestCreationTime
from   TableB b,
       TableC c
where  b.CID = c.CID
group by b.AID

Обратите внимание, что в этот момент мы не учитывали статус.

Затем вам нужно присоединить его к TableA (чтобы получить статус), а также к TableB и TableC (чтобы получить CID). Вам нужно сделать все очевидные ссылки (AID, CID), а также соединить столбец LatestCreationTime в представлении со столбцом CreationTime в TableC. Не забудьте также присоединиться к представлению в AID, иначе, если две записи были созданы одновременно для разных записей A, у вас возникнут проблемы.

select A.AID,
       C.CID
from   TableA a,
       TableB b,
       TableC c,
       LatestCreationTimes lct
where  a.AID = b.AID
and    b.CID = c.CID
and    a.AID = lct.AID
and    c.CreationTime = lct.LatestCreationTime
and    a.STATUS = 'OK'

Я уверен, что это работает — я протестировал его, подправил данные, повторно протестировал, и он себя ведет. По крайней мере, он делает то, для чего, как мне кажется, предназначен.

Однако это не касается возможности двух идентичных CreationTimes в таблице C для одной и той же записи. Я предполагаю, что этого не должно происходить, однако, если вы не написали что-то, что абсолютно ограничивает это, это необходимо учитывать.

Для этого мне нужно сделать предположение о том, какой из них вы бы предпочли. В этом случае я хочу сказать, что если есть два совпадающих CID, вы бы предпочли более высокий (скорее всего, более современный).

select A.AID,
       max(C.CID) CID
from   TableA a,
       TableB b,
       TableC c,
       LatestCreationTimes lct
where  a.AID = b.AID
and    b.CID = c.CID
and    c.CreationTime = lct.LatestCreationTime
and    a.STATUS = 'OK'
group by A.AID

И это, я считаю, должно работать для вас. Если вы хотите, чтобы это был один запрос, а не представление, то:

select A.AID,
       max(C.CID) CID
from   TableA a,
       TableB b,
       TableC c,
       (select b.AID,
               max(c.CreationTime) LatestCreationTime
        from   TableB b,
               TableC c
        where  b.CID = c.CID
        group by b.AID) lct
where  a.AID = b.AID
and    b.CID = c.CID
and    c.CreationTime = lct.LatestCreationTime
and    a.STATUS = 'OK'
group by A.AID

(Я только что встроил представление в запрос, в остальном принцип точно такой же).

Нет необходимости в подзапросе, агрегация для определения последнего времени создания cid проста:

SELECT a.aid
    ,c.cid
    ,max(c.creationtime) as maxcCreationTime
FROM c INNER JOIN b ON b.cid = c.cid
    INNER JOIN a on a.aid = b.aid
WHERE a.status = 'OK'
GROUP BY a.aid, c.cid

Если вам действительно не нужно время создания в наборе строк, вы можете просто обернуть его в подзапрос и удалить из проекции:

SELECT agg.aid, agg.cid
FROM (
    SELECT a.aid
        ,c.cid
        ,max(c.creationtime) as maxcCreationTime
    FROM c INNER JOIN b ON b.cid = c.cid
        INNER JOIN a on a.aid = b.aid
    WHERE a.status = 'OK'
    GROUP BY a.aid, c.cid
) as agg

Кодирование на веб-странице, пожалуйста, извините за любые синтаксические ошибки. Кроме того, я парень mssql, поэтому я надеюсь, что в мире Oracle нет ничего другого для этого.

Обратите внимание, что предоставленная вами схема не обеспечивает уникальность CREATIONTIME для каждого cid. Если есть два значения cid, которые соответствуют данному вспомогательному значению с одинаковым временем создания, они оба будут выведены. Если вы полагаетесь на то, что пара cid,creationtime будет уникальной, вы должны принудительно применить ее декларативно с помощью ограничения.

Я что-то упускаю? Что не так с:

РЕДАКТИРОВАТЬ: Хорошо, я вижу, вы действительно хотите сгруппироваться с помощью.

SELECT c.cid FROM b, c,
    (SELECT b.aid as aid, max(c.creationtime) as creationtime
     FROM b, c
     WHERE b.cid = c.cid
       AND b.aid IN (SELECT a.aid FROM a WHERE status = 'OK')
     GROUP BY b.aid) as z
WHERE b.cid = c.cid
  AND z.aid = b.aid
  AND z.creationtime = c.creationtime