如何在多个表中获取日期列的最大日期值

Question

例如，在数据库中有两个表。 例如： 表T：

declare @t table (name varchar(20),DOB date)
Insert into @t (name,DOB) values ('Mohan','2001-07-19')
Insert into @t (name,DOB) values ('Minu','1998-06-19')

表格：TT

declare @tt table (name varchar(20),DOB date)
Insert into @tt (name,DOB) values ('Raju','2010-07-19')
Insert into @tt (name,DOB) values ('Rani','2001-06-19')

现在我有一个查询，可以根据日期类型筛选获取多个表的表名和列名。

SELECT   C.TABLE_SCHEMA, c.TABLE_NAME,c.COLUMN_NAME
FROM     INFORMATION_SCHEMA.COLUMNS c 
         JOIN sys.objects o ON o.name = c.TABLE_NAME   
WHERE    o.type = 'U' AND C.DATA_TYPE = 'Datetime'

输出：

T_Schema    T_name T_column
  dbo         T      DOB   
  dbo         TT     DOB

但是我该如何获取两个表的最大日期呢？如下所示的输出：

  T_Schema  T_name T_column  Max_dt
  dbo         T      DOB     2001-07-19
  dbo         TT     DOB     2010-07-19

请为我提供最佳实现方式。

- mohan111

---

@RyanWilson 问题在于如何动态地实现，即查询所有表。 - DavidG

所以你想要在数据库中为每个日期时间列返回一行，该行提供模式、表名、列名和最大日期？基本上你已经得到了99%的查询结果，你只需要将其变成动态SQL，就可以完成了。 - Sean Lange

你可以动态构建查询字符串并执行它。我最好为此创建一个存储过程。https://www.mssqltips.com/sqlservertip/1160/execute-dynamic-sql-commands-in-sql-server/ - ZorgoZ

@ZorgoZ，为什么使用存储过程是更好的方法？ - Sean Lange

@mohan111，你最终会使用游标来构建一个相当复杂的字符串。如果你不想总是从代码中发出t-sql脚本，存储过程为这样的任务提供了一个清晰的层。 - ZorgoZ

Answer 1

这里有另一种不使用光标的选项。我觉得从性能角度来看，它并不会更好，因为你仍然需要为每一行子查询。但我真的很讨厌游标。我还使用了系统表而不是信息模式视图，因为后者有时可能有些奇怪。https://sqlblog.org/2011/11/03/the-case-against-information_schema-views

declare @SQL nvarchar(max) = N''

