代码之家 › 专栏 › 技术社区 › Clyde

是否可以执行此SQL查询?

pivot tsql sql-server-2005 sql-server sql

1

Clyde · 技术社区 · 15 年前

Employee  Task   IsCurrent
--------  ----   ---------
Jeff      1      Yes
Jeff      2      No
Jane      1      No
Jane      2      Yes
Jane      3      No
Jane      4      No
Jake      1      Yes

员工有一些任务,其中一个是当前任务。任务编号表示一个顺序——它们按顺序执行任务,在完成最后一个任务时循环回到开始。我想查询下三个任务。我想要这个结果集:

Employee    CurrentTask  NextTask  NextTask2
--------    -----------  --------  ---------
Jeff        1            2         1
Jane        2            3         4
Jake        1            1         1

这可能吗?我正在使用SQL2005

更新:抱歉,我没有明确说明任务编号不一定是连续的——也就是说,Mike实际上可能有任务10、23和223。所以我不能加入寻找下一个连续的任务号。

5 回复 | 直到 15 年前

1

5

OMG Ponies 15 年前

WITH summary AS (
   SELECT t.employee,
          t.task,
          t.iscurrent,
          ROW_NUMBER() OVER (PARTITION BY t.employee
                                 ORDER BY t.task) AS rank
     FROM TABLE t)
   SELECT a.employee,
          a.task AS current_task,
          COALESCE(b.task, y.min_task) AS next_task,
          COALESCE(c.task, y.min_task) AS next_task2
     FROM summary a
     JOIN (SELECT x.employee,
                  MIN(x.task) AS min_task
             FROM TABLE x
         GROUP BY x.employee) y ON y.employee = a.employee
LEFT JOIN summary b ON b.employee = a.employee
                   AND b.rank = a.rank + 1
LEFT JOIN summary c ON c.employee = a.employee
                   AND c.rank = a.rank + 2                   
    WHERE a.iscurrent = 'yes'

就像我前面说的,如果SQLServer支持超前/滞后函数,会更容易。。。

2

1

a_horse_with_no_name 15 年前

如果您只对下一个和之后的一个感兴趣,可以使用以下方法:

SELECT t.employee,
       t.task AS current_task,
       t1.task AS next_task, 
       t2.task AS next_task_2
FROM tasks t
  LEFT JOIN tasks t1 ON t1.task = t.task + 1 AND t1.employee = t.employee
  LEFT JOIN tasks t2 ON t2.task = t1.task + 1 AND t2.employee = t1.employee
WHERE t.isCurrent = 'yes'

3

0

CaffGeek 15 年前

如果存储过程包含以下步骤,应该不会太糟糕

使用Employee、CurrentTask、NextTask和NextTask2列创建临时表这是一个简单的SQL语句。。。你可以把第一步和第二步结合起来。

INSERT INTO #tempTable (Employee, CurrentTask) VALUES Employee, Task WHERE IsCurrent = 'Yes'

TOP 1 和 Order By Task ASC

4

0

cichy 15 年前

SELECT e.Employee, e.Task AS CurrentTask, 
(
    SELECT TOP 1 e1.Task FROM dbo.Employees AS e1 WHERE e1.Employee=e.Employee AND e1.Task > e.Task ORDER BY e1.Task ASC
) AS NextTask,
(
    SELECT TOP 1 e2.Task FROM dbo.Employees AS e2 WHERE e2.Employee=e.Employee AND e2.Task > (
        SELECT TOP 1 e1.Task FROM dbo.Employees AS e1 WHERE e1.Employee=e.Employee AND e1.Task > e.Task ORDER BY e1.Task ASC
    ) ORDER BY e2.Task ASC
) AS NextTask2
FROM dbo.Employees AS e
WHERE e.IsCurrent = 'Yes'

另外,也许PIVOT可以做到,但我并不擅长。

5

0

egrunin 15 年前

这是一个由两部分组成的问题。首先,必须解决模数问题(按顺序列出任务,从当前任务开始):

步骤1:构建一个结果如下的查询(我已经根据您的编辑修改了数据)

Employee  IsCurrent OriginalTask [Task+MaxTask] as Task
--------  ----      ------------ ---------------------- 
Jeff      Yes       37           15
Jeff      No        44           22
Jane      No        55           13
Jane      Yes       67           25
Jane      No        73           31
Jane      No        84           42
Jake      Yes       38           19

步骤2:与原始查询进行联合

Employee  IsCurrent Task OriginalTask
--------  ----      ---- ------------
Jeff      Yes       15   15
Jeff      No        22   22
Jeff      Yes       37   15
Jeff      No        44   22
Jane      No        13   13
Jane      Yes       25   25
Jane      No        31   31
Jane      No        42   42
Jane      No        55   13
Jane      Yes       67   25
Jane      No        73   31
Jane      No        84   42
Jake      Yes       19   19
Jake      Yes       38   19

Employee  IsCurrent Task OriginalTask
--------  ----      ---- ------------ 
Jeff      Yes       15   15
Jeff      No        22   22
Jane      Yes       25   25
Jane      No        31   31
Jane      No        42   42
Jane      No        55   13
Jake      Yes       19   19