mysqlサブクエリ
59877 ワード
1 # 7：
2 /*
3 ：
4 select ， 、
5 ，
6
7 ：
8 ：、
9 select ：
10
11 FROM ：、
12
13 where having ：***
14 （ ）√
15 （ ） √
16
17 exists （ ）：
18
19
20 ： （ ） （ ）
21
22 （ ）
23 、
24 （ ）
25
26
27 */
28 # 、where haing
29 /*1、 （ ）
30 2、 （ ）
31 3、 ( ）
32 ：
33 ①
34 ②
35 ③ ，
36 > < >= >= = <>
37
38 ，
39 in、any/some、all
40
41 ④ ，
42 */
43 #1、
44 # 1： Abel ？
45 ① Abel SELECT
46 salary
47 FROM
48 employees
49 WHERE
50 last_name = 'Abel' ② ， salary > ① SELECT
51 *
52 FROM
53 employees
54 WHERE
55 salary > (
56 SELECT
57 salary
58 FROM
59 employees
60 WHERE
61 last_name = 'Abel'
62 );
63
64 # 2： ： jb_id 141 ， salary 143 ，job_id
65 #① 141 job_id
66 SELECT
67 job_id
68 FROM
69 employees
70 WHERE
71 employee_id = 141 #② 143 salary
72 SELECT
73 salary
74 FROM
75 employees
76 WHERE
77 employee_id = 143 #③ ，job_id ， job_id=① salary>②
78 SELECT
79 last_name,
80 job_id,
81 salary
82 FROM
83 employees
84 WHERE
85 job_id = (
86 SELECT
87 job_id
88 FROM
89 employees
90 WHERE
91 employee_id = 141
92 )
93 AND salary > (
94 SELECT
95 salary
96 FROM
97 employees
98 WHERE
99 employee_id = 143
100 );
101
102 # 3： last_name,job_id salary
103 SELECT
104 last_name,
105 job_id,
106 salary
107 FROM
108 employees
109 WHERE
110 salary = (
111 SELECT
112 min(salary)
113 FROM
114 employees
115 );
116
117 # 4 50 id
118 # 50
119 SELECT
120 MIN(salary)
121 FROM
122 employees
123 WHERE
124 department_id = 50 #
125 SELECT
126 MIN(salary),
127 department_id
128 FROM
129 employees
130 GROUP BY
131 department_id # ② ， min(salary)>①
132 SELECT
133 MIN(salary),
134 department_id
135 FROM
136 employees
137 GROUP BY
138 department_id
139 HAVING
140 MIN(salary) > (
141 SELECT
142 MIN(salary)
143 FROM
144 employees
145 WHERE
146 department_id = 50
147 );
148
149 #
150 SELECT
151 MIN(salary),
152 department_id
153 FROM
154 employees
155 GROUP BY
156 department_id
157 HAVING
158 MIN(salary) > (
159 SELECT
160 salary
161 FROM
162 employees
163 WHERE
164 department_id = 250
165 );
166
167 #2、 （ ）
168 # 1： location id 1400 1700
169 #① location_id 1400 1700
170 SELECT DISTINCT
171 department_id
172 FROM
173 departments
174 WHERE
175 location_id IN (1400, 1700) # ， ①
176 SELECT
177 last_name
178 FROM
179 employees
180 WHERE
181 department_id IN (
182 SELECT DISTINCT
183 department_id
184 FROM
185 departments
186 WHERE
187 location_id IN (1400, 1700)
188 );
189
190 # 2： job_id 'IT PROG’ ： 、 、 job_id salary
191 # job_id 'IT PROG’
192 SELECT DISTINCT
193 salary
194 FROM
195 employees
196 WHERE
197 job_id = 'IT_PROG' # 、 、 job_id salary,salary<①
198 SELECT
199 last_name,
200 employee_id,
201 job_id,
202 salary
203 FROM
204 employees
205 WHERE
206 salary < ANY (
207 SELECT DISTINCT
208 salary
209 FROM
210 employees
211 WHERE
212 job_id = 'IT_PROG'
213 )
214 AND job_id <> 'IT_PROG';
215
216 # 3： job_id ' IT PROG' 、 、job_id sa1ary
217 SELECT
218 last_name,
219 employee_id,
220 job_id,
221 salary
222 FROM
223 employees
224 WHERE
225 salary < ALL (
226 SELECT DISTINCT
227 salary
228 FROM
229 employees
230 WHERE
231 job_id = 'IT_PROG'
232 )
233 AND job_id <> 'IT_PROG';
234
235 #3、 （ ）
236 # ：
237 SELECT
238 *
239 FROM
240 employees
241 WHERE
242 (employee_id, salary) = #①
243 SELECT
244 MIN(
245 employee_i #①
246 SELECT
247 MIN(employee_id)
248 FROM
249 employeesd
250 )
251 FROM
252 employees;
253
254 #②
255 SELECT
256 MAX(salary)
257 FROM
258 employees #③
259 SELECT
260 *
261 FROM
262 employees
263 WHERE
264 employee_id = (
265 SELECT
266 MIN(employee_id)
267 FROM
268 employees
269 )
270 AND salary = # 、select
271 /*
272
273
274 # 、select
275 /*
276
277 */
278 # 1：
279 SELECT
280 d.*, (
281 SELECT
282 COUNT(*)
283 FROM
284 employees e
285 WHERE
286 e.department_id = d.department_id
287 )
288 FROM
289 departments dȩǏ */ # 1：
290 SELECT
291 d.*, (
292 SELECT
293 COUNT(*)
294 FROM
295 employees e
296 WHERE
297 e.department_id = d.department_id
298 )
299 FROM
300 departments d;
301
302 # 2： =102
303 SELECT
304 (
305 SELECT
306 department_name
307 FROM
308 departments d
309 JOIN employees e ON d.department_id = e.department_id
310 WHERE
311 e.employee_id = 102
312 ) # 、from
313 /*
314 ，
315 */
316 # ：
317 #
318 SELECT
319 avg(salary),
320 department_id
321 FROM
322 employees
323 GROUP BY
324 department_id;
325
326 #② 1 job_grades ， BETWEEN lowest_sal and highest_sal
327 SELECT
328 ag_dep.*, g.grade_level
329 FROM
330 (
331 SELECT
332 avg(salary) ag,
333 department_id
334 FROM
335 employees
336 GROUP BY
337 department_id
338 ) ag_dep
339 JOIN job_grades g ON ag_dep.ag BETWEEN lowest_sal
340 AND highest_sal;
341
342 # 、exists （ ）
343 /*
344 EXISTS( )
345 : 1 0
346
347 */
348 SELECT
349 EXISTS (
350 SELECT
351 employee_id
352 FROM
353 employees
354 WHERE
355 salary = 30000
356 );
357
358 # 1：
359 #in
360 SELECT
361 department_name
362 FROM
363 departments d
364 WHERE
365 department_id IN (
366 SELECT
367 department_id
368 FROM
369 employees e
370 );
371
372 #exists
373 SELECT
374 department_name
375 FROM
376 departments d
377 WHERE
378 EXISTS (
379 SELECT
380 *
381 FROM
382 employees e
383 WHERE
384 d.department_id = e.department_id
385 );
386
387 # 2：
388 #in
389 SELECT
390 bo.*
391 FROM
392 boys bo
393 WHERE
394 bo.id NOT IN (
395 SELECT
396 boyfriend_id
397 FROM
398 beauty
399 );
400
401 #EXISTS
402 SELECT
403 bo.*
404 FROM
405 boys bo
406 WHERE
407 NOT EXISTS (
408 SELECT
409 boyfriend_id
410 FROM
411 beauty
412 WHERE
413 bo.id = b.boyfriend_id
414 );
415
416 #1、 zlotkey
417 #① zlotkey
418 SELECT
419 department_id
420 FROM
421 employees
422 WHERE
423 last_name = 'zlotkey' #② =①
424 SELECT
425 last_name,
426 salary
427 FROM
428 employees
429 WHERE
430 department_id = (
431 SELECT
432 department_id
433 FROM
434 employees
435 WHERE
436 last_name = 'Zlotkey'
437 );
438
439 #2 ，
440 #
441 SELECT
442 avg(salary)
443 FROM
444 employees # > ，
445 SELECT
446 last_name,
447 employee_id,
448 salary
449 FROM
450 employees
451 WHERE
452 salary > (
453 SELECT
454 avg(salary)
455 FROM
456 employees
457 );
458
459 #3 ，
460 #
461 SELECT
462 avg(salary),
463 department_id
464 FROM
465 employees
466 GROUP BY
467 department_id #② employees ，
468 SELECT
469 employee_id,
470 last_name,
471 salary,
472 e.department_id
473 FROM
474 employees e
475 INNER JOIN (
476 SELECT
477 avg(salary) ag,
478 department_id
479 FROM
480 employees
481 GROUP BY
482 department_id
483 ) ag_dep ON e.department_id = ag_dep.department_id
484 WHERE
485 salary > ag_dep.ag;
486
487 # u
488 SELECT
489 employee_id,
490 last_name
491 FROM
492 employees e
493 WHERE
494 department_id IN (
495 SELECT DISTINCT
496 department_id
497 FROM
498 employees
499 WHERE
500 last_name LIKE '%u%'
501 );
502
503 #5. 1ocation_id 1700
504 #① location_id 1700
505 SELECT DISTINCT
506 department_id
507 FROM
508 departments
509 WHERE
510 location_id = 1700;
511
512 #② =①
513 SELECT
514 employee_id
515 FROM
516 employees
517 WHERE
518 department_id = ANY (
519 SELECT DISTINCT
520 department_id
521 FROM
522 departments
523 WHERE
524 location_id = 1700
525 );
526
527 #6. King
528 #① King
529 SELECT
530 employee_id
531 FROM
532 employees
533 WHERE
534 last_name = 'K_ing';
535
536 #② manager_id=①
537 SELECT
538 last_name,
539 salary
540 FROM
541 employees
542 WHERE
543 manager_id IN (
544 SELECT
545 employee_id
546 FROM
547 employees
548 WHERE
549 last_name = 'K_ing'
550 );
551
552 #7. ， first_name 1ast_name ， ，
553 #①
554 SELECT
555 MAX(salary)
556 FROM
557 employees #② =①
558 SELECT
559 CONCAT(first_name, last_name) " ， "
560 FROM
561 employees
562 WHERE
563 salary = (
564 SELECT
565 MAX(salary)
566 FROM
567 employees
568 );