代码之家 › 专栏 › 技术社区 › JosephDoggie

TSQL—获取所有表的计数的快速方法

ssms-17 sql-server-2014 count tsql

JosephDoggie · 技术社区 · 7 年前

If数据库 d1 有桌子 T1,T2,T3,T4 一切都在现场 "Date1" .

获取所有表中日期早于3天前的所有记录计数的最佳方法是什么?

我知道可以进行联合,我假设没有漂亮的语法可以省略所有的表[像C++中的“父”对象]。

这里,best可能意味着更高效,或者只是T-SQL中令人愉悦的语法。

这是针对SSMS 17.7的。Microsoft SQL Server 2014(SP2)

3 回复 | 直到 7 年前

Zohar Peled 7 年前

SELECT COUNT(*)
FROM
(
     SELECT Date1
     FROM T1
     UNION ALL
     SELECT Date1
     FROM T2
     SELECT Date1
     FROM T3
     SELECT Date1
     FROM T4
) As t
WHERE Date1 <= DATEADD(DAY, -3, GETDATE())

如果您事先不知道表名,可以使用 information_schema.columns

Lukas Eder 7 年前

你对父对象感兴趣,那就是视图。您可以将其用于各种查询。或者,如果需要,可以添加更多列:

CREATE VIEW parent AS
SELECT Date1 FROM t1 UNION ALL
SELECT Date1 FROM t2 UNION ALL
SELECT Date1 FROM t3 UNION ALL
SELECT Date1 FROM t4;

现在,你可以用你想要的方式来查询

SELECT COUNT(*) FROM parent WHERE Date1 <= DATEADD(DAY, -3, GETDATE())

LukStorms 7 年前

没有工会?

自从 COUNT 没有 GROUP BY 返回1个值,为什么不使用 CROSS JOIN

SELECT 
 t1.Cnt AS [T1], 
 t2.Cnt AS [T2], 
 t3.Cnt AS [T3],
 t4.Cnt AS [T4],
 (t1.Cnt + t2.Cnt + t3.Cnt + t4.Cnt) AS [T1234]
FROM 
(SELECT COUNT(*) AS Cnt FROM T1 WHERE [Date1] < CAST(GetDate()-3 AS DATE)) AS t1
CROSS JOIN
(SELECT COUNT(*) AS Cnt FROM T2 WHERE [Date1] < CAST(GetDate()-3 AS DATE)) AS t2
CROSS JOIN
(SELECT COUNT(*) AS Cnt FROM T3 WHERE [Date1] < CAST(GetDate()-3 AS DATE)) AS t3
CROSS JOIN
(SELECT COUNT(*) AS Cnt FROM T4 WHERE [Date1] < CAST(GetDate()-3 AS DATE)) AS t4

或者 CROSS APPLY

SELECT 
 t1.Cnt AS [T1], 
 t2.Cnt AS [T2], 
 t3.Cnt AS [T3],
 t4.Cnt AS [T4],
 (t1.Cnt + t2.Cnt + t3.Cnt + t4.Cnt) AS [T1234]
FROM (SELECT CAST(GetDate()-3 AS DATE) as Dt) d
CROSS APPLY (SELECT COUNT(*) AS Cnt FROM T1 WHERE [Date1] < d.Dt) AS t1
CROSS APPLY (SELECT COUNT(*) AS Cnt FROM T2 WHERE [Date1] < d.Dt) AS t2
CROSS APPLY (SELECT COUNT(*) AS Cnt FROM T3 WHERE [Date1] < d.Dt) AS t3
CROSS APPLY (SELECT COUNT(*) AS Cnt FROM T4 WHERE [Date1] < d.Dt) AS t4

declare @T1 table (id int primary key identity(1,1), [Date1] date);
declare @T2 table (id int primary key identity(1,1), [Date1] date);
declare @T3 table (id int primary key identity(1,1), [Date1] date);
declare @T4 table (id int primary key identity(1,1), [Date1] date);

insert into @T1 ([Date1]) values (getdate()-6),(getdate()-5),(getdate()-4),(getdate()-3),(getdate()-2),(getdate()-1),(getdate()-0);
insert into @T2 ([Date1]) select top 6 [Date1] from @T1 order by [Date1] desc;
insert into @T3 ([Date1]) select top 5 [Date1] from @T1 order by [Date1] desc;
insert into @T4 ([Date1]) select top 4 [Date1] from @T1 order by [Date1] desc;

SELECT 
 t1.Cnt AS [T1], 
 t2.Cnt AS [T2], 
 t3.Cnt AS [T3],
 t4.Cnt AS [T4],
 (t1.Cnt + t2.Cnt + t3.Cnt + t4.Cnt) AS [T1234]
FROM 
(SELECT COUNT(*) AS Cnt FROM @T1 WHERE [Date1] < CAST(GetDate()-3 AS DATE)) AS t1
CROSS JOIN
(SELECT COUNT(*) AS Cnt FROM @T2 WHERE [Date1] < CAST(GetDate()-3 AS DATE)) AS t2
CROSS JOIN
(SELECT COUNT(*) AS Cnt FROM @T3 WHERE [Date1] < CAST(GetDate()-3 AS DATE)) AS t3
CROSS JOIN
(SELECT COUNT(*) AS Cnt FROM @T4 WHERE [Date1] < CAST(GetDate()-3 AS DATE)) AS t4

返回:

T1  T2  T3  T4  T1234
3   2   1   0   6

user153383 7 年前

WITH CteDate( Date1 )
AS ( SELECT Date1 FROM t1 UNION ALL
     SELECT Date1 FROM t2 UNION ALL
     SELECT Date1 FROM t3 UNION ALL
     SELECT Date1 FROM t4
    )
SELECT COUNT(*) FROM CteDate WHERE Date1 <= DATEADD(DAY, -3, GETDATE())

它适用于所有大于或等于2005的SQL Server。