高手,挑战下SQL语句.看似无规律,实际有规律(不局限于一条SQL)

Question

CREATE TABLE t(
orgid varchar(20),orgname  varchar(30),orgtype  int,part_orgid VARCHAR(20))INSERT INTO t
SELECT 'IT','IT部门',3,'ST' union all           
SELECT 'ST' ,   ' XX集团'  , 1  ,     '-'union all       
SELECT 'ST1'  ,  '第1工厂' , 2 ,     'ST'union all       
SELECT 'ST2' ,   '第2工厂' , 2 ,     'ST'union all       
SELECT 'IT01' ,  '硬件科' ,  4 ,     'IT'union all       
SELECT 'IT0201',  '软件班',   5  ,  'IT'/*orgtype 1代表集团 2代表工厂 3代表 部门 4代表科 5代表班重点说明：IT部门的直接上级是集团,但由于他是部门，中间无工厂级别的，直接为空即可！
即为：跨级别的中间组织则为空。
现有一需求:
生成如下格式：*/
集团ID, 集团名称,工厂ID，工厂名称，部门ID, 部门名称， 科ID,  科名称， 班ID,   班名称
 ST      XX集团    ST1      第1工厂   
 ST      XX集团                     IT       IT部门     IT01   硬件科  
 ST      XX集团                     IT       IT部门                    IT0201   软件班
 ST      XX集团    ST2      第2工厂   --orgid为1放在第1,2列, orgid为2放在第3,4列, orgid为3放在第5,6列,orgid为4放在第7,8列,orgid为5放在第9,10列,

Answer 1

IT的那两行没放在第1工厂下面.IT是直属于集团公司的.

Answer 2

这个东西一条查询暂时没思路 ，不过可以FULL JOIN生成临时表再UPDATE

Answer 3

也可以写个自定义函数，输入5个ID和输出标识符，输出对应的ID或NAME

Answer 4

select (case when orgtype=1 then orgid else '' end) as a1,
   (case when orgtype=1  then orgname else '' end) as a2,
   (case when orgtype=2 then orgid else '' end) as a3,
   (case when orgtype=2 then orgname else '' end) as a4,
   (case when orgtype=3 then orgid else '' end) as a5,
   (case when orgtype=3 then orgname else '' end) as a6,
   (case when orgtype=4 then orgid else '' end) as a7,
   (case when orgtype=4 then orgname else '' end) as a8,
   (case when orgtype=5 then orgid else '' end) as a9,
   (case when orgtype=5 then orgname else '' end) as a10
from t 
只想到这样 希望有帮助！大伙 别拍砖啊！

Answer 5

--???
CREATE TABLE tt12(
orgid varchar(20),orgname  varchar(30),orgtype  int,part_orgid VARCHAR(20))
INSERT INTO tt12
SELECT 'IT','IT部门',3,'ST' union all           
SELECT 'ST' ,   ' XX集团'  , 1  ,     '-'union all       
SELECT 'ST1'  ,  '第1工厂' , 2 ,     'ST'union all       
SELECT 'ST2' ,   '第2工厂' , 2 ,     'ST'union all       
SELECT 'IT01' ,  '硬件科' ,  4 ,     'IT'union all       
SELECT 'IT0201',  '软件班',   5  ,  'IT'SELECT  aa.orgid 集团ID,
aa.orgname 集团名称,
CASE WHEN bb.orgid IS NULL THEN aa.borgid ELSE '' END 工厂ID,
CASE WHEN bb.orgid IS NULL THEN aa.borgname ELSE '' END 工厂名称,
CASE WHEN bb.orgid IS NOT NULL THEN aa.borgid ELSE '' END 部门ID,
CASE WHEN bb.orgid IS NOT NULL THEN aa.borgname ELSE '' END 部门名称,
CASE WHEN bb.orgid IS NOT NULL AND bb.borgtype=4 THEN bb.borgid ELSE '' END 科ID,
CASE WHEN bb.orgid IS NOT NULL AND bb.borgtype=4 THEN bb.borgname ELSE '' END 科名称,
CASE WHEN bb.orgid IS NOT NULL AND bb.borgtype=5 THEN bb.borgid ELSE '' END 班ID,
CASE WHEN bb.orgid IS NOT NULL AND bb.borgtype=5 THEN bb.borgname ELSE '' END 班名称
 FROM 
