SQL> SELECT NVL(B.QT, 0) AS QT, TOTAL
2 FROM (SELECT BFHXLB,
3 COUNT(1) AS TOTAL
4 FROM ARC_AQJLBUHX T
5 WHERE 1 = 1
6 GROUP BY BFHXLB) A,
7 (SELECT BFHXLB, COUNT(1) QT
8 FROM ARC_AQJLBUHX T
9 WHERE DW NOT IN
10 ('01', '02', '030', '19', '23', '24', '40', '20', '222000')
11 GROUP BY BFHXLB) B
12 WHERE A.BFHXLB = B.BFHXLB(+)
13 UNION
14 SELECT SUM(QT),
15 SUM(TOTAL)
16 FROM (SELECT BFHXLB,
17 COUNT(1) AS TOTAL
18 FROM ARC_AQJLBUHX T
19 WHERE 1 = 1
20 GROUP BY BFHXLB) A,
21 (SELECT BFHXLB, COUNT(1) QT
22 FROM ARC_AQJLBUHX T
23 WHERE DW NOT IN
24 ('01', '02', '030', '19', '23', '24', '40', '20', '222000')
25 GROUP BY BFHXLB) B
26 WHERE A.BFHXLB = B.BFHXLB(+)
27 ORDER BY TOTAL;
QT TOTAL
---------- ----------
0 1
1
已选择2行。
SQL> SELECT NVL(B.QT, 0) AS QT, TOTAL
2 FROM (SELECT BFHXLB,
3 COUNT(*) AS TOTAL
4 FROM ARC_AQJLBUHX T
5 WHERE 1 = 1
6 GROUP BY BFHXLB) A,
7 (SELECT BFHXLB, COUNT(*) QT
8 FROM ARC_AQJLBUHX T
9 WHERE DW NOT IN
10 ('01', '02', '030', '19', '23', '24', '40', '20', '222000')
11 GROUP BY BFHXLB) B
12 WHERE A.BFHXLB = B.BFHXLB(+)
13 UNION
14 SELECT SUM(QT),
15 SUM(TOTAL)
16 FROM (SELECT BFHXLB,
17 COUNT(*) AS TOTAL
18 FROM ARC_AQJLBUHX T
19 WHERE 1 = 1
20 GROUP BY BFHXLB) A,
21 (SELECT BFHXLB, COUNT(*) QT
22 FROM ARC_AQJLBUHX T
23 WHERE DW NOT IN
24 ('01', '02', '030', '19', '23', '24', '40', '20', '222000')
25 GROUP BY BFHXLB) B
26 WHERE A.BFHXLB = B.BFHXLB(+)
27 ORDER BY TOTAL;
两者在效率上没有多少区别
select count(1) from table_name
形同
select count('adsf') from table_name
(可为任意数字或字符)
形同
select count(one) from(select 1 as one from table_name)
select count(one) from(select 100 as one from table_name)
select count(one) from(select 'asdf' as one from table_name)
SQL*PLUS>SELECT NVL(B.QT, 0) AS QT, TOTAL
2 FROM (SELECT BFHXLB,
3 COUNT(1) AS TOTAL
4 FROM ARC_AQJLBUHX T
5 WHERE 1 = 1
6 GROUP BY BFHXLB) A,
7 (SELECT BFHXLB, COUNT(1) QT
8 FROM ARC_AQJLBUHX T
9 WHERE DW NOT IN
10 ('01', '02', '030', '19', '23', '24', '40', '20', '222000')
11 GROUP BY BFHXLB) B
12 WHERE A.BFHXLB = B.BFHXLB(+)
13 UNION
14 SELECT SUM(QT),
15 SUM(TOTAL)
16 FROM (SELECT BFHXLB,
17 COUNT(1) AS TOTAL
18 FROM ARC_AQJLBUHX T
19 WHERE 1 = 1
20 GROUP BY BFHXLB) A,
21 (SELECT BFHXLB, COUNT(1) QT
22 FROM ARC_AQJLBUHX T
23 WHERE DW NOT IN
24 ('01', '02', '030', '19', '23', '24', '40', '20', '222000')
25 GROUP BY BFHXLB) B
26 WHERE A.BFHXLB = B.BFHXLB(+)
27 ORDER BY TOTAL;
QT TOTAL
---------- ----------
0 1
1 1
已用时间: 00: 00: 00.00
SQL*PLUS>
然后是count(*)的统计结果
SQL*PLUS>SELECT NVL(B.QT, 0) AS QT, TOTAL
2 FROM (SELECT BFHXLB,
3 COUNT(*) AS TOTAL
4 FROM ARC_AQJLBUHX T
5 WHERE 1 = 1
6 GROUP BY BFHXLB) A,
7 (SELECT BFHXLB, COUNT(*) QT
8 FROM ARC_AQJLBUHX T
9 WHERE DW NOT IN
10 ('01', '02', '030', '19', '23', '24', '40', '20', '222000')
11 GROUP BY BFHXLB) B
12 WHERE A.BFHXLB = B.BFHXLB(+)
13 UNION
14 SELECT SUM(QT),
15 SUM(TOTAL)
16 FROM (SELECT BFHXLB,
17 COUNT(*) AS TOTAL
18 FROM ARC_AQJLBUHX T
19 WHERE 1 = 1
20 GROUP BY BFHXLB) A,
21 (SELECT BFHXLB, COUNT(*) QT
22 FROM ARC_AQJLBUHX T
23 WHERE DW NOT IN
24 ('01', '02', '030', '19', '23', '24', '40', '20', '222000')
25 GROUP BY BFHXLB) B
26 WHERE A.BFHXLB = B.BFHXLB(+)
27 ORDER BY TOTAL;