mysql—包含子查询的两个表的计数并集

u91tlkcl 于 2021-06-18 发布在 Mysql

关注(0)|答案(1)|浏览(309)

我有一个有效的问题。它有count和subquery。

SELECT 
  COUNT(*) AS total 
FROM
  (SELECT 
    COUNT(aset) 
  FROM
    `public_1` 
  WHERE `public_1`.`aset` NOT IN 
    (SELECT 
      asset_code 
    FROM
      application_detail 
    WHERE application_id = 6) 
    AND org_id = 7 
    AND status_id = 8 
  GROUP BY aset) t

现在我需要合并不同的表，并从两个表中得到总数。此代码可能获取计数记录，但值不正确。

SELECT 
  COUNT(*) AS total 
FROM
  (SELECT 
    COUNT(aset) 
  FROM
    `public_1` 
  WHERE `public_1`.`aset` NOT IN 
    (SELECT 
      asset_code 
    FROM
      application_detail 
    WHERE application_id = 6) 
    AND org_id = 7 
    AND status_id = 8 
  UNION
  SELECT 
    COUNT(aset) 
  FROM
    `public_2` 
  WHERE `public_2`.`aset` NOT IN 
    (SELECT 
      asset_code 
    FROM
      application_detail 
    WHERE application_id = 6) 
    AND org_id = 7 
    AND status_id = 8 
  GROUP BY aset) z

请帮我把问题弄对。提前谢谢

mysql union notin

来源：https://stackoverflow.com/questions/53676491/count-union-of-two-table-that-has-subquery

1条答案

按热度按时间

sg2wtvxw1#

使用 SELECT COUNT(DISTINCT aset) 把你的数字加起来。

SELECT t1.total + t2.total AS total
FROM (
    SELECT COUNT(DISTINCT aset) AS total
    FROM `public_1` 
    WHERE `public_1`.`aset` NOT IN 
        (SELECT 
          asset_code 
        FROM
          application_detail 
        WHERE application_id = 6) 
        AND org_id = 7 
        AND status_id = 8) AS t1
CROSS JOIN (
    SELECT COUNT(DISTINCT aset) AS total
    FROM `public_2` 
    WHERE `public_2`.`aset` NOT IN 
        (SELECT 
          asset_code 
        FROM
          application_detail 
        WHERE application_id = 6) 
        AND org_id = 7 
        AND status_id = 8) AS t2

赞(0）回复(0）举报 2021-06-18

我来回答

mysql—包含子查询的两个表的计数并集

1条答案

相关问题

热门标签

最新问答