这个SQL语句怎么写？

Question

表Item：
ID_i   UpID_i   Item_ch   Order_i
1      null     质量      null
2      1        质量1     1
3      1        质量2     2
4      null     工期      null
5      4        工期1     1
6      4        工期2     2
7      4        工期3     3表有层次结构，UpID_i标记的是上级ID（是ID_i中的一个值），我想在得到这样的结果：
UpItem_ch  Item_ch Order_i
质量  质量1 1
质量  质量2 2
工期  工期1 1
工期  工期2 2
工期  工期3 3
请问SQL语句怎么写？

Answer 1

如果只有两层SELECT B.Item_ch AS UpItem_ch,A.Item_ch,A.Order_i
FROM (SELECT * FROM Item WHERE UpID_i IS NOT NULL) A
JOIN (SELECT * FROM Item WHERE UpID_i NULL) B
ON A.UpID_i=B.ID_i

Answer 2

--> 测试数据：[Item]
if object_id('[Item]') is not null drop table [Item]
create table [Item]([ID_i] int,[UpID_i] int,[Item_ch] varchar(5),[Order_i] int)
insert [Item]
select 1,null,'质量',null union all
select 2,1,'质量1',1 union all
select 3,1,'质量2',2 union all
select 4,null,'工期',null union all
select 5,4,'工期1',1 union all
select 6,4,'工期2',2 union all
select 7,4,'工期3',3select 
substring(Item_ch,1,patindex('%[1-9]%',Item_ch)-1),
Item_ch,
Order_i 
from [Item]
where UpID_i is not null
---------------------------
质量 质量1 1
质量 质量2 2
工期 工期1 1
工期 工期2 2
工期 工期3 3

Answer 3

if object_id('[Item]') is not null drop table [Item]
go
create table [Item]([ID_i] int,[UpID_i] int,[Item_ch] varchar(5),[Order_i] int)
insert [Item]
select 1,null,'质量',null union all
select 2,1,'质量1',1 union all
select 3,1,'质量2',2 union all
select 4,null,'工期',null union all
select 5,4,'工期1',1 union all
select 6,4,'工期2',2 union all
select 7,4,'工期3',3
 
---查询
SELECT B.Item_ch AS UpItem_ch,A.Item_ch,A.Order_i
FROM (SELECT * FROM Item WHERE UpID_i IS NOT NULL) A
JOIN (SELECT * FROM Item WHERE UpID_i IS NULL) B
ON A.UpID_i=B.ID_i/**
UpItem_ch Item_ch Order_i     
--------- ------- ----------- 
质量        质量1     1
质量        质量2     2
工期        工期1     1
工期        工期2     2
工期        工期3     3（所影响的行数为 5 行）
**/

Answer 4

如果有多层，你这个UpItem_ch要怎么取呢？

Answer 5

select 
substring(Item_ch,1,patindex('%[1-9]%',Item_ch)-1),
Item_ch,
Order_i 
from [Item]
where UpID_i is not null

Answer 6

SELECT B.Item_ch AS UpItem_ch,A.Item_ch,A.Order_i
FROM (SELECT * FROM Item WHERE UpID_i IS NOT NULL) A
JOIN (SELECT * FROM Item WHERE UpID_i IS NULL) B
ON A.UpID_i=B.ID_i
顶！

Answer 7

Mark！  Study！   Up!

Answer 8

----------------------------------------------------------------
-- Author  :SQL77(只为思齐老)
-- Date    :2010-01-21 10:10:49
-- Version:
--      Microsoft SQL Server  2000 - 8.00.194 (Intel X86) 
-- Aug  6 2000 00:57:48 
-- Copyright (c) 1988-2000 Microsoft Corporation
-- Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据：#tb
if object_id('tempdb.dbo.#tb') is not null drop table #tb
go 
create table #tb([ID_i] int,[UpID_i] int,[Item_ch] varchar(5),[Order_i] int)
insert #tb
select 1,null,'质量',null union all
select 2,1,'质量1',1 union all
select 3,1,'质量2',2 union all
select 4,null,'工期',null union all
select 5,4,'工期1',1 union all
select 6,4,'工期2',2 union all
select 7,4,'工期3',3
--------------开始查询--------------------------
select distinct a.*,b.Item_ch,b.Order_i
from 
(
select ID_i,Item_ch from #tb where UpID_i is null
)a
,
#tb b where a.ID_i=b.UpID_i 
----------------结果----------------------------
/* （所影响的行数为 7 行）ID_i        Item_ch Item_ch Order_i     
----------- ------- ------- ----------- 
1           质量      质量1     1
1           质量      质量2     2
4           工期      工期1     1
4           工期      工期2     2
4           工期      工期3     3（所影响的行数为 5 行）
*/