select @SQL = @SQL + 
    N'select SCHEMA_NAME = ''' + QUOTENAME(s.name) + ''', TABLE_NAME = ''' 
        + QUOTENAME(o.name) + ''', COLUMN_NAME = '''
        + QUOTENAME(c.name) + ''', MaxDate = '
        + '(select MAX(' + QUOTENAME(c.name) + ') from  ' + QUOTENAME(s.name) + '.' + QUOTENAME(o.name) + ') UNION ALL '
from sys.columns c
join sys.systypes st on st.type = c.system_type_id
join sys.objects o on o.object_id = c.object_id and o.type = 'U'
join sys.schemas s on s.schema_id = o.schema_id
where st.name = 'datetime'
order by s.name
    , o.name
    , c.name

set @SQL = left(@SQL, len(@SQL) - 10) --removes final UNION ALL

select @SQL

--uncomment below when you are satisfied the dynamic sql is correct
--exec sp_executesql @SQL

- Sean Lange

---

唉...让我修复大小写。我通常比那更好。特别是因为我们最大的系统使用区分大小写的环境。尽管内置函数名称即使在区分大小写的排序中也不区分大小写。 - Sean Lange

从内存中使用Latin1_BIN？我曾经非常喜欢将其用作基础排序规则，真的可以让你保持警觉。 - DavidG

@DavidG，这是我们ERP使用的排序规则。但它仍然不关心内置函数名称的大小写敏感性。但确实让事情变得有趣。 - Sean Lange

Answer 2

以下是一些可通过游标实现的动态 SQL 语句，应该能满足您的需求。

如果您有很多表格或者首次测试，请注意不要使用此方法。通常来说，游标的性能并不好。您可以在系统数据库（如 master）上运行此代码，因为它包含较少的值，以便了解其工作原理。

create table #MaxDate (tname varchar(256), cname varchar(256), mdate datetime)

declare cur cursor local fast_forward
for 
    SELECT   C.TABLE_SCHEMA, c.TABLE_NAME,c.COLUMN_NAME
    FROM     INFORMATION_SCHEMA.COLUMNS c 
             JOIN sys.objects o ON o.name = c.TABLE_NAME   
    WHERE    o.type = 'U' AND C.DATA_TYPE = 'Datetime'

declare @schema varchar(64), @table varchar(256), @column varchar(256)
declare @sql varchar(max)

open cur
fetch next from cur into @schema, @table, @column 
while @@FETCH_STATUS = 0

begin
    set @sql = 'select ''' + @table + '''' + ',''' + '' + @column + '''' + ',' +  'max(' + @column + ') from ' + @schema + '.' + @table
    print @sql

    insert into #MaxDate
    exec (@sql)
    fetch next from cur into @schema, @table, @column
end

close cur
deallocate cur

select * from #MaxDate
drop table #MaxDate

- S3S

Answer 3

以下是使用游标、动态SQL和临时表的答案：

DECLARE table_cursor CURSOR LOCAL FAST_FORWARD FOR
SELECT
    C.TABLE_SCHEMA,
    c.TABLE_NAME,
    c.COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS c
JOIN sys.objects o
    ON o.name = c.TABLE_NAME
WHERE o.type = 'U'
AND C.DATA_TYPE = 'Datetime'

DECLARE @schema SYSNAME
DECLARE @table SYSNAME
DECLARE @column SYSNAME
DECLARE @sql NVARCHAR(1000)

CREATE TABLE #Data (SchemaName SYSNAME, TableName SYSNAME, ColumnName SYSNAME, MaxDate DATETIME)

OPEN table_cursor
FETCH NEXT FROM table_cursor INTO @schema, @table, @column

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql = 'INSERT INTO #Data (SchemaName, TableName, ColumnName, MaxDate) SELECT '''+@schema+''', '''+@table+''', '''+@column+''', MAX(['+@column+']) FROM ['+@schema+'].['+@table+']'
    EXEC sp_executesql @sql

    FETCH NEXT FROM table_cursor INTO @schema, @table, @column
END

CLOSE table_cursor
DEALLOCATE table_cursor

SELECT * FROM #Data
DROP TABLE #Data

- DavidG

---

@scsimon 不知道我从哪里拖出了sysname，可能是很久以前被压抑的记忆！ - DavidG

@mohan111 有什么区别吗？While循环，游标，动态构建的联合查询 - 它们都是一样的，你必须将读取多个表的结果合并在一起。 - Aaron Bertrand

@AaronBertrand 是的...这就是为什么在我的回答中我说它不会表现得更好。无论如何，这种情况都必须查询所有这些表。 - Sean Lange

@SeanLange 更多的我在瞎猜，如果你能怎么做。我没有预料到 union 方法。 - S3S

谢谢您非常棒的讨论，我将在我的需求中实现这段代码。@DavidG - mohan111

Answer 4

使用SQL构建SQL语句

DECLARE @SQL as nvarchar(max) ='';
select @SQL = @SQL + 'SELECT ' + Column_Name + ' adate, ''' + Column_Name + ''' colname, ''' + Table_name + ''' tabname  FROM ' +  Table_name + ' UNION ' FROM   INFORMATION_SCHEMA.COLUMNS where data_type like '%date%'
select @SQL = 'SELECT TOP 100 * FROM (' + LEFT(@SQL, LEN(@SQL) -6) + ') IQ  WHERE IQ.adate IS NOT null ORDER BY IQ.adate DESC';

 --cut n paste the sql below, see what it does for you
select @SQL

Mark II - 为您执行SQL，并处理名称中的空格

DECLARE @SQL as nvarchar(max) ='';
select @SQL = @SQL + 'SELECT [' + Column_Name + '] adate, ''' + Column_Name + ''' colname, ''' + Table_name + ''' tabname  FROM [' +  Table_name + '] UNION ' FROM   INFORMATION_SCHEMA.COLUMNS where data_type like '%date%'
select @SQL = 'SELECT TOP 100 * FROM (' + LEFT(@SQL, LEN(@SQL) -6) + ') IQ  WHERE IQ.adate IS NOT null ORDER BY IQ.adate DESC';

select @SQL;
EXEC sp_executesql @sql;

- Cato