关于查询的问题

Question

sq

Answer 1

----------------------------------------------------------------
-- Author  :DBA_HuangZJ(發糞塗牆)
-- Date    :2014-07-24 16:33:14
-- Version:
--      Microsoft SQL Server 2012 - 11.0.5058.0 (X64) 
-- May 14 2014 18:34:29 
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据：[huang]
if object_id('[huang]') is not null drop table [huang]
go 
create table [huang]([员工号] int,[姓名] varchar(1),[岗位] varchar(6),[上级员工号] int)
insert [huang]
select 1,'a','程序猿',6 union all
select 2,'b','测试猿',7 union all
select 3,'c','程序猿',6 union all
select 4,'d','测试猿',7 union all
select 5,'e','程序猿',6 union all
select 6,'f','经理',null union all
select 7,'g','经理',null
--------------开始查询--------------------------
SELECT CASE WHEN id<>1 THEN '' ELSE CAST([上级员工号] AS VARCHAR) END [上级员工号],
CASE WHEN id<>1 THEN '' ELSE CAST([上级姓名] AS VARCHAR) END [上级姓名],
CASE WHEN id<>1 THEN '' ELSE CAST([上级岗位] AS VARCHAR) END [上级岗位],
[员工号],[姓名],[岗位]
FROM (
SELECT a.[员工号] as [上级员工号] ,a.[姓名][上级姓名],a.[岗位] AS [上级岗位],b.[员工号] ,b.[姓名],b.[岗位],ROW_NUMBER()OVER(PARTITION BY a.[员工号] ORDER BY b.[员工号])id
FROM 
(SELECT [员工号],[姓名],[岗位]
FROM huang 
WHERE [上级员工号] IS NULL )a LEFT JOIN (
SELECT [员工号],[姓名],[岗位],[上级员工号]
FROM huang 
WHERE [上级员工号] IS NOT  NULL )b ON a.[员工号]=b.[上级员工号])a
----------------结果----------------------------
/* 
上级员工号                          上级姓名                           上级岗位                           员工号         姓名   岗位
------------------------------ ------------------------------ ------------------------------ ----------- ---- ------
6                              f                              经理                             1           a    程序猿
                                                                                             3           c    程序猿
                                                                                             5           e    程序猿
7                              g                              经理                             2           b    测试猿
                                                                                             4           d    测试猿
*/