SQL: Calculating the Max date across multiple tabl

问题:

I am trying to find the max date across multiple columns (in multiple tables). I have an SQL query that I think is almost workable, but am having a hard time debugging it. The problem is that it never returns anything. Any ideas on what I'm doing incorrectly? I don't get any errors, just an empty row "maxdate."

Here's my query:

SELECT
(
    SELECT MAX(dates) FROM
    (
        SELECT dates = t1.UpdateDate
        UNION ALL
        SELECT dates = t2.UpdateDate
        UNION ALL
        SELECT dates = t3.UpdateDate
        UNION ALL
        SELECT dates = t4.UpdateDate
    ) as dateAcrossColumns
) as maxdate

FROM table1 as t1

join table2 as t2 on t1.ID = t2.ID
join table3 as t3 on t1.ID = t3.ID
join table4 as t4 on t1.ID = t4.ID
join table5 as t5 on t1.Status = t5.Status

WHERE t1.id = @param and t5.status <> 3

回答1:

First join you join to table 2 using table 1's ID and table 3's ID. I presume you mean table 1's ID and table 2's ID?

join table2 as t2 on t1.ID = t3.ID

Should be:

join table2 as t2 on t1.ID = t2.ID

Now that this has been changed;

I tried your query and it works fine, are you sure that your joins are bringing back any rows? To test it replace your SELECT statement with SELECT *, if you get no rows back then they are being filtered out somewhere in your joins / WHERE.

回答2:

If it's possible for any of the tables to not have any rows for the given @param, the (inner) joins will filter out all of the other tables.

Try unioning the dates and get the max of the unions

WITH allDates AS (
    SELECT UpdateDate
    FROM   Table1
    WHERE  ID = @param
    UNION
    SELECT UpdateDate
    FROM   Table2
    WHERE  ID = @param
    UNION
    SELECT UpdateDate
    FROM   Table3
    WHERE  ID = @param
    UNION
    SELECT UpdateDate
    FROM   Table4
    WHERE  ID = @param
    UNION
    SELECT t5.UpdateDate
    FROM   Table5 AS t5
    JOIN   Table1 AS t1
        ON t5.Status = t1.Status
    WHERE  t1.ID = @param
)
SELECT MAX( UpdateDate ) AS MaxDate
FROM   allDates

回答3:

Cant think of a simpler way of doing this :)

SELECT MAX(Dates) AS RequiredDate
FROM (
    SELECT  t1.UpdateDate AS DateOne
           ,t2.UpdateDate AS DateTwo
           ,t3.UpdateDate AS DateThree
           ,t4.UpdateDate AS DateFour
    FROM table1 as t1
    join table2 as t2 on t1.ID = t2.ID
    join table3 as t3 on t1.ID = t3.ID
    join table4 as t4 on t1.ID = t4.ID
    join table5 as t5 on t1.[Status] = t5.[Status]
    WHERE t1.id = @param and t5.[Status] <> 3
    )t
  UNPIVOT (Dates FOR DateCols 
            IN (DateOne, DateTwo,DateThree,DateFour))up

回答4:

I was trying to solve a similar problem to find the last modified date across a set of joined tables in MySQL. The following worked for me using a combination of GREATEST and MAX:

SELECT GREATEST(MAX(t1.modified), MAX(t2.modified), MAX(t3.modified),
    MAX(t4.modified)) AS lastmodified
FROM `table1` AS t1
LEFT JOIN `table2` AS t2 ON t1.id = t2.t1_id
LEFT JOIN `table3` AS t3 ON t2.id = t3.t2_id
LEFT JOIN `table4` AS t4 ON t3.id = t4.t3_id
WHERE t1.id = @param