mysql - 计算MySql中的多列数

Question

表“兽人”

P1        p2       p3         p4__________________________________w2        w2       w2         w1w1        w5       w3         w7

表“artg”

ref      design_________________w1        product1w2        product2  w3        product3w4        product4w5        product5w6        product6w7        product7  

我需要将 P1、P2、P3 和 P4 一起计数。

类似输出:

Design    Total_________________Product1     2Product2     3

Answer 1

使用Union All将orc表的所有列合并为一列。然后找到每个设计的数量并将其与artg表连接起来。

查询

select t2.design,count(t1.p) as Total 
from
(
    select p1 as p from orc
    union all
    select p2 as p from orc
    union all
    select p3 as p from orc
    union all
    select p4 as p from orc
)t1
right join artg t2
on t1.p = t2.ref
group by t2.design;

结果

+----------+-------+
| Design   | Total |
+----------+-------+
| product1 | 2     |
| product2 | 3     |
| product3 | 1     |
| product4 | 0     |
| product5 | 1     |
| product6 | 0     |
| product7 | 1     |
+----------+-------+