Name week
张三 1
张三 2
张三 3
王五 4
赵六 5
李四 6
王五 7
解释:表名为employee。张三在周一、周二、周三上了班,李四在周六上了班……。
现在要求查询出每个员工在该周中哪几天没有上班?
张三 1
张三 2
张三 3
王五 4
赵六 5
李四 6
王五 7
解释:表名为employee。张三在周一、周二、周三上了班,李四在周六上了班……。
现在要求查询出每个员工在该周中哪几天没有上班?
FROM (SELECT n.NAME, w.week
FROM (SELECT ROWNUM week
FROM DUAL
CONNECT BY ROWNUM <= 7) w,
(SELECT dinsict NAME
FROM a) n) w1,
a
WHERE w1.NAME = a.NAME(+) AND w1.week = a.week(+) AND a.NAME IS NULL
ORDER BY 1, 2
(SELECT '张三' NAME, 1 week
FROM DUAL
UNION ALL
SELECT '张三' NAME, 2
FROM DUAL
UNION ALL
SELECT '张三' NAME, 3
FROM DUAL
UNION ALL
SELECT '王五' NAME, 4
FROM DUAL
UNION ALL
SELECT '赵六' NAME, 5
FROM DUAL
UNION ALL
SELECT '李四' NAME, 6
FROM DUAL
UNION ALL
SELECT '王五' NAME, 7
FROM DUAL)
SELECT w1.NAME, w1.week
FROM (SELECT n.NAME, w.week
FROM (SELECT ROWNUM week
FROM DUAL
CONNECT BY ROWNUM <= 7) w,
(SELECT DISTINCT NAME
FROM a) n) w1,
a
WHERE w1.NAME = a.NAME(+) AND w1.week = a.week(+) AND a.NAME IS NULL
ORDER BY 1, 2结果
NAME WEEK李四 1
李四 2
李四 3
李四 4
李四 5
李四 7
王五 1
王五 2
王五 3
王五 5
王五 6
张三 4
张三 5
张三 6
张三 7
赵六 1
赵六 2
赵六 3
赵六 4
赵六 6
赵六 7
from (select t.*, 7 - count(1) over(partition by name) c from employee t)
group by name, c
SELECT w1.NAME, w1.week
FROM (SELECT n.NAME, w.week
FROM (SELECT ROWNUM week
FROM DUAL
CONNECT BY ROWNUM <= 7) w,
(SELECT DISTINCT NAME
FROM employee) n) w1,
employee a
WHERE w1.NAME = a.NAME(+) AND w1.week = a.week(+) AND a.NAME IS NULL
ORDER BY 1, 2
(SELECT DISTINCT A.NAME,B.WEEK FROM EMPLOYEE A,EMPLOYEE B) A
WHERE NOT EXISTS(SELECT 1 FROM EMPLOYEE B WHERE A.NAME=B.NAME AND A.WEEK=B.WEEK)
SELECT A.* FROM
(SELECT DISTINCT A.NAME,B.WEEK FROM EMPLOYEE A,EMPLOYEE B) A
WHERE NOT EXISTS(SELECT 1 FROM EMPLOYEE B WHERE A.NAME=B.NAME AND A.WEEK=B.WEEK)
李四 1
李四 2
李四 3
李四 4
李四 5
李四 7
王五 1
王五 2
王五 3
王五 5
王五 6
张三 4
张三 5
张三 6
张三 7
赵六 1
赵六 2
赵六 3
赵六 4
赵六 6
赵六 7