Answer 9

--> 测试数据：[Item]
if object_id('[Item]') is not null drop table [Item]
create table [Item]([ID_i] int,[UpID_i] int,[Item_ch] varchar(5),[Order_i] int)
insert [Item]
select 1,null,'质量',null union all
select 2,1,'质量1',1 union all
select 3,1,'质量2',2 union all
select 4,null,'工期',null union all
select 5,4,'工期1',1 union all
select 6,4,'工期2',2 union all
select 7,4,'工期3',3select 
(select Item_ch from [Item] where ID_i=t.UpID_i) as UpItem_ch,
Item_ch,
Order_i 
from [Item] t
where UpID_i is not null
-------------------------
质量 质量1 1
质量 质量2 2
工期 工期1 1
工期 工期2 2
工期 工期3 3

Answer 10

with tt
as
(
select * ,Code = cast(id_i as varchar(20)) from Item where upid_i is null
union all
select a.*,Code =cast(rtrim(b.id_i)+rtrim(a.id_i) as varchar(20)) from Item a join tt b on a.upid_i = b.id_i
)
select * from tt order by code/**
ID_i        UpID_i      Item_ch Order_i     Code
----------- ----------- ------- ----------- --------------------
1           NULL        质量      NULL        1
2           1           质量1     1           12
3           1           质量2     2           13
4           NULL        工期      NULL        4
5           4           工期1     1           45
6           4           工期2     2           46
7           4           工期3     3           47(7 row(s) affected)
/

Answer 11

with tt 
as 
( 
select * ,Code = cast(id_i as varchar(20)) from Item where upid_i is null 
union all 
select a.*,Code =cast(rtrim(b.id_i)+rtrim(a.id_i) as varchar(20)) from Item a join tt b on a.upid_i = b.id_i 
) 
select * from tt order by code /** 
ID_i        UpID_i      Item_ch Order_i    Code 
----------- ----------- ------- ----------- -------------------- 
1          NULL        质量      NULL        1 
2          1          质量1    1          12 
3          1          质量2    2          13 
4          NULL        工期      NULL        4 
5          4          工期1    1          45 
6          4          工期2    2          46 
7          4          工期3    3          47 (7 row(s) affected) 

Answer 12

with tt 
as 
( 
select * ,Code = cast(id_i as varchar(20)) from Item where upid_i is null 
union all 
select a.*,Code =cast(rtrim(b.id_i)+rtrim(a.id_i) as varchar(20)) from Item a join tt b on a.upid_i = b.id_i 
) 
select * from tt order by code /** 
ID_i        UpID_i      Item_ch Order_i    Code 
----------- ----------- ------- ----------- -------------------- 
1          NULL        质量      NULL        1 
2          1          质量1    1          12 
3          1          质量2    2          13 
4          NULL        工期      NULL        4 
5          4          工期1    1          45 
6          4          工期2    2          46 
7          4          工期3    3          47 (7 row(s) affected) 

Answer 13

----------------------------------------------------------------
-- Author  :fredrickhu(我是小F，向高手学习)
-- Date    :2010-01-21 10:09:54
-- Version:
--      Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) 
-- Nov 24 2008 13:01:59 
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([ID_i] int,[UpID_i] int,[Item_ch] varchar(5),[Order_i] int)
insert [tb]
select 1,null,'质量',null union all
select 2,1,'质量1',1 union all
select 3,1,'质量2',2 union all
select 4,null,'工期',null union all
select 5,4,'工期1',1 union all
select 6,4,'工期2',2 union all
select 7,4,'工期3',3
--------------开始查询--------------------------
;WITH f AS(
 -- 定位点成员
 SELECT * FROM tb where UpID_i is null
 UNION ALL
 SELECT A.*
 FROM tb A, f B
 WHERE A.UpID_i = B.ID_i
)
select
 b.Item_ch as UpItem_ch ,a.Item_ch,a.Order_i
from
 f a, f b
where
a.UpID_i=b.ID_i
GO
----------------结果----------------------------
/* UpItem_ch Item_ch Order_i
--------- ------- -----------
工期        工期1     1
工期        工期2     2
工期        工期3     3
质量        质量1     1
质量        质量2     2(5 行受影响)
*/

Answer 14

表Item： 
ID_i  UpID_i  Item_ch  Order_i 
8      null     最大项    null
1      8        质量      null 
2      1        质量1    1 
3      1        质量2    2 
4      8        工期      null 
5      4        工期1    1 
6      4        工期2    2 
7      4        工期3    3 我想得到下面的结果，注意是只要第二层开始以8为上级ID的的数据，最顶级的就不要了
ID_i UpItem_ch Item_ch Order_i 
2     质量        质量1   1 
3     质量        质量2   2 
5     工期        工期1   1 
6     工期        工期2   2 
7     工期        工期3   3 
这个SQL语句又该怎么写？ 

