tailieunhanh - SQL Puzzles & Answers- P3

Tham khảo tài liệu 'sql puzzles & answers- p3', công nghệ thông tin, cơ sở dữ liệu phục vụ nhu cầu học tập, nghiên cứu và làm việc hiệu quả | 62 PUZZLE 15 FIND THE LAST TWO SALARIES employee. If the programmers were not so lazy you could pass this table to them and let them format it for the report. Answer 2 The real problem is harder. One way to do this within the limits of SQL-89 is to break the problem into two cases 1. Employees with only one salary action 2. Employees with two or more salary actions We know that every employee has to fall into one and only one of those cases. One solution is to UNION both of the sets together SELECT FROM Salaries AS S0 Salaries AS S1 WHERE AND SELECT MAX FROM Salaries AS S2 WHERE AND SELECT MAX FROM Salaries AS S3 WHERE AND UNION ALL SELECT MAX MAX NULL NULL FROM Salaries AS S4 GROUP BY HAVING COUNT 1 emp_name sal_date sal_ amt sal_date sal_amt Tom 1996-12-20 1996-10-20 Harry 1996-09-20 1996-07-20 Dick 1996-06-20 NULL NULL Please purchase PDF Split-Merge on to remove this watermark. PUZZLE 15 FIND THE LAST TWO SALARIES 63 DB2 programmers will recognize this as a version of the OUTER JOIN done without an SQL-92 standard OUTER JOIN operator. The first SELECT statement is the hardest. It is a self-join on the Salaries table with copy S0 being the source for the most recent salary information and copy S1 the source for the next most recent information. The second SELECT statement is simply a grouped query that locates the employees with one row. Since the two result sets are disjoint we can use the UNION ALL instead of a UNION operator to save an extra sorting operation. Answer 3 I got several answers in response to my challenge for a better solution to this puzzle. Richard Romley of Smith Barney sent in the following SQL-92 solution. It takes advantage of the subquery table