如何在mysql中获取两个不同查询结果计数的总和？

发布于 2024-12-25 10:23:15 字数 683 浏览 0 评论 0原文

我需要从类别+子类别+子子类别中获取文件总数

为此，我使用我的视图编写这种查询。

select ((select count(*) from view_category where 1=1)+ (select count(*) from view sub category where 1=1) + (select count(*) from view subsub category where 1=1)) as cnt

它的返回计数值。但我想知道是否有其他更好的方法可以获得相同的结果。

我尝试了这种方法，但它不起作用（How to SUM() multiple subquery MySQL 中的行？)

select sum(int_val) from((select count(*) from view_category where 1=1) as int_val union (select count(*) from view sub Category where 1=1) as int_val union（从视图子子类别中选择 count(*)，其中 1=1) as int_val ）。

原文

I need to take total count of files from category + sub category + subsub category

For that I write this kind of a query using my views.

select ((select count(*) from view_category where 1=1)+ (select count(*) from view sub category where 1=1) + (select count(*) from view subsub category where 1=1)) as cnt

Its returning count value. But I want to know any other better method is available to get the same result.

I tried this way but its not working (How to SUM() multiple subquery rows in MySQL?)

select sum(int_val) from((select count(*) from view_category where 1=1) as int_val union (select count(*) from view sub category where 1=1) as int_val union (select count(*) from view subsub category where 1=1) as int_val ).

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

丑疤怪 2025-01-01 10:23:15

您不需要进行联合，并且可以将每个查询作为自己的别名...只要每个查询仅返回一行，您就可以做各种疯狂的事情。通过忽略任何“连接”条件，您会得到笛卡尔结果，但是 1:1:1 的笛卡尔结果将仅包含 1 条记录

select
        ByCat.CatCount
      + BySubCat.SubCatCount
      + BySubSubCat.SubSubCatCount as Cnt
   from
      ( select count(*) CatCount
           from view_category ) ByCat,

      ( select count(*) SubCatCount
           from view_sub_category) BySubCat,

      (select count(*) SubSubCatCount
           from view_subsub_category ) BySubSubCat

另外想象一下，如果您也需要 sum() 或 AVG() 从其他元素中计数...您可以将它们放在一行中并根据需要使用。

you don't need to do a union, and can just have each as its own from alias... As long as each query is returning only one row, you can do all sorts of crazy things. By ignoring any "join" condition, you get a Cartesian result, but a Cartesian of 1:1:1 will result with only 1 record

select
        ByCat.CatCount
      + BySubCat.SubCatCount
      + BySubSubCat.SubSubCatCount as Cnt
   from
      ( select count(*) CatCount
           from view_category ) ByCat,

      ( select count(*) SubCatCount
           from view_sub_category) BySubCat,

      (select count(*) SubSubCatCount
           from view_subsub_category ) BySubSubCat

Also imagine if you needed sum() or AVG() counts too from other elements... You could get those into a single row and use however you needed.

回复收藏 0 原文

弥枳 2025-01-01 10:23:15

如果表具有相似的结构，您可以使用 UNION 来合并结果，然后执行一个 COUNT(*)。

回复收藏 0 原文

凉月流沐 2025-01-01 10:23:15

这对我有用

select count(*) from(
(select count(*) from view_category where 1=1) union (select count(*) from view sub category where 1=1) union (select count(*) from view subsub category where 1=1) ) AS int_val;

This is working for me

select count(*) from(
(select count(*) from view_category where 1=1) union (select count(*) from view sub category where 1=1) union (select count(*) from view subsub category where 1=1) ) AS int_val;

回复收藏 0 原文

~没有更多了~