Answer 15

----------------------------------------------------------------
-- Author  :fredrickhu(我是小F，向高手学习)
-- Date    :2010-01-21 11:03:33
-- Version:
--      Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) 
-- Nov 24 2008 13:01:59 
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([ID_i] int,[UpID_i] int,[Item_ch] varchar(6),[Order_i] int)
insert [tb]
select 8,null,'最大项',null union all
select 1,8,'质量',null union all
select 2,1,'质量1',1 union all
select 3,1,'质量2',2 union all
select 4,8,'工期',null union all
select 5,4,'工期1',1 union all
select 6,4,'工期2',2 union all
select 7,4,'工期3',3
--------------开始查询--------------------------
;WITH f AS(
 -- 定位点成员
 SELECT * FROM tb where UpID_i is null
 UNION ALL
 SELECT A.*
 FROM tb A, f B
 WHERE A.UpID_i = B.ID_i
)
select
 b.Item_ch as UpItem_ch ,a.Item_ch,a.Order_i
from
 f a, f b
where
 a.UpID_i=b.ID_i
and
 a.Order_i is not null
GO
----------------结果----------------------------
/* UpItem_ch Item_ch Order_i
--------- ------- -----------
质量        质量1     1
质量        质量2     2
工期        工期1     1
工期        工期2     2
工期        工期3     3(5 行受影响)
*/

Answer 16

----------------------------------------------------------------
-- Author  :fredrickhu(我是小F，向高手学习)
-- Date    :2010-01-21 11:03:33
-- Version:
--      Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) 
-- Nov 24 2008 13:01:59 
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([ID_i] int,[UpID_i] int,[Item_ch] varchar(6),[Order_i] int)
insert [tb]
select 8,null,'最大项',null union all
select 1,8,'质量',null union all
select 2,1,'质量1',1 union all
select 3,1,'质量2',2 union all
select 4,8,'工期',null union all
select 5,4,'工期1',1 union all
select 6,4,'工期2',2 union all
select 7,4,'工期3',3
--------------开始查询--------------------------
;WITH f AS(
 -- 定位点成员
 SELECT * FROM tb where UpID_i is null
 UNION ALL
 SELECT A.*
 FROM tb A, f B
 WHERE A.UpID_i = B.ID_i
)
select
 a.id_i ,b.Item_ch as UpItem_ch ,a.Item_ch,a.Order_i
from
 f a, f b
where
 a.UpID_i=b.ID_i
and
 a.Order_i is not null
GO
----------------结果----------------------------
/* id_i        UpItem_ch Item_ch Order_i
----------- --------- ------- -----------
2           质量        质量1     1
3           质量        质量2     2
5           工期        工期1     1
6           工期        工期2     2
7           工期        工期3     3(5 行受影响)
*/

Answer 17

--> 测试数据：[Item]
if object_id('[Item]') is not null drop table [Item]
create table [Item]([ID_i] int,[UpID_i] int,[Item_ch] varchar(6),[Order_i] int)
insert [Item]
select 8,null,'最大项',null union all
select 1,8,'质量',null union all
select 2,1,'质量1',1 union all
select 3,1,'质量2',2 union all
select 4,8,'工期',null union all
select 5,4,'工期1',1 union all
select 6,4,'工期2',2 union all
select 7,4,'工期3',3select 
ID_i,
(select Item_ch from [Item] where ID_i=t.UpID_i)
Item_ch,
Order_i
from [Item] t   
where ID_i is not null and UpID_i != 8
--------------------------
2 质量 1
3 质量 2
5 工期 1
6 工期 2
7 工期 3

Answer 18

if object_id('tempdb.dbo.#TB') is not null
 drop table #TB;
gocreate table #TB
([ID_i] int,[UpID_i] int,[Item_ch] varchar(50),[Order_i] int);
insert #TB
select 1,null,'质量',null union all
select 2,1,'质量1',1 union all
select 3,1,'质量2',2 union all
select 4,null,'工期',null union all
select 5,4,'工期1',1 union all
select 6,4,'工期2',2 union all
select 7,4,'工期3',3select * from #tbselect a.id_i, a.item_ch, b.item_ch, b.order_i from #tb a inner join #tb b on a.id_i = b.upid_i/*
质量 质量1 1 1
质量 质量2 2 4
工期 工期1 1 4
工期 工期2 2 4
工期 工期3 3
*/