问题描述
我将在接下来的几个月内获得第二版我的SQL PUZZLES书的材料
。
1）如果有人有新的拼图，发送给我。你会得到你的名字
的印刷，名望，荣耀和免费副本，取决于我的
出版商。
2）如果有人对旧拼图有新答案，请发送给我。在
特别是，当我写第一版时，大多数产品仍然是使用SQL-86标准的b $ b。这意味着没有OUTER JOIN，没有CTE，没有
派生表，以及有限的标准化功能等等。
我想看到新的答案使用SQL-92和SQL-99 OLAP
扩展。 DB2程序员有更多时间玩其他
SQL产品，所以你有优势。
3）我会发布一些老歌在新闻组和鱼新的
答案。我会用我的
库中的旧IT书来奖励最好的。我会尝试在数据库区域保留我的选择，但是你可以获得一些奇怪的东西，比如说明了
打卡的历史。
4）在我们有标准的OUTER JOIN语法之前，这是十年前我的杂志
专栏中出现的一个启动者，一个老人。
我的原始答案是一个复杂的嵌套噩梦;你能做得更好吗？
你会得到一张人事工资历史的缩略表
创造工资表薪金
（emp_name CHAR（10）NOT NULL，
sal_date DATETIME NOT NULL，
sal_amt DECIMAL（8,2）NOT NULL，
PRIMARY KEY （emp_name，sal_date））;
插入工资价值（''Tom''，''1996-06-20''，500.00）;
插入工资价值（''Tom''，''1996-08-20''，700.00）;
插入工资价值（''Tom''，''1996-10- 20''，800.00）;
插入工资价值（''Tom''，''1996-12-20''，900.00）;
INSERT INTO薪金VALUES（''Dick''，''1996-06-20''，500.00）;
插入工资价值（''Harry''，''1996-07-20''， 500.00）;
插入工资价值（''Harry''，''1996-09-20''，700.00）;
目标是产生查询t帽子会告诉我们当前的工资和每个员工的生效日期，以及他之前的工资和生效日期。如果他是新员工，那么他以前的工资和生效日期显示为NULL。样本结果
数据为：
结果
emp_name curr_date curr_amt prev_date prev_amt
==================================================
''Tom''''1996-12-20''900.00''1996-10-20''800.00
''Harry'' ''1996-09-20''700.00''1996-07-20''500.00
''Dick''''1996-06-20''500.00 NULL NULL
I am getting material for a second edition of my SQL PUZZLES book
together of the next few months.
1) If anyone has a new puzzle, send it to me. You will get your name
in print, fame, glory and perhaps a free copy, depending on my
publisher.
2) If anyone has a new answer to an old puzzle, send it to me. In
particular, when I wrote the first edition, most products were still
using the SQL-86 Standards. That meant no OUTER JOIN, no CTE, no
derived tables, and limited standardized functions and so forth.
I''d like to see new answers with SQL-92 stuff and SQL-99 OLAP
extensions. DB2 programmers have had more time to play with than other
SQL products, so you shoudl have an advantage.
3) I will post some of the oldies on newsgroups and fish for new
answers. I will reward the best ones with an old IT book from my
library. I will try to keep my choice in the database area, but you
could get a copy of something weird, like an illustrated history of
punch cards.
4) Here is a starter, an oldie that appeared in one of my magazine
columns over a decade ago, before we had standard OUTER JOIN syntax.
My original answer was a complex nested nightmare; can you do better?
You are given an abbreviated table of personnel salary history
CREATE TABLE Salaries
(emp_name CHAR(10) NOT NULL,
sal_date DATETIME NOT NULL,
sal_amt DECIMAL (8,2) NOT NULL,
PRIMARY KEY (emp_name, sal_date));
INSERT INTO Salaries VALUES (''Tom'', ''1996-06-20'', 500.00);
INSERT INTO Salaries VALUES (''Tom'', ''1996-08-20'', 700.00);
INSERT INTO Salaries VALUES (''Tom'', ''1996-10-20'', 800.00);
INSERT INTO Salaries VALUES (''Tom'', ''1996-12-20'', 900.00);
INSERT INTO Salaries VALUES (''Dick'', ''1996-06-20'', 500.00);
INSERT INTO Salaries VALUES (''Harry'', ''1996-07-20'', 500.00);
INSERT INTO Salaries VALUES (''Harry'', ''1996-09-20'', 700.00);
The goal is to produce a query that will show us the current salary and
the effective date of each employee, and his immediately previous
salary and effective date. If he is a new employee, then his previous
salary and effective date are shown as NULL. The results of the sample
data are:
Result
emp_name curr_date curr_amt prev_date prev_amt
==================================================
''Tom'' ''1996-12-20'' 900.00 ''1996-10-20'' 800.00
''Harry'' ''1996-09-20'' 700.00 ''1996-07-20'' 500.00
''Dick'' ''1996-06-20'' 500.00 NULL NULL
推荐答案
Mark，
我很乐意做你的接下来的4位客户访问。
只需告诉我姓名和公司信用卡。
您的车当然会在我身上。
干杯
Serge
-
Serge Rielau
DB2解决方案开发
IBM多伦多实验室
Mark,
I would be delighted to do your next 4 customer visits.
Just give me the names and your corporate credit-card.
Your car will be on me of course.
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
阅读乔口头大棒的人们要求在哪里是BITAND ）
和BITOR（）函数或其他暴行。
所以，
与sals（
emp_name，
sal_date，
sal_amt，
relative
）as（
SELECT emp_name，
sal_date，
sal_amt，
rownumber（）结束（按emp_name命令划分sal_date desc）
FROM工资
）
SELECT c.emp_name，
c.sal_date为curr_date，
c.sal_amt为curr_amt，
p.sal_date as prev_date，
p.sal_amt as prev_amt
来自sals c
LEFT OUTER JOIN sals p
ON（c.emp_name = p.emp_name和p.relative = 2）
WHERE c.relative = 1;
EMP_NAME CURR_DATE CURR_AMT PREV_DATE PREV_AMT
---------- ---------- ---------- ---------- ---- ------
Harry 09/20/1996 700.00 07/20/1996 500.00
Tom 12/20/1996 900.00 10/20/1996 800.00
迪克06/20/1996 500.00 - -
3条记录被选中。
I don''t care if Joe is asking us to do his work for him. It''s
the worth 2 minutes of my time for all of the pleasure I''ve derived
reading Joe verbally bludgeon people for asking "where are BITAND()
and BITOR() functions" or other atrocities.
So,
WITH sals (
emp_name,
sal_date,
sal_amt,
relative
) as (
SELECT emp_name,
sal_date,
sal_amt,
rownumber() over (partition by emp_name order by sal_date desc)
FROM salaries
)
SELECT c.emp_name,
c.sal_date as curr_date,
c.sal_amt as curr_amt,
p.sal_date as prev_date,
p.sal_amt as prev_amt
FROM sals c
LEFT OUTER JOIN sals p
ON (c.emp_name = p.emp_name and p.relative = 2)
WHERE c.relative = 1;
EMP_NAME CURR_DATE CURR_AMT PREV_DATE PREV_AMT
---------- ---------- ---------- ---------- ----------
Harry 09/20/1996 700.00 07/20/1996 500.00
Tom 12/20/1996 900.00 10/20/1996 800.00
Dick 06/20/1996 500.00 - -
3 record(s) selected.
这篇关于正在编写的第二版Celko Puzzle书的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！