gpt4 book ai didi

mysql,计算两个表之间的不同值

转载 作者:行者123 更新时间:2023-11-29 02:39:28 25 4
gpt4 key购买 nike

我有两张 table

CREATE TABLE TableA  
(ID_A INT,
P1 INT,
P2 INT,
P3 INT,
P4 INT);

INSERT INTO TableA VALUES
(1,3,4,3,5),
(2,5,4,3,4);

CREATE TABLE TableB
(ID_B INT,
NAME TEXT);

INSERT INTO TableB VALUES
(1,"A"),
(2,"B"),
(3,"C"),
(4,"D"),
(5,"E"),
(6,"F");

表中的值

TableA
+------+----+----+----+-----+
| ID_A | P1 | P2 | P3 | P4 |
+------+----+----+----+-----+
| 1 | 3 | 4 | 3 | 5 |
| 2 | 5 | 4 | 3 | 4 |
+------+----+----+----+-----+

TableB
+------+------+
| ID_B | Name |
+------+------+
| 1 | A |
| 2 | B |
| 3 | C |
| 4 | D |
| 5 | E |
| 6 | F |
+------+------+

表A P1 = 表B ID_B ,

表A P2 = 表B ID_B ,

表A P3 = 表B ID_B ,

表A P4 = 表B ID_B

我想要TableB中P1,P2,P3,P4对应的所有名字的总和

+----------+------+
| COUNT(*) | Name |
+----------+------+
| 3 | C |
| 3 | D |
| 2 | E |
+----------+------+

感谢那些愿意帮助我的人。

最佳答案

您可以使用UNION ALLDerived table 来处理这个问题。

select count(*) cnt, name 
from
(
select b.name
from TableB b
inner join TableA p1 on p1.p1 = b.id_b
union all
select b.name
from TableB b
inner join TableA p2 on p2.p2 = b.id_b
union all
select b.name
from TableB b
inner join TableA p3 on p3.p3 = b.id_b
union all
select b.name
from TableB b
inner join TableA p4 on p4.p4 = b.id_b
)A
group by name
order by name

输出

cnt         name
----------- --------------------------------------------------
3 C
3 D
2 E

https://www.db-fiddle.com/f/nRpqNiUmpRVJJ4gMP66Ywn/9

关于mysql,计算两个表之间的不同值,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/55863184/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com