SQL：计算多个表之间的最大日期 [英] SQL: Calculating the Max date across multiple tables

问题描述

我想在多个列（多个表格）中找到最大日期。我有一个SQL查询，我认为几乎可行，但我有一个很难调试它。问题是它从来没有返回任何东西。任何想法，我做错了什么？我没有得到任何错误，只是一个空行maxdate。

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."

这是我的查询：

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 ID和表3的ID。我假设你的意思是表1的ID和表2的ID？

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

应为：

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

改变了;

我试过你的查询，它工作正常，你确定你的联接带回任何行？要测试它用 SELECT * 替换你的 SELECT 语句，如果你没有返回任何行，在您的连接/ WHERE 中。

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.

这篇关于SQL：计算多个表之间的最大日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！