从带有联合部分2的多个表中选择计数相关的行 [英] Select count dependent rows from multiple tables with union part 2

问题描述

在第2部分中，我有两个表

In part 2, i have two tables

tb1中的第一个表，tb2中的第二个表.

first table from tb1, second from tb2.

+------+----------+------+
| kd   | skl      | name |
+------+----------+------+
| 001  | database | a    |
| 001  | web      | b    |
| 001  | web      | c    |
| 002  | app      | d    |
| 002  | web      | e    |
| 002  | app      | f    |
| 003  | json     | g    |
| 003  | -        | h    |
| 003  | -        | i    |
| 004  | ruby     | j    |
| 004  | database | k    |
| 004  | web      | l    |
| 005  | -        | m    |
| 005  | -        | n    |
| 005  | -        | o    |
+------+----------+------+

+------+----------+------+
| kd   | skl      | name |
+------+----------+------+
| 001  | database | p    |
| 001  | web      | q    |
| 001  | web      | r    |
| 002  | app      | s    |
| 002  | web      | t    |
| 002  | app      | u    |
| 003  | json     | v    |
| 003  | web      | w    |
| 003  | app      | x    |
| 004  | ruby     | y    |
| 004  | database | z    |
| 004  | web      | d    |
| 005  | -        | c    |
| 005  | web      | b    |
| 005  | app      | a    |
+------+----------+------+

就像我在选择计数中的第一个问题一样来自具有联合的多个表的相关行

我在下面进行此查询.

    select kd,skl,name,sum(row) brs from
(select a.kd,skl,name,count(a.kd) row
from tb1 a where skl in('web','app')
group by a.kd,skl,name
union all
select b.kd,skl,name,count(b.kd)
from tb2 b where skl in('web','app')
group by b.kd,skl,name)t
group by kd,name,skl;

选择查询给出结果

+------+-----+------+------+
| kd   | skl | name | brs  |
+------+-----+------+------+
| 001  | web | b    |    1 |
| 001  | web | c    |    1 |
| 001  | web | q    |    1 |
| 001  | web | r    |    1 |
| 002  | app | d    |    1 |
| 002  | web | e    |    1 |
| 002  | app | f    |    1 |
| 002  | app | s    |    1 |
| 002  | web | t    |    1 |
| 002  | app | u    |    1 |
| 003  | web | w    |    1 |
| 003  | app | x    |    1 |
| 004  | web | d    |    1 |
| 004  | web | l    |    1 |
| 005  | app | a    |    1 |
| 005  | web | b    |    1 |
+------+-----+------+------+

问题在于brs列的不计数取决于kd列数据.

the problem is that brs column not count depends on kd column data.

我需要下面的结果

+------+-----+------+------+
| kd   | skl | name | brs  |
+------+-----+------+------+
| 001  | web | b    |    4 |
| 001  | web | c    |    4 |
| 001  | web | q    |    4 |
| 001  | web | r    |    4 |
| 002  | app | d    |    6 |
| 002  | web | e    |    6 |
| 002  | app | f    |    6 |
| 002  | app | s    |    6 |
| 002  | web | t    |    6 |
| 002  | app | u    |    6 |
| 003  | web | w    |    2 |
| 003  | app | x    |    2 |
| 004  | web | l    |    2 |
| 004  | web | d    |    2 |
| 005  | web | b    |    2 |
| 005  | app | a    |    2 |
+------+-----+------+------+

请提供选择查询示例或该主题的线索.

please give select query example or a clue for this topic.

非常感谢您的所有建议.

Many Thanks for all advices.

推荐答案

这不是最美观的查询，但它应该可以工作:

This is not the most aesthetically pleasing query but it should work:

SELECT t1.kd, t1.sk1, t1.name, t2.brs
FROM
(
    SELECT a.kd, skl, name
    FROM tb1 a WHERE skl IN ('web','app')
    UNION ALL
    SELECT b.kd, skl, name,
    FROM tb2 b WHERE skl IN ('web','app')
) t1
INNER JOIN
(
    SELECT r.kd, SUM(r.row) brs
    FROM
    (
        SELECT a.kd, COUNT(a.kd) row
        FROM tb1 a WHERE skl IN ('web','app')
        GROUP BY a.kd
        UNION ALL
        SELECT b.kd, COUNT(b.kd)
        FROM tb2 b WHERE skl IN ('web','app')
        GROUP BY b.kd
    ) r
    GROUP BY r.kd
) t2
ON t1.kd = t2.kd

这篇关于从带有联合部分2的多个表中选择计数相关的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！