select bh ,mc ,sum(bz) from ( select bh,mc,bz from table union select bh2,mc2,bz from table union select bh3,mc3,bz from table ) group by bh ,mc
首先必须把三列通过union all合并为一列,剩下的就好办了。sql语句如下所示: --sql语句 SELECT d.bh,d.mc,SUM(d.bz) "BZ(补助)" FROM (SELECT t.bh bh, t.mc mc,t.bz bz FROM test_a t UNION ALL SELECT t.bh2 bh, t.mc2 mc,t.bz bz FROM test_a t UNION ALL SELECT t.bh3 bh, t.mc3 mc,t.bz bz FROM test_a t) d WHERE d.bh IS NOT NULL GROUP BY d.bh,d.mc ORDER BY d.bh
每个人查一次(共3次)ZYBH、BH、MC
再根据 BH 分组统计好了
bh
,mc
,sum(bz)
from (
select bh,mc,bz from table union
select bh2,mc2,bz from table union
select bh3,mc3,bz from table
)
group by
bh
,mc
--sql语句
SELECT d.bh,d.mc,SUM(d.bz) "BZ(补助)" FROM
(SELECT t.bh bh, t.mc mc,t.bz bz FROM test_a t
UNION ALL
SELECT t.bh2 bh, t.mc2 mc,t.bz bz FROM test_a t
UNION ALL
SELECT t.bh3 bh, t.mc3 mc,t.bz bz FROM test_a t) d
WHERE d.bh IS NOT NULL
GROUP BY d.bh,d.mc
ORDER BY d.bh