执行SQ文
【select PROVINCECD,OLDSEGMENT,count(1) from main_pas where
(PROVINCECD='01' and OLDSEGMENT='PC/LUX') or (PROVINCECD='01' and OLDSEGMENT='PC/MED')
or (PROVINCECD='22' and OLDSEGMENT='PC/LUX') or (PROVINCECD='22' and OLDSEGMENT='PC/MED')
and REGYEARMONTH>= '200801' and REGYEARMONTH<='200809' group by PROVINCECD,OLDSEGMENT】
的结果如下 :
1 01 PC/LUX 1796
2 01 PC/MED 5559
3 22 PC/MED 1130(4个条件3个结果,由一个被省略)结果中符合【PROVINCECD='22' and OLDSEGMENT='PC/LUX'】这个条件的结果是0,在最终结果中没有显示,请问怎样才能让这条0记录的值也显示在最终结果中呢?
【select PROVINCECD,OLDSEGMENT,count(1) from main_pas where
(PROVINCECD='01' and OLDSEGMENT='PC/LUX') or (PROVINCECD='01' and OLDSEGMENT='PC/MED')
or (PROVINCECD='22' and OLDSEGMENT='PC/LUX') or (PROVINCECD='22' and OLDSEGMENT='PC/MED')
and REGYEARMONTH>= '200801' and REGYEARMONTH<='200809' group by PROVINCECD,OLDSEGMENT】
的结果如下 :
1 01 PC/LUX 1796
2 01 PC/MED 5559
3 22 PC/MED 1130(4个条件3个结果,由一个被省略)结果中符合【PROVINCECD='22' and OLDSEGMENT='PC/LUX'】这个条件的结果是0,在最终结果中没有显示,请问怎样才能让这条0记录的值也显示在最终结果中呢?
from main_pas a,
(select PROVINCECD, OLDSEGMENT, count(1) cu
from main_pas
where (PROVINCECD = '01' and OLDSEGMENT = 'PC/LUX')
or (PROVINCECD = '01' and OLDSEGMENT = 'PC/MED')
or (PROVINCECD = '22' and OLDSEGMENT = 'PC/LUX')
or (PROVINCECD = '22' and OLDSEGMENT = 'PC/MED')
and REGYEARMONTH >= '200801'
and REGYEARMONTH <= '200809'
group by PROVINCECD, OLDSEGMENT) b
where a.PROVINCECD = b.PROVINCECD(+)
(PROVINCECD='01' and OLDSEGMENT='PC/LUX') or (PROVINCECD='01' and OLDSEGMENT='PC/MED')
or (PROVINCECD='22' and OLDSEGMENT='PC/MED')
and REGYEARMONTH>= '200801' and REGYEARMONTH <='200809' group by PROVINCECD,OLDSEGMENT
from (select distinct PROVINCECD, OLDSEGMENT from main_pas) a,
(select PROVINCECD, OLDSEGMENT, count(1) cu
from main_pas
where (PROVINCECD = '01' and OLDSEGMENT = 'PC/LUX')
or (PROVINCECD = '01' and OLDSEGMENT = 'PC/MED')
or (PROVINCECD = '22' and OLDSEGMENT = 'PC/LUX')
or (PROVINCECD = '22' and OLDSEGMENT = 'PC/MED')
and REGYEARMONTH >= '200801'
and REGYEARMONTH <= '200809'
group by PROVINCECD, OLDSEGMENT) b
where a.PROVINCECD = b.PROVINCECD(+)
select PROVINCECD, OLDSEGMENT, c
from (select PROVINCECD, OLDSEGMENT, count(1) c
from main_pas
where REGYEARMONTH >= '200801'
and REGYEARMONTH <= '200809'
group by PROVINCECD, OLDSEGMENT) t
where (PROVINCECD = '01' and OLDSEGMENT = 'PC/LUX')
or (PROVINCECD = '01' and OLDSEGMENT = 'PC/MED')
or (PROVINCECD = '22' and OLDSEGMENT = 'PC/LUX')
or (PROVINCECD = '22' and OLDSEGMENT = 'PC/MED')
1 01 PC/LUX 1796
2 01 PC/MED 5559
3 22 PC/LUX 0
4 22 PC/MED 1130
from main_pas
where (PROVINCECD='01' and OLDSEGMENT='PC/LUX')
or (PROVINCECD='01' and OLDSEGMENT='PC/MED')
or (PROVINCECD='22' and OLDSEGMENT='PC/LUX')
or (PROVINCECD='22' and OLDSEGMENT='PC/MED')
and (REGYEARMONTH>= '200801')
and (REGYEARMONTH <='200809')
group by PROVINCECD,OLDSEGMENT
--原来的语句会不会有逻辑问题呢?是不是下面的:
select PROVINCECD,OLDSEGMENT,count(1)
from main_pas
where (
(PROVINCECD='01' and OLDSEGMENT='PC/LUX')
or (PROVINCECD='01' and OLDSEGMENT='PC/MED')
or (PROVINCECD='22' and OLDSEGMENT='PC/LUX')
or (PROVINCECD='22' and OLDSEGMENT='PC/MED')
)
and (REGYEARMONTH>= '200801')
and (REGYEARMONTH <='200809')
group by PROVINCECD,OLDSEGMENT
1 01 MPV 5559
2 01 MPV 1796
3 01 PC/LUX 5559
4 01 PC/LUX 1796
5 01 PC/MED 5559
6 01 PC/MED 1796
7 01 PC/MINI 5559
8 01 PC/MINI 1796
9 01 PC/SML_HIGH 5559
10 01 PC/SML_HIGH 1796
11 01 PC/SML_LOW 5559
12 01 PC/SML_LOW 1796
13 01 PC/UK 5559
14 01 PC/UK 1796
15 01 SUV 5559
16 01 SUV 1796
17 01 Unknown 5559
18 01 Unknown 1796
19 02 MPV 0
20 02 PC/LUX 0
21 02 PC/MED 0
22 02 PC/MINI 0
23 02 PC/SML_HIGH 0
24 02 PC/SML_High 0
25 02 PC/SML_LOW 0
26 02 PC/UK 0
27 02 SUV 0
28 02 Unknown 0
29 03 MPV 0
30 03 PC/LUX 0
31 03 PC/MED 0
32 03 PC/MINI 0
33 03 PC/SML_HIGH 0
34 03 PC/SML_LOW 0
35 03 PC/UK 0
36 03 SUV 0
37 03 Unknown 0
38 04 MPV 0
39 04 PC/LUX 0
40 04 PC/MED 0
41 04 PC/MINI 0
42 04 PC/SML_HIGH 0
43 04 PC/SML_LOW 0
44 04 PC/UK 0
45 04 SUV 0
46 04 Unknown 0
47 05 MPV 0
48 05 PC/LUX 0
49 05 PC/MED 0
50 05 PC/MINI 0
51 05 PC/SML_HIGH 0
52 05 PC/SML_LOW 0
53 05 PC/UK 0
54 05 SUV 0
55 05 Unknown 0
56 06 MPV 0
57 06 PC/LUX 0
58 06 PC/MED 0
59 06 PC/MINI 0
60 06 PC/SML_HIGH 0
61 06 PC/SML_LOW 0
62 06 PC/UK 0
63 06 SUV 0
64 06 Unknown 0
65 07 MPV 0
66 07 PC/LUX 0
67 07 PC/MED 0
68 07 PC/MINI 0
69 07 PC/SML_HIGH 0
70 07 PC/SML_LOW 0
71 07 PC/UK 0
72 07 SUV 0
73 07 Unknown 0
74 08 MPV 0
75 08 PC/LUX 0
76 08 PC/MED 0
77 08 PC/MINI 0
78 08 PC/SML_HIGH 0
79 08 PC/SML_LOW 0
80 08 PC/UK 0
81 08 SUV 0
82 08 Unknown 0
83 09 MPV 0
84 09 PC/LUX 0
85 09 PC/MED 0
86 09 PC/MINI 0
87 09 PC/SML_HIGH 0
88 09 PC/SML_High 0
89 09 PC/SML_LOW 0
90 09 PC/UK 0
91 09 SUV 0
92 09 Unknown 0
93 10 MPV 0
94 10 PC/LUX 0
95 10 PC/MED 0
96 10 PC/MINI 0
97 10 PC/SML_HIGH 0
98 10 PC/SML_High 0
99 10 PC/SML_LOW 0
100 10 PC/UK 0
101 10 SUV 0
102 10 Unknown 0
103 11 MPV 0
104 11 PC/LUX 0
105 11 PC/MED 0
106 11 PC/MINI 0
107 11 PC/SML_HIGH 0
108 11 PC/SML_LOW 0
109 11 PC/UK 0
110 11 SUV 0
111 11 Unknown 0
112 12 MPV 0
113 12 PC/LUX 0
114 12 PC/MED 0
115 12 PC/MINI 0
116 12 PC/SML_HIGH 0
117 12 PC/SML_LOW 0
118 12 PC/UK 0
119 12 SUV 0
120 12 Unknown 0
121 13 MPV 0
122 13 PC/LUX 0
123 13 PC/MED 0
124 13 PC/MINI 0
125 13 PC/SML_HIGH 0
126 13 PC/SML_LOW 0
127 13 PC/UK 0
128 13 SUV 0
129 13 Unknown 0
130 14 MPV 0
131 14 PC/LUX 0
132 14 PC/MED 0
133 14 PC/MINI 0
134 14 PC/SML_HIGH 0
135 14 PC/SML_LOW 0
136 14 PC/UK 0
137 14 SUV 0
138 14 Unknown 0
139 15 MPV 0
140 15 PC/LUX 0
141 15 PC/MED 0
142 15 PC/MINI 0
143 15 PC/SML_HIGH 0
144 15 PC/SML_LOW 0
145 15 PC/UK 0
146 15 SUV 0
147 15 Unknown 0
148 16 MPV 0
149 16 PC/LUX 0
150 16 PC/MED 0
151 16 PC/MINI 0
152 16 PC/SML_HIGH 0
153 16 PC/SML_LOW 0
154 16 PC/UK 0
155 16 SUV 0
156 16 Unknown 0
157 17 MPV 0
158 17 PC/LUX 0
159 17 PC/MED 0
160 17 PC/MINI 0
161 17 PC/SML_HIGH 0
162 17 PC/SML_LOW 0
163 17 PC/UK 0
164 17 SUV 0
165 17 Unknown 0
166 18 MPV 0
167 18 PC/LUX 0
168 18 PC/MED 0
169 18 PC/MINI 0
170 18 PC/SML_HIGH 0
171 18 PC/SML_LOW 0
172 18 PC/UK 0
173 18 SUV 0
174 18 Unknown 0
175 19 MPV 0
176 19 PC/LUX 0
177 19 PC/MED 0
178 19 PC/MINI 0
179 19 PC/SML_HIGH 0
180 19 PC/SML_High 0
181 19 PC/SML_LOW 0
182 19 PC/UK 0
183 19 SUV 0
184 19 Unknown 0
185 20 MPV 0
186 20 PC/LUX 0
187 20 PC/MED 0
188 20 PC/MINI 0
189 20 PC/SML_HIGH 0
190 20 PC/SML_LOW 0
191 20 PC/UK 0
192 20 SUV 0
193 20 Unknown 0
194 21 MPV 0
195 21 PC/LUX 0
196 21 PC/MED 0
197 21 PC/MINI 0
198 21 PC/SML_HIGH 0
199 21 PC/SML_LOW 0
200 21 PC/UK 0
201 21 SUV 0
202 21 Unknown 0
203 22 MPV 1130
204 22 MPV 285
205 22 PC/LUX 1130
206 22 PC/LUX 285
207 22 PC/MED 1130
208 22 PC/MED 285
209 22 PC/MINI 1130
210 22 PC/MINI 285
211 22 PC/SML_HIGH 1130
212 22 PC/SML_HIGH 285
213 22 PC/SML_High 1130
214 22 PC/SML_High 285
215 22 PC/SML_LOW 1130
216 22 PC/SML_LOW 285
217 22 PC/UK 1130
218 22 PC/UK 285
219 22 SUV 1130
220 22 SUV 285
221 22 Unknown 1130
222 22 Unknown 285
223 23 MPV 0
224 23 PC/LUX 0
225 23 PC/MED 0
226 23 PC/MINI 0
227 23 PC/SML_HIGH 0
228 23 PC/SML_High 0
229 23 PC/SML_LOW 0
230 23 PC/UK 0
231 23 SUV 0
232 23 Unknown 0
233 24 MPV 0
234 24 PC/LUX 0
235 24 PC/MED 0
236 24 PC/MINI 0
237 24 PC/SML_HIGH 0
238 24 PC/SML_LOW 0
239 24 PC/UK 0
240 24 SUV 0
241 24 Unknown 0
242 25 MPV 0
243 25 PC/LUX 0
244 25 PC/MED 0
245 25 PC/MINI 0
246 25 PC/SML_HIGH 0
247 25 PC/SML_LOW 0
248 25 PC/UK 0
249 25 SUV 0
250 25 Unknown 0
251 26 MPV 0
252 26 PC/LUX 0
253 26 PC/MED 0
254 26 PC/MINI 0
255 26 PC/SML_HIGH 0
256 26 PC/SML_LOW 0
257 26 PC/UK 0
258 26 SUV 0
259 26 Unknown 0
260 27 MPV 0
261 27 PC/LUX 0
262 27 PC/MED 0
263 27 PC/MINI 0
264 27 PC/SML_HIGH 0
265 27 PC/SML_LOW 0
266 27 PC/UK 0
267 27 SUV 0
268 27 Unknown 0
269 28 MPV 0
270 28 PC/LUX 0
271 28 PC/MED 0
272 28 PC/MINI 0
273 28 PC/SML_HIGH 0
274 28 PC/SML_LOW 0
275 28 PC/UK 0
276 28 SUV 0
277 28 Unknown 0
278 29 MPV 0
279 29 PC/LUX 0
280 29 PC/MED 0
281 29 PC/MINI 0
282 29 PC/SML_HIGH 0
283 29 PC/SML_LOW 0
284 29 PC/UK 0
285 29 SUV 0
286 30 MPV 0
287 30 PC/LUX 0
288 30 PC/MED 0
289 30 PC/MINI 0
290 30 PC/SML_HIGH 0
291 30 PC/SML_LOW 0
292 30 PC/UK 0
293 30 SUV 0
294 30 Unknown 0
295 31 MPV 0
296 31 PC/LUX 0
297 31 PC/MED 0
298 31 PC/MINI 0
299 31 PC/SML_HIGH 0
300 31 PC/SML_LOW 0
301 31 PC/UK 0
302 31 SUV 0
303 31 Unknown 0
-- 下面SQL的表达,你的四个条件中无论哪个不存在数据,都有统计出0
-- 但有点不足的是:四个查询条件写死了.
with x as
(select '01','PC/LUX' from dual union all select '01','PC/MED' from dual
union all select '22','PC/LUX' from dual union all select '22','PC/MED' from dual)
select x.*,nvl(t.c,0)
from
x,
(
select PROVINCECD, OLDSEGMENT, count(1) c
from main_pas
where (PROVINCECD = '01' and OLDSEGMENT = 'PC/LUX')
or (PROVINCECD = '01' and OLDSEGMENT = 'PC/MED')
or (PROVINCECD = '22' and OLDSEGMENT = 'PC/LUX')
or (PROVINCECD = '22' and OLDSEGMENT = 'PC/MED')
and REGYEARMONTH >= '200801'
and REGYEARMONTH <= '200809'
group by PROVINCECD, OLDSEGMENT
)t
where x left join t on(x.PROVINCECD=t.PROVINCECD and x.OLDSEGMENT=t.OLDSEGMENT)
with x as
(select '01' PROVINCECD,'PC/LUX' OLDSEGMENT from dual union all select '01','PC/MED' from dual
union all select '22','PC/LUX' from dual union all select '22','PC/MED' from dual)
select x.*,nvl(t.c,0)
from
x left join
(
select PROVINCECD, OLDSEGMENT, count(1) c
from main_pas
where (PROVINCECD = '01' and OLDSEGMENT = 'PC/LUX')
or (PROVINCECD = '01' and OLDSEGMENT = 'PC/MED')
or (PROVINCECD = '22' and OLDSEGMENT = 'PC/LUX')
or (PROVINCECD = '22' and OLDSEGMENT = 'PC/MED')
and REGYEARMONTH >= '200801'
and REGYEARMONTH <= '200809'
group by PROVINCECD, OLDSEGMENT
)t on(x.PROVINCECD=t.PROVINCECD and x.OLDSEGMENT=t.OLDSEGMENT