SELECT nvl(责任单位,'小计') 责任单位,
sum(decode(故障,'故障A',1)) 故障A,
sum(decode(故障,'故障B',1)) 故障B,
sum(decode(故障,'故障C',1)) 故障C,
count(1) 小计
FROM 表二
where 责任单位 in (select 责任单位 from 表1 where 是否内部='是')
GROUP BY rollup(责任单位)
第二个把where条件去掉即可
SELECT nvl(责任单位,'小计') 责任单位,
sum(decode(故障,'故障A',1)) 故障A,
sum(decode(故障,'故障B',1)) 故障B,
sum(decode(故障,'故障C',1)) 故障C,
count(1) 小计
FROM 表二
GROUP BY rollup(责任单位)
sum(decode(故障,'故障A',1)) 故障A,
sum(decode(故障,'故障B',1)) 故障B,
sum(decode(故障,'故障C',1)) 故障C,
count(1) 小计
FROM 表二
where 责任单位 in (select 责任单位 from 表1 where 是否内部='是')
GROUP BY rollup(责任单位)
第二个把where条件去掉即可
SELECT nvl(责任单位,'小计') 责任单位,
sum(decode(故障,'故障A',1)) 故障A,
sum(decode(故障,'故障B',1)) 故障B,
sum(decode(故障,'故障C',1)) 故障C,
count(1) 小计
FROM 表二
GROUP BY rollup(责任单位)
(select 1 num, 'Y' Y_N, 'IN01' NM
from dual
UNION ALL
select 2 num, 'Y' Y_N, 'IN02' NM
from dual
UNION ALL
select 3 num, 'N' Y_N, 'OUT01' NM
from dual
UNION ALL
select 4 num, 'N' Y_N, 'OUT02' NM
from dual),
t2 as
(select 'IN02' NM, 'C' ERR
from dual
union all
select 'OUT01' NM, 'A' ERR
from dual
union all
select 'IN01' NM, 'B' ERR
from dual
union all
select 'IN01' NM, 'C' ERR
from dual
union all
select 'OUT02' NM, 'A' ERR
from dual)select NM zerendanwei, A guzhangA, B guzhangB, C guzhangC, A + B + C xiaoji
from (SELECT t1.nm, ERR
FROM T1, T2
WHERE T1.NM = T2.NM
AND T1.Y_N = 'Y') pivot(COUNT(ERR) for(ERR) in('A' AS A,
'B' AS B,
'C' AS C))
UNION ALL
select 'XIAOJI', SUM(A), SUM(B), SUM(C), SUM(A + B + C)
from (SELECT t1.nm, ERR
FROM T1, T2
WHERE T1.NM = T2.NM
AND T1.Y_N = 'Y') pivot(COUNT(ERR) for(ERR) in('A' AS A,
'B' AS B,
'C' AS C));
with t1 as
(select 1 num, 'Y' Y_N, 'IN01' NM
from dual
UNION ALL
select 2 num, 'Y' Y_N, 'IN02' NM
from dual
UNION ALL
select 3 num, 'N' Y_N, 'OUT01' NM
from dual
UNION ALL
select 4 num, 'N' Y_N, 'OUT02' NM
from dual),
t2 as
(select 'IN02' NM, 'C' ERR
from dual
union all
select 'OUT01' NM, 'A' ERR
from dual
union all
select 'IN01' NM, 'B' ERR
from dual
union all
select 'IN01' NM, 'C' ERR
from dual
union all
select 'OUT02' NM, 'A' ERR
from dual)select NM zerendanwei, A guzhangA, B guzhangB, C guzhangC, A + B + C xiaoji
from (SELECT t1.nm, ERR
FROM T1, T2
WHERE T1.NM = T2.NM) pivot(COUNT(ERR) for(ERR) in('A' AS A,
'B' AS B,
'C' AS C))
UNION ALL
select 'XIAOJI', SUM(A), SUM(B), SUM(C), SUM(A + B + C)
from (SELECT t1.nm, ERR
FROM T1, T2
WHERE T1.NM = T2.NM) pivot(COUNT(ERR) for(ERR) in('A' AS A,
'B' AS B,
'C' AS C));
(select 1 num, 'Y' Y_N, 'IN01' NM
from dual
UNION ALL
select 2 num, 'Y' Y_N, 'IN02' NM
from dual
UNION ALL
select 3 num, 'N' Y_N, 'OUT01' NM
from dual
UNION ALL
select 4 num, 'N' Y_N, 'OUT02' NM
from dual),
t2 as
(select 'IN02' NM, 'C' ERR
from dual
union all
select 'OUT01' NM, 'A' ERR
from dual
union all
select 'IN01' NM, 'B' ERR
from dual
union all
select 'IN01' NM, 'C' ERR
from dual
union all
select 'OUT02' NM, 'A' ERR
from dual)select NM zerendanwei, A guzhangA, B guzhangB, C guzhangC, A + B + C xiaoji
from (SELECT t1.nm, ERR
FROM T1, T2
WHERE T1.NM = T2.NM
AND T1.Y_N = 'Y') pivot(COUNT(ERR) for(ERR) in('A' AS A,
'B' AS B,
'C' AS C))
UNION ALL
select 'XIAOJI', SUM(A), SUM(B), SUM(C), SUM(A + B + C)
from (SELECT t1.nm, ERR
FROM T1, T2
WHERE T1.NM = T2.NM
AND T1.Y_N = 'Y') pivot(COUNT(ERR) for(ERR) in('A' AS A,
'B' AS B,
'C' AS C));
with t1 as
(select 1 num, 'Y' Y_N, 'IN01' NM
from dual
UNION ALL
select 2 num, 'Y' Y_N, 'IN02' NM
from dual
UNION ALL
select 3 num, 'N' Y_N, 'OUT01' NM
from dual
UNION ALL
select 4 num, 'N' Y_N, 'OUT02' NM
from dual),
t2 as
(select 'IN02' NM, 'C' ERR
from dual
union all
select 'OUT01' NM, 'A' ERR
from dual
union all
select 'IN01' NM, 'B' ERR
from dual
union all
select 'IN01' NM, 'C' ERR
from dual
union all
select 'OUT02' NM, 'A' ERR
from dual)select NM zerendanwei, A guzhangA, B guzhangB, C guzhangC, A + B + C xiaoji
from (SELECT t1.nm, ERR
FROM T1, T2
WHERE T1.NM = T2.NM) pivot(COUNT(ERR) for(ERR) in('A' AS A,
'B' AS B,
'C' AS C))
UNION ALL
select 'XIAOJI', SUM(A), SUM(B), SUM(C), SUM(A + B + C)
from (SELECT t1.nm, ERR
FROM T1, T2
WHERE T1.NM = T2.NM) pivot(COUNT(ERR) for(ERR) in('A' AS A,
'B' AS B,
'C' AS C));太感谢了,版主。
就是结果还是没有按照表一的排序号来排序,目前的结果是:
1 IN02 0 0 1 1
2 IN01 0 1 1 2
3 XIAOJI 0 1 2 3我要的结果是:这里的责任单位排序要按照表一的排序号来排序,1就是第一个,2就是第二个,3就是第三个,4就是第四个,这样的。
1 IN01 0 1 1 2
2 IN02 0 0 1 1
3 XIAOJI 0 1 2 3还请版主劳心了,谢谢啊。
with t1 as
(select 1 num, 'Y' Y_N, 'IN01' NM
from dual
UNION ALL
select 2 num, 'Y' Y_N, 'IN02' NM
from dual
UNION ALL
select 3 num, 'N' Y_N, 'OUT01' NM
from dual
UNION ALL
select 4 num, 'N' Y_N, 'OUT02' NM
from dual),
t2 as
(select 'IN02' NM, 'C' ERR
from dual
union all
select 'OUT01' NM, 'A' ERR
from dual
union all
select 'IN01' NM, 'B' ERR
from dual
union all
select 'IN01' NM, 'C' ERR
from dual
union all
select 'OUT02' NM, 'A' ERR
from dual)
select *
from (select NM zerendanwei,
A guzhangA,
B guzhangB,
C guzhangC,
A + B + C xiaoji
from (SELECT t1.nm, ERR
FROM T1, T2
WHERE T1.NM = T2.NM
AND T1.Y_N = 'Y') pivot(COUNT(ERR) for(ERR) in('A' AS A,
'B' AS B,
'C' AS C))
order by nm)
UNION ALL
select 'XIAOJI', SUM(A), SUM(B), SUM(C), SUM(A + B + C)
from (SELECT t1.nm, ERR
FROM T1, T2
WHERE T1.NM = T2.NM
AND T1.Y_N = 'Y') pivot(COUNT(ERR) for(ERR) in('A' AS A,
'B' AS B,
'C' AS C));
oracle本身rollup就可以实现小计功能