(SELECT a.*,b.orgid borgid, b.orgname borgname,b.orgtype borgtype,b.part_orgid bpart_orgid FROM tt12 a,tt12 b 
WHERE a.orgid=b.part_orgid AND a.orgid='st' ) aa
left  JOIN 
(SELECT a.*,b.orgid borgid, b.orgname borgname,b.orgtype borgtype,b.part_orgid bpart_orgid FROM tt12 a,tt12 b 
 WHERE a.orgid=b.part_orgid AND a.orgid='it') bb
on aa.borgname=bb.orgname
/*集团ID                 集团名称                           工厂ID                 工厂名称                           部门ID                 部门名称                           科ID                  科名称                            班ID                  班名称                            
-------------------- ------------------------------ -------------------- ------------------------------ -------------------- ------------------------------ -------------------- ------------------------------ -------------------- ------------------------------ 
ST                    XX集团                                                                              IT                   IT部门                           IT01                 硬件科                                                 
ST                    XX集团                                                                              IT                   IT部门                                                                               IT0201               软件班
ST                    XX集团                          ST1                  第1工厂                                                                                                                                                        
ST                    XX集团                          ST2                  第2工厂                                                                                                                                                        （所影响的行数为 4 行）
*/

Answer 6

--静态写法，支持5层深度
--如深度超过5，继续嵌套
--如深度不定，需动态展BOM来做，最好用存储过程/*測試數據*/
CREATE TABLE t(
orgid varchar(20),orgname  varchar(30),orgtype  int,part_orgid VARCHAR(20))INSERT INTO t
SELECT 'IT','IT部門',3,'ST' union all           
SELECT 'ST' ,   ' XX集團'  , 1  ,     '-'union all       
SELECT 'ST1'  ,  '第1工廠' , 2 ,     'ST'union all       
SELECT 'ST2' ,   '第2工廠' , 2 ,     'ST'union all       
SELECT 'IT01' ,  '硬件科' ,  4 ,     'IT'union all       
SELECT 'IT0201',  '軟體班',   5  ,  'IT'  union allselect  'RD','RD部門',3,'XY1'  union all
SELECT 'XY' ,   ' XY集團'  , 1  ,     '-'union all       
SELECT 'XY1'  ,  '第x工廠' , 2 ,     'XY'union all       
SELECT 'XY2' ,   '第y工廠' , 2 ,     'XY'union all       
SELECT 'RD01' ,  '研發科' ,  4 ,     'RD'union all       
SELECT 'RD0201',  '硬體班',   5  ,  'RD01' GO
/*静态嵌套5层及以内*/
select 
isnull( id1,'') as [集团ID], isnull(name1,'') as [集团名称],
isnull(isnull(X4.id2, case when E.orgtype=2 then E.orgid end),'') as [工厂ID], isnull(isnull(X4.name2,case when E.orgtype=2 then E.orgname end),'') as [工厂名称],
isnull(isnull(X4.id3, case when E.orgtype=3 then E.orgid end ),'') as [部门ID],isnull( isnull(X4.name3,case when E.orgtype=3 then E.orgname end),'') as [部门名称],
isnull(isnull(X4.id4, case when E.orgtype=4 then E.orgid end ),'') as [科ID],isnull( isnull(X4.name4,case when E.orgtype=4 then E.orgname end),'') as [科名称],
isnull(isnull(X4.id5, case when E.orgtype=5 then E.orgid end ),'') as [班ID],isnull( isnull(X4.name5,case when E.orgtype=5 then E.orgname end) ,'')as [班名称]
from
(
select 
  id1,name1, D.orgid as tmp_id,D.orgname as tmp_name , D.orgtype as type,D.part_orgid as pid,
isnull(X3.id2, case when D.orgtype=2 then D.orgid end) as id2, isnull(X3.name2,case when D.orgtype=2 then D.orgname end) as name2,
isnull(X3.id3, case when D.orgtype=3 then D.orgid end ) as id3, isnull(X3.name3,case when D.orgtype=3 then D.orgname end) as name3,
isnull(X3.id4, case when D.orgtype=4 then D.orgid end ) as id4, isnull(X3.name4,case when D.orgtype=4 then D.orgname end) as name4,
isnull(X3.id5, case when D.orgtype=5 then D.orgid end ) as id5, isnull(X3.name5,case when D.orgtype=5 then D.orgname end) as name5
from
(
select  
    id1,name1, C.orgid as tmp_id,C.orgname as tmp_name , C.orgtype as type,C.part_orgid as pid,
isnull(X2.id2, case when C.orgtype=2 then C.orgid end) as id2, isnull(X2.name2,case when C.orgtype=2 then C.orgname end) as name2,
isnull(X2.id3, case when C.orgtype=3 then C.orgid end ) as id3, isnull(X2.name3,case when C.orgtype=3 then C.orgname end) as name3,
isnull(X2.id4, case when C.orgtype=4 then C.orgid end ) as id4, isnull(X2.name4,case when C.orgtype=4 then C.orgname end) as name4,
isnull(X2.id5, case when C.orgtype=5 then C.orgid end ) as id5, isnull(X2.name5,case when C.orgtype=5 then C.orgname end) as name5
from
(
select   X1.orgid as id1,X1.orgname as name1, B.orgid as tmp_id,B.orgname as tmp_name , B.orgtype as type,B.part_orgid as pid,
case when B.orgtype=2 then B.orgid end as id2,
case when B.orgtype=2 then B.orgname end as name2,
case when B.orgtype=3 then B.orgid end as id3,
case when B.orgtype=3 then B.orgname end as name3,
case when B.orgtype=4 then B.orgid end as id4,
case when B.orgtype=4 then B.orgname end as name4,
case when B.orgtype=5 then B.orgid end as id5,
case when B.orgtype=5 then B.orgname end as name5
from
  (select * from t where orgtype=1) X1
left join t B
  on X1.orgid=B.part_orgid
) X2
left join t C
  on X2.tmp_id=C.part_orgid
) X3
left join t D
  on X3.tmp_id=D.part_orgid
) X4
left join t E
 on X4.tmp_id=E.part_orgid/*结果*/
/*
集团ID                 集团名称                           工厂ID                 工厂名称                           部门ID                 部门名称                           科ID                  科名称                            班ID                  班名称                            
-------------------- ------------------------------ -------------------- ------------------------------ -------------------- ------------------------------ -------------------- ------------------------------ -------------------- ------------------------------ 
ST                    XX集團                                                                              IT                   IT部門                           IT01                 硬件科                                                 
ST                    XX集團                                                                              IT                   IT部門                                                                               IT0201               軟體班
ST                    XX集團                          ST1                  第1工廠                                                                                                                                                        
ST                    XX集團                          ST2                  第2工廠                                                                                                                                                        
XY                    XY集團                          XY1                  第x工廠                           RD                   RD部門                           RD01                 研發科                            RD0201               硬體班
XY                    XY集團                          XY2                  第y工廠                                                                                                                                                        */
GO--刪除測試環境
drop table T

Answer 7

--建立测试环境
IF OBJECT_ID('t') IS NOT NULL  DROP TABLE  t
GO
CREATE TABLE t(
orgid varchar(20),orgname  varchar(30),orgtype  int,part_orgid VARCHAR(20))INSERT INTO t
SELECT 'IT','IT部门',3,'ST' union all           
SELECT 'ST' ,   ' XX集团'  , 1  ,     '-'union all       
SELECT 'ST1'  ,  '第1工厂' , 2 ,     'ST'union all       
SELECT 'ST2' ,   '第2工厂' , 2 ,     'ST'union all       
SELECT 'IT01' ,  '硬件科' ,  4 ,     'IT'union all       
SELECT 'IT0201',  '软件班',   5  ,  'IT'
go
/*orgtype 1代表集团 2代表工厂 3代表 部门 4代表科 5代表班重点说明：IT部门的直接上级是集团,但由于他是部门，中间无工厂级别的，直接为空即可！
即为：跨级别的中间组织则为空。
现有一需求:
生成如下格式：
集团ID, 集团名称,工厂ID，工厂名称，部门ID, 部门名称， 科ID,  科名称， 班ID,   班名称
 ST      XX集团    ST1      第1工厂   
 ST      XX集团                     IT       IT部门     IT01   硬件科  
 ST      XX集团                     IT       IT部门                    IT0201   软件班
 ST      XX集团    ST2      第2工厂   --orgid为1放在第1,2列, orgid为2放在第3,4列, orgid为3放在第5,6列,orgid为4放在第7,8列,orgid为5放在第9,10列,
*/
IF OBJECT_ID('p_test') IS NOT NULL  DROP proc  p_test
GO
create proc p_test
as
begin
declare @orgid varchar(20),@orgname varchar(30),@orgtype int,@part_orgid VARCHAR(20)
declare @l int
    
create table #tb(集团ID varchar(20), 集团名称 varchar(30),
工厂ID varchar(20),工厂名称 varchar(30),
部门ID varchar(20), 部门名称 varchar(30), 
科ID varchar(20),  科名称 varchar(30), 
班ID varchar(20),   班名称 varchar(30),
part_orgid varchar(20),level int)
declare cur1 cursor for select * from t a where not exists(select 1 from t b where b.part_orgid=a.orgid)
open cur1
fetch next from cur1 into @orgid,@orgname,@orgtype,@part_orgid
while @@fetch_status=0
begin
set @l=0  
insert #tb 
select  case when orgtype=1 then a.orgid end,case when orgtype=1 then a.orgname end,
case when orgtype=2 then a.orgid end,case when orgtype=2 then a.orgname end,
case when orgtype=3 then a.orgid end,case when orgtype=3 then a.orgname end,
case when orgtype=4 then a.orgid end,case when orgtype=4 then a.orgname end,
case when orgtype=5 then a.orgid end,case when orgtype=5 then a.orgname end,
a.part_orgid,@l 
from t a where orgid=@orgid
while @@rowcount>0
begin  
set @l=@l+1
update b 
set b.集团ID = case when orgtype=1 then a.orgid else b.集团ID end,b.集团名称 = case when orgtype=1 then a.orgname else b.集团名称 end,
b.工厂ID = case when orgtype=2 then a.orgid else b.工厂ID end,b.工厂名称 = case when orgtype=2 then a.orgname else b.工厂名称 end,
b.部门ID = case when orgtype=3 then a.orgid else b.部门ID end,b.部门名称 = case when orgtype=3 then a.orgname else b.部门名称 end,
b.科ID = case when orgtype=4 then a.orgid else b.科ID end,b.科名称 = case when orgtype=4 then a.orgname else b.科名称 end,
b.班ID = case when orgtype=5 then a.orgid else b.班ID end,b.班名称 = case when orgtype=5 then a.orgname else b.班名称 end,
b.part_orgid=a.part_orgid
from t as a,#tb as b  
where a.orgid=b.part_orgid
end fetch next from cur1 into @orgid,@orgname,@orgtype,@part_orgid
end
close cur1
deallocate cur1
select 集团ID, 集团名称,工厂ID,工厂名称,部门ID,部门名称,科ID, 科名称,班ID,班名称 from #tb
end
go
--执行
exec p_test
--结果
/*
集团ID                 集团名称                           工厂ID                 工厂名称                           部门ID                 部门名称                           科ID                  科名称                            班ID                  班名称
-------------------- ------------------------------ -------------------- ------------------------------ -------------------- ------------------------------ -------------------- ------------------------------ -------------------- ------------------------------
ST                    XX集团                          ST1                  第1工厂                           NULL                 NULL                           NULL                 NULL                           NULL                 NULL
ST                    XX集团                          ST2                  第2工厂                           NULL                 NULL                           NULL                 NULL                           NULL                 NULL
ST                    XX集团                          NULL                 NULL                           IT                   IT部门                           IT01                 硬件科                            NULL                 NULL
ST                    XX集团                          NULL                 NULL                           IT                   IT部门                           NULL                 NULL                           IT0201               软件班(4 行受影响)
*/
排序实在搞不定

Answer 8

IF OBJECT_ID('T') IS NOT NULL DROP TABLE T
GO
CREATE TABLE t(
orgid varchar(20),orgname  varchar(30),orgtype  int,part_orgid VARCHAR(20))INSERT INTO t
SELECT 'IT','IT部门',3,'ST' union all           
SELECT 'ST' ,   ' XX集团'  , 1  ,     '-'union all       
SELECT 'ST1'  ,  '第1工厂' , 2 ,     'ST'union all       
SELECT 'ST2' ,   '第2工厂' , 2 ,     'ST'union all       
SELECT 'IT01' ,  '硬件科' ,  4 ,     'IT'union all       
SELECT 'IT0201',  '软件班',   5  ,  'IT'
GO
--DECLARE @ID1 varchar(20),@ID2 varchar(20),@ID3 varchar(20),@ID4 varchar(20),@ID5 varchar(20),@TYPE INT,@OUTID VARCHAR(20)
--SELECT @ID1=NULL,@ID2=NULL,@ID3=NULL,@ID4=NULL,@ID5='IT',@TYPE=1
--IF @TYPE=(SELECT ORGTYPE FROM t WHERE )
--SELECT @OUTID=ORGID
--FROM t
--WHERE /*orgtype 1代表集团 2代表工厂 3代表 部门 4代表科 5代表班重点说明：IT部门的直接上级是集团,但由于他是部门，中间无工厂级别的，直接为空即可！
即为：跨级别的中间组织则为空。
现有一需求:
生成如下格式：
集团ID, 集团名称,工厂ID，工厂名称，部门ID, 部门名称， 科ID,  科名称， 班ID,   班名称
 ST      XX集团    ST1      第1工厂   
 ST      XX集团                     IT       IT部门     IT01   硬件科  
 ST      XX集团                     IT       IT部门                    IT0201   软件班
 ST      XX集团    ST2      第2工厂   
*/
--orgid为1放在第1,2列, orgid为2放在第3,4列, orgid为3放在第5,6列,orgid为4放在第7,8列,orgid为5放在第9,10列,SELECT 
[集团ID]=T6.orgid 
,[集团集团名称]=T6.orgname 
,[工厂ID]=CASE WHEN T7.orgtype=2 THEN T7.orgid ELSE '' END
,[工厂名称]=CASE WHEN T7.orgtype=2 THEN T7.orgNAME ELSE '' END
,[部门ID]=CASE WHEN T8.orgtype=3 THEN T8.orgid ELSE '' END
,[部门名称]=CASE WHEN T8.orgtype=3 THEN T8.orgNAME ELSE '' END
,[科ID]=COALESCE(T4.ORGID,'')
,[科名称]=COALESCE(T4.ORGNAME,'')
,[科ID]=COALESCE(T5.ORGID,'')
,[科名称]=COALESCE(T5.ORGNAME,'')
FROM (
SELECT orgid,orgname  
FROM T WHERE orgtype=1
) T1
FULL JOIN (
SELECT orgid,orgname,part_orgid 
FROM T WHERE orgtype=2
) T2 ON T1.orgid=T2.part_orgid 
FULL JOIN (
SELECT orgid,orgname,part_orgid 
FROM T WHERE orgtype=3
) T3 ON  T2.orgid=T3.part_orgid 
FULL JOIN (
SELECT orgid,orgname,part_orgid 
FROM T WHERE orgtype=4
) T4 ON  T3.orgid=T4.part_orgid 
FULL JOIN (
SELECT orgid,orgname,part_orgid 
FROM T WHERE orgtype=5
) T5 ON  T4.orgid=T5.part_orgid 
LEFT JOIN t T6 ON COALESCE(T1.ORGID,T3.PART_ORGID
,(SELECT PART_ORGID FROM T WHERE ORGID=T4.PART_ORGID)
,(SELECT PART_ORGID FROM T WHERE ORGID=T5.PART_ORGID)
)=T6.orgid 
LEFT JOIN t T7 ON COALESCE(T2.ORGID,T4.PART_ORGID
,(SELECT PART_ORGID FROM T WHERE ORGID=T5.PART_ORGID)
)=T7.orgid 
LEFT JOIN t T8 ON COALESCE(T3.ORGID,T5.PART_ORGID)=T8.orgid 
/*
ST  XX集团 ST1 第1工厂
ST  XX集团 ST2 第2工厂
ST  XX集团 IT IT部门 IT01 硬件科
ST  XX集团 IT IT部门 IT0201 软件班
*/

Answer 9

如果你的表中数据都如你的示例数据这种规律，那就把我的语句改一部分：
AND a.orgid='st'  改成 and a.orgtype=1
AND a.orgid='it'  改成 and a.orgtype=3 至于还有别的规律的数据，那就另考虑

Answer 10

select a.orgid,a.orgname,
   (case when c.orgtype=2 then c.orgid else '' end) as a3,
   (case when c.orgtype=2 then c.orgname else '' end) as a4,
   b.orgid,
   b.orgname,
   (case when c.orgtype=4 then c.orgid else '' end) as a7,
   (case when c.orgtype=4 then c.orgname else '' end) as a8,
   (case when c.orgtype=5 then c.orgid else '' end) as a9,
   (case when c.orgtype=5 then c.orgname else '' end) as a10
from t c,
 (select * from t where orgtype=1) a,
 (select * from t where orgtype=3) b
where c.orgtype not in(1,3)