查询问题

全部加入At_Work AS W ON E.empno = W.empno AND E.depno = W.depno AND C.caldate = W.working_date 在哪里C.caldate BETWEEN''20031017''和''20031018'' 或C.caldate IS NULL - David Portas ------------ 请只回复新闻组 - HiSome days ago I posted a problem with a query.Thaks to Dave and John, I got a little closer to a solution.Their suggestion was a solution to my problem as I stated it, but the realproblem is a bit more complicated, so I have to expand the original problema bit.The query has to handle unknown number of departments, and a date interval.I want:1) For each day, for each department: a list of (from at_work table) allemployees at work.2) In the same list I want listed (from emp table) all emplyees that belongsto this department, but is not on work this dateHere are new scripts:create table emp(empno int not null,depno int not null)alter table emp add primary key (empno)create table at_work(empno int not null,depno int not null,working_date int not null,duration int not null)alter table at_work add primary key (empno, depno, working_date)alter table at_work add constraint fk_at_work_emp foreign key (empno)references emp (empno)insert into emp (empno, depno) values (1,10)insert into emp (empno, depno) values (2,10)insert into emp (empno, depno) values (3,20)insert into emp (empno, depno) values (4,20)insert into at_work (empno, depno, working_date, duration) values(1,10,''20031017'',5)insert into at_work (empno, depno, working_date, duration) values(3,10,''20031017'',4)insert into at_work (empno, depno, working_date, duration) values(1,10,''20031018'',6)insert into at_work (empno, depno, working_date, duration) values(4,10,''20031018'',7)insert into at_work (empno, depno, working_date, duration) values(1,20,''20031017'',3)insert into at_work (empno, depno, working_date, duration) values(3,20,''20031017'',5)insert into at_work (empno, depno, working_date, duration) values(2,20,''20031018'',6)insert into at_work (empno, depno, working_date, duration) values(3,20,''20031018'',7)insert into at_work (empno, depno, working_date, duration) values(4,20,''20031018'',8)The result set should now look like this:empno depno working_date duration---------------------------------------------1 10 ''20031017'' 53 10 ''20031017'' 42 10 ''20031017'' NULL1 10 ''20031018'' 64 10 ''20031018'' 72 10 ''20031018'' NULL1 20 ''20031017'' 33 20 ''20031017'' 54 20 ''20031017'' NULL2 20 ''20031018'' 63 20 ''20031018'' 74 20 ''20031018'' 8Could someone please help me?Thanks in advanceRegards,Gunnar V?yenliEDB-konsulent asNORWAY 解决方案 "Gunnar V?yenli" <[email protected]> wrote in message news:3f********@news.broadpark.no... Hi Some days ago I posted a problem with a query. Thaks to Dave and John, I got a little closer to a solution. Their suggestion was a solution to my problem as I stated it, but the real problem is a bit more complicated, so I have to expand the original problem a bit. The query has to handle unknown number of departments, and a date interval. I want: 1) For each day, for each department: a list of (from at_work table) all employees at work. 2) In the same list I want listed (from emp table) all emplyees that belongs to this department, but is not on work this date Here are new scripts: create table emp ( empno int not null, depno int not null ) alter table emp add primary key (empno) create table at_work ( empno int not null, depno int not null, working_date int not null, duration int not null ) alter table at_work add primary key (empno, depno, working_date) alter table at_work add constraint fk_at_work_emp foreign key (empno) references emp (empno) insert into emp (empno, depno) values (1,10) insert into emp (empno, depno) values (2,10) insert into emp (empno, depno) values (3,20) insert into emp (empno, depno) values (4,20) insert into at_work (empno, depno, working_date, duration) values (1,10,''20031017'',5) insert into at_work (empno, depno, working_date, duration) values (3,10,''20031017'',4) insert into at_work (empno, depno, working_date, duration) values (1,10,''20031018'',6) insert into at_work (empno, depno, working_date, duration) values (4,10,''20031018'',7) insert into at_work (empno, depno, working_date, duration) values (1,20,''20031017'',3) insert into at_work (empno, depno, working_date, duration) values (3,20,''20031017'',5) insert into at_work (empno, depno, working_date, duration) values (2,20,''20031018'',6) insert into at_work (empno, depno, working_date, duration) values (3,20,''20031018'',7) insert into at_work (empno, depno, working_date, duration) values (4,20,''20031018'',8) The result set should now look like this: empno depno working_date duration --------------------------------------------- 1 10 ''20031017'' 5 3 10 ''20031017'' 4 2 10 ''20031017'' NULL 1 10 ''20031018'' 6 4 10 ''20031018'' 7 2 10 ''20031018'' NULL 1 20 ''20031017'' 3 3 20 ''20031017'' 5 4 20 ''20031017'' NULL 2 20 ''20031018'' 6 3 20 ''20031018'' 7 4 20 ''20031018'' 8 Could someone please help me? Thanks in advance Regards, Gunnar V?yenli EDB-konsulent as NORWAYSELECT COALESCE(W.empno, E.empno) AS empno,COALESCE(W.depno, E.depno) AS depno,COALESCE(W.working_date, D.working_date) AS working_date,W.durationFROM Emp AS ECROSS JOIN(SELECT DISTINCT working_date FROM At_Work) AS DFULL OUTER JOINAt_Work AS WON E.empno = W.empno ANDE.depno = W.depno ANDD.working_date = W.working_dateORDER BY depno, working_date, empnoempno depno working_date duration1 10 20031017 52 10 20031017 NULL3 10 20031017 41 10 20031018 62 10 20031018 NULL4 10 20031018 71 20 20031017 33 20 20031017 54 20 20031017 NULL2 20 20031018 63 20 20031018 74 20 20031018 8Regards,jagSince you want to report on dates which may or may not exist in your table,best create a Calendar table:CREATE TABLE Calendar(caldate DATETIME NOT NULL PRIMARY KEY)Populate with as many years as you need:INSERT INTO Calendar (caldate) VALUES (''20000101'')WHILE (SELECT MAX(caldate) FROM Calendar)<''20101231''INSERT INTO Calendar (caldate)SELECT DATEADD(D,DATEDIFF(D,''19991231'',caldate),(SELECT MAX(caldate) FROM Calendar))FROM CalendarHere''s the query:SELECT COALESCE(W.empno, E.empno) AS empno,COALESCE(W.depno, E.depno) AS depno,COALESCE(C.caldate, W.working_date) AS working_date,W.durationFROM Calendar AS CCROSS JOIN Emp AS EFULL JOIN At_Work AS WON E.empno=W.empno AND E.depno=W.depno AND C.caldate=W.working_dateWHERE C.caldate BETWEEN ''20031017'' AND ''20031018''OR C.caldate IS NULL--David Portas------------Please reply only to the newsgroup-- 这篇关于查询问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！ 阿里云证书，YYDS！