sql—创建一个填充了其他表的记录计数的表

tyg4sfes 于 2021-07-29 发布在 Java

关注(0)|答案(2)|浏览(241)

我想验证每天有2个（或更多）表具有相同的计数。我认为对每个表执行select计数并在temp表中以它们自己的命名列中的值形式报告它们将是有效的，即使只是在结果窗口中观察数字也是如此

CREATE TABLE #sync_count (mst_cnt AS varchar(5), info_cnt AS varchar(5));
    INSERT INTO #sync_count (mst_cnt, info_cnt)
    VALUES (
    SELECT Sum(SRV_MST.SRV_MACID) AS mst_cnt FROM SRV_MST,
    SELECT Sum(SRV_INFO.SRV_MACID) AS info_cnt FROM SRV_INFO,
    ))

DELETE #sync_count

期望输出

SRV_MST | SRV_INFO
===================
  22    | 22
===================

不知道我到底哪里出错了。有什么建议吗？非常感谢

sql sql-server count select

来源：https://stackoverflow.com/questions/62958838/creating-a-table-populated-with-record-counts-from-other-tables

2条答案

按热度按时间

omqzjyyz1#

子查询周围需要括号：

CREATE TABLE #sync_count (mst_cnt varchar(5), info_cnt varchar(5));
----------------------------------^ as is not used in table declarations.

INSERT INTO #sync_count (mst_cnt, info_cnt)
    VALUES ( (SELECT Sum(SRV_MST.SRV_MACID) AS mst_cnt FROM SRV_MST),
             (SELECT Sum(SRV_INFO.SRV_MACID) AS info_cnt FROM SRV_INFO)
           );

在最后一个paren之前还有一个逗号。
我强烈建议您将计数定义为数字而不是字符串！

CREATE TABLE #sync_count (mst_cnt int, info_cnt int);

实际上，您可以省去表声明，只使用：

SELECT (SELECT Sum(SRV_MST.SRV_MACID) FROM SRV_MST) AS mst_cnt,
       (SELECT Sum(SRV_INFO.SRV_MACID) FROM SRV_INFO) AS info_cnt 
INTO #sync_count ;

赞(0）回复(0）举报 2021-07-29

l0oc07j22#

你可以这样写：

INSERT INTO #sync_count (mst_cnt, info_cnt)
SELECT
    (SELECT Sum(SRV_MST.SRV_MACID) FROM SRV_MST),
    (SELECT Sum(SRV_INFO.SRV_MACID) FROM SRV_INFO)

赞(0）回复(0）举报 2021-07-29

我来回答

sql—创建一个填充了其他表的记录计数的表

2条答案

相关问题

热门标签

最新问答