时间戳

首页 > 解决方案 > 需要将来自多个表的计数与通用列名组合起来

sql - 需要将来自多个表的计数与通用列名组合起来

问题描述

我有 2 个表，我试图根据日期从中获取计数。换句话说，表中从 2018 年 1 月 1 日开始的所有记录都将被计算并返回到“计数”列。

我的查询有效，但我最终得到的是 2 个日期，每个表中有 1 个。我想要结束的是 1 个日期，其中两个表的总计数。

 SELECT CONVERT(varchar(10),DWSTOREDATETIME, 101) AS DWSTOREDATETIME
, COUNT(*) AS Count
  FROM [dwdata].[dbo].SO00
  WHERE DWSTOREDATETIME > '2018-01-01 00:00:00' AND DWSTOREDATETIME < '2019-01-01 00:00:00'
  GROUP BY CONVERT(varchar(10),DWSTOREDATETIME, 101)

  SELECT CONVERT(varchar(10),DWSTOREDATETIME, 101) AS DWSTOREDATETIME
, COUNT(*) AS Count
  FROM [dwdata].[dbo].NO00
  WHERE DWSTOREDATETIME > '2018-01-01 00:00:00' AND DWSTOREDATETIME < '2019-01-01 00:00:00'
  GROUP BY CONVERT(varchar(10),DWSTOREDATETIME, 101)

因此，而不是显示 1/1/2018 Count:1 的记录和另一条显示 1/1/2018 Count:2 的记录

我会有一条记录显示 2018 年 1 月 1 日计数：3

标签： sqlcount

解决方案

您必须获得2 个子查询SUM的相应日期：UNION

SELECT t.DWSTOREDATETIME, SUM(t.Count) AS Count
FROM(
  SELECT CONVERT(varchar(10),DWSTOREDATETIME, 101) AS DWSTOREDATETIME
  , COUNT(*) AS Count
  FROM [dwdata].[dbo].SO00
  WHERE DWSTOREDATETIME > '2018-01-01 00:00:00' AND DWSTOREDATETIME < '2019-01-01 00:00:00'
  GROUP BY CONVERT(varchar(10),DWSTOREDATETIME, 101)
  UNION ALL
  SELECT CONVERT(varchar(10),DWSTOREDATETIME, 101) AS DWSTOREDATETIME
  , COUNT(*) AS Count
  FROM [dwdata].[dbo].NO00
  WHERE DWSTOREDATETIME > '2018-01-01 00:00:00' AND DWSTOREDATETIME < '2019-01-01 00:00:00'
  GROUP BY CONVERT(varchar(10),DWSTOREDATETIME, 101)
) AS t
GROUP BY t.DWSTOREDATETIME

推荐阅读