各位大神,我想实现将人员的所有上级写入一行中分列显示,具体描述如下:
id name manageid(上级ID)
1 A 2
2 A1 3
3 A2 4
4 A3 0 --无上级
7 B 8
8 B1 4
4 A3 0 --无上级条件是:人员.manageid=该人员上级的id想实现人员有N个上级,则自动添加N列来显示上级:
id name shangji1 shangji2 shangji3
1 A A1 A2 A3
7 B B1 A3 null请问怎么写sql语句呢?
id name manageid(上级ID)
1 A 2
2 A1 3
3 A2 4
4 A3 0 --无上级
7 B 8
8 B1 4
4 A3 0 --无上级条件是:人员.manageid=该人员上级的id想实现人员有N个上级,则自动添加N列来显示上级:
id name shangji1 shangji2 shangji3
1 A A1 A2 A3
7 B B1 A3 null请问怎么写sql语句呢?
DROP TABLE #T
GOCREATE TABLE #T
(ID INT,
NAME VARCHAR(10),
MANAGERID INT)INSERT INTO #T
SELECT 1,'A',2 UNION ALL
SELECT 2,'A1',3 UNION ALL
SELECT 3,'A2',4 UNION ALL
SELECT 4,'A3',0 UNION ALL
SELECT 7,'B',8 UNION ALL
SELECT 8,'B1',4 UNION ALL
SELECT 4,'A3',0GODECLARE @SQL VARCHAR(MAX);WITH CTE_1
AS
(SELECT DISTINCT ID,NAME,MANAGERID FROM #T),CTE_2
AS
(SELECT *,ID AS GROUP_ID,1 AS LEVEL FROM CTE_1
UNION ALL
SELECT A.*,B.GROUP_ID,B.LEVEL+1
FROM CTE_1 A
JOIN CTE_2 B ON B.MANAGERID=A.ID) SELECT @SQL=ISNULL(@SQL+',','')+'MAX(CASE WHEN LEVEL='+CAST(LEVEL AS VARCHAR)+' THEN NAME ELSE '''' END) AS LEVEL'+CAST(LEVEL AS VARCHAR)
FROM (SELECT LEVEL FROM CTE_2 WHERE LEVEL<>1 GROUP BY LEVEL) AS ASET @SQL='WITH CTE_1
AS
(SELECT DISTINCT ID,NAME,MANAGERID FROM #T),CTE_2
AS
(SELECT *,ID AS GROUP_ID,1 AS LEVEL FROM CTE_1
UNION ALL
SELECT A.*,B.GROUP_ID,B.LEVEL+1
FROM CTE_1 A
JOIN CTE_2 B ON B.MANAGERID=A.ID)
SELECT ID,NAME,'+@SQL+' FROM CTE_2 GROUP BY ID,NAME'EXEC(@SQL)
大神,您这个语句的sql好像不是我想要的那个:
我想要的结果是这个:
请大神帮忙
大神,您这个语句的sql好像不是我想要的那个:
我想要的结果是这个:
请大神帮忙昨天那个有些问题,你再试试下面的DECLARE @SQL VARCHAR(MAX);WITH CTE_1
AS
(SELECT DISTINCT ID,NAME,MANAGERID FROM #T),CTE_2
AS
(SELECT *,ID AS GROUP_ID,NAME AS GROUP_NAME, 1 AS LEVEL FROM CTE_1
WHERE MANAGERID<>0
UNION ALL
SELECT A.*,B.GROUP_ID,B.GROUP_NAME,B.LEVEL+1
FROM CTE_1 A
JOIN CTE_2 B ON B.MANAGERID=A.ID)
SELECT @SQL=ISNULL(@SQL+',','')+'MAX(CASE WHEN LEVEL='+CAST(LEVEL AS VARCHAR)+' THEN NAME ELSE '''' END) AS LEVEL'+CAST(LEVEL AS VARCHAR)
FROM (SELECT LEVEL FROM CTE_2 WHERE LEVEL<>1 GROUP BY LEVEL) AS ASET @SQL='WITH CTE_1
AS
(SELECT DISTINCT ID,NAME,MANAGERID FROM #T),CTE_2
AS
(SELECT *,ID AS GROUP_ID,NAME AS GROUP_NAME, 1 AS LEVEL FROM CTE_1
WHERE MANAGERID<>0
UNION ALL
SELECT A.*,B.GROUP_ID,B.GROUP_NAME,B.LEVEL+1
FROM CTE_1 A
JOIN CTE_2 B ON B.MANAGERID=A.ID) SELECT GROUP_ID AS ID,GROUP_NAME AS NAME,'+@SQL+' FROM CTE_2 GROUP BY GROUP_ID,GROUP_NAME'EXEC(@SQL)
大神,您这个语句的sql好像不是我想要的那个:
我想要的结果是这个:
请大神帮忙昨天那个有些问题,你再试试下面的DECLARE @SQL VARCHAR(MAX);WITH CTE_1
AS
(SELECT DISTINCT ID,NAME,MANAGERID FROM #T),CTE_2
AS
(SELECT *,ID AS GROUP_ID,NAME AS GROUP_NAME, 1 AS LEVEL FROM CTE_1
WHERE MANAGERID<>0
UNION ALL
SELECT A.*,B.GROUP_ID,B.GROUP_NAME,B.LEVEL+1
FROM CTE_1 A
JOIN CTE_2 B ON B.MANAGERID=A.ID)
SELECT @SQL=ISNULL(@SQL+',','')+'MAX(CASE WHEN LEVEL='+CAST(LEVEL AS VARCHAR)+' THEN NAME ELSE '''' END) AS LEVEL'+CAST(LEVEL AS VARCHAR)
FROM (SELECT LEVEL FROM CTE_2 WHERE LEVEL<>1 GROUP BY LEVEL) AS ASET @SQL='WITH CTE_1
AS
(SELECT DISTINCT ID,NAME,MANAGERID FROM #T),CTE_2
AS
(SELECT *,ID AS GROUP_ID,NAME AS GROUP_NAME, 1 AS LEVEL FROM CTE_1
WHERE MANAGERID<>0
UNION ALL
SELECT A.*,B.GROUP_ID,B.GROUP_NAME,B.LEVEL+1
FROM CTE_1 A
JOIN CTE_2 B ON B.MANAGERID=A.ID) SELECT GROUP_ID AS ID,GROUP_NAME AS NAME,'+@SQL+' FROM CTE_2 GROUP BY GROUP_ID,GROUP_NAME'EXEC(@SQL)
再次感谢大神,请问可以将每个上级的ID也添加到里面,分列显示,例如这样:
select 1 as id,'A' as n,2 as pid
union all
select 2,'A1',3
union all
select 3,'A2',4
union all
select 4,'A3',0
union all
select 7,'B',8
union all
select 8,'B1',4),t1 as (
select *,convert(varchar(max),'') as p from t where pid=0
union all
select t.*,convert(varchar(max),t1.n+(case when t1.p='' then '' else ','+t1.p end)) from t,t1 where t.pid=t1.id
)
select id,n,isnull([1],'') as lv1,isnull([2],'') as lv2,isnull([3],'') as lv3,isnull([4],'') as lv4 from t1 a
cross apply (
select id,convert(varchar(5),value) as value from master.dbo.splitstr(p,',')
) b
pivot (
max(value) for b.id in ([1],[2],[3],[4])
) p
id n lv1 lv2 lv3 lv4
----------- ---- ----- ----- ----- -----
1 A A1 A2 A3
2 A1 A2 A3
3 A2 A3
4 A3
7 B B1 A3
8 B1 A3 (6 行受影响)类似split的sql自定义函数,网上很多,把响应的函数方法名替换,字段替换即可
select 1 as id,'A' as n,2 as pid
union all
select 2,'A1',3
union all
select 3,'A2',4
union all
select 4,'A3',0
union all
select 7,'B',8
union all
select 8,'B1',4),t1 as (
select *,convert(varchar(4),'') as lv1,convert(varchar(4),'') as lv2,convert(varchar(4),'') as lv3,convert(varchar(4),'') as lv4 from t where pid=0
union all
select t.*,convert(varchar(4),t1.n),convert(varchar(4),lv1),convert(varchar(4),lv2),convert(varchar(4),lv3) from t,t1 where t.pid=t1.id
)
select * from t1有限层级的话,直接写也比较方便
大神,您这个语句的sql好像不是我想要的那个:
我想要的结果是这个:
请大神帮忙昨天那个有些问题,你再试试下面的DECLARE @SQL VARCHAR(MAX);WITH CTE_1
AS
(SELECT DISTINCT ID,NAME,MANAGERID FROM #T),CTE_2
AS
(SELECT *,ID AS GROUP_ID,NAME AS GROUP_NAME, 1 AS LEVEL FROM CTE_1
WHERE MANAGERID<>0
UNION ALL
SELECT A.*,B.GROUP_ID,B.GROUP_NAME,B.LEVEL+1
FROM CTE_1 A
JOIN CTE_2 B ON B.MANAGERID=A.ID)
SELECT @SQL=ISNULL(@SQL+',','')+'MAX(CASE WHEN LEVEL='+CAST(LEVEL AS VARCHAR)+' THEN NAME ELSE '''' END) AS LEVEL'+CAST(LEVEL AS VARCHAR)
FROM (SELECT LEVEL FROM CTE_2 WHERE LEVEL<>1 GROUP BY LEVEL) AS ASET @SQL='WITH CTE_1
AS
(SELECT DISTINCT ID,NAME,MANAGERID FROM #T),CTE_2
AS
(SELECT *,ID AS GROUP_ID,NAME AS GROUP_NAME, 1 AS LEVEL FROM CTE_1
WHERE MANAGERID<>0
UNION ALL
SELECT A.*,B.GROUP_ID,B.GROUP_NAME,B.LEVEL+1
FROM CTE_1 A
JOIN CTE_2 B ON B.MANAGERID=A.ID) SELECT GROUP_ID AS ID,GROUP_NAME AS NAME,'+@SQL+' FROM CTE_2 GROUP BY GROUP_ID,GROUP_NAME'EXEC(@SQL)
再次感谢大神,请问可以将每个上级的ID也添加到里面,分列显示,例如这样:DECLARE @SQL VARCHAR(MAX);WITH CTE_1
AS
(SELECT DISTINCT ID,NAME,MANAGERID FROM #T),CTE_2
AS
(SELECT *,ID AS GROUP_ID,NAME AS GROUP_NAME, 1 AS LEVEL FROM CTE_1
WHERE MANAGERID<>0
UNION ALL
SELECT A.*,B.GROUP_ID,B.GROUP_NAME,B.LEVEL+1
FROM CTE_1 A
JOIN CTE_2 B ON B.MANAGERID=A.ID)
SELECT @SQL=ISNULL(@SQL+',','')+'MAX(CASE WHEN LEVEL='+CAST(LEVEL AS VARCHAR)+' THEN NAME ELSE '''' END) AS NAME_LEVEL'+CAST(LEVEL AS VARCHAR)+
+',MAX(CASE WHEN LEVEL='+CAST(LEVEL AS VARCHAR)+' THEN ID ELSE '''' END) AS ID_LEVEL'+CAST(LEVEL AS VARCHAR)
FROM (SELECT LEVEL FROM CTE_2 WHERE LEVEL<>1 GROUP BY LEVEL) AS ASET @SQL='WITH CTE_1
AS
(SELECT DISTINCT ID,NAME,MANAGERID FROM #T),CTE_2
AS
(SELECT *,ID AS GROUP_ID,NAME AS GROUP_NAME, 1 AS LEVEL FROM CTE_1
WHERE MANAGERID<>0
UNION ALL
SELECT A.*,B.GROUP_ID,B.GROUP_NAME,B.LEVEL+1
FROM CTE_1 A
JOIN CTE_2 B ON B.MANAGERID=A.ID) SELECT GROUP_ID AS ID,GROUP_NAME AS NAME,'+@SQL+' FROM CTE_2 GROUP BY GROUP_ID,GROUP_NAME'EXEC(@SQL)
我想要的结果是这个:
请大神帮忙昨天那个有些问题,你再试试下面的DECLARE @SQL VARCHAR(MAX);WITH CTE_1
AS
(SELECT DISTINCT ID,NAME,MANAGERID FROM #T),CTE_2
AS
(SELECT *,ID AS GROUP_ID,NAME AS GROUP_NAME, 1 AS LEVEL FROM CTE_1
WHERE MANAGERID<>0
UNION ALL
SELECT A.*,B.GROUP_ID,B.GROUP_NAME,B.LEVEL+1
FROM CTE_1 A
JOIN CTE_2 B ON B.MANAGERID=A.ID)
SELECT @SQL=ISNULL(@SQL+',','')+'MAX(CASE WHEN LEVEL='+CAST(LEVEL AS VARCHAR)+' THEN NAME ELSE '''' END) AS LEVEL'+CAST(LEVEL AS VARCHAR)
FROM (SELECT LEVEL FROM CTE_2 WHERE LEVEL<>1 GROUP BY LEVEL) AS ASET @SQL='WITH CTE_1
AS
(SELECT DISTINCT ID,NAME,MANAGERID FROM #T),CTE_2
AS
(SELECT *,ID AS GROUP_ID,NAME AS GROUP_NAME, 1 AS LEVEL FROM CTE_1
WHERE MANAGERID<>0
UNION ALL
SELECT A.*,B.GROUP_ID,B.GROUP_NAME,B.LEVEL+1
FROM CTE_1 A
JOIN CTE_2 B ON B.MANAGERID=A.ID) SELECT GROUP_ID AS ID,GROUP_NAME AS NAME,'+@SQL+' FROM CTE_2 GROUP BY GROUP_ID,GROUP_NAME'EXEC(@SQL)
再次感谢大神,请问可以将每个上级的ID也添加到里面,分列显示,例如这样:DECLARE @SQL VARCHAR(MAX);WITH CTE_1
AS
(SELECT DISTINCT ID,NAME,MANAGERID FROM #T),CTE_2
AS
(SELECT *,ID AS GROUP_ID,NAME AS GROUP_NAME, 1 AS LEVEL FROM CTE_1
WHERE MANAGERID<>0
UNION ALL
SELECT A.*,B.GROUP_ID,B.GROUP_NAME,B.LEVEL+1
FROM CTE_1 A
JOIN CTE_2 B ON B.MANAGERID=A.ID)
SELECT @SQL=ISNULL(@SQL+',','')+'MAX(CASE WHEN LEVEL='+CAST(LEVEL AS VARCHAR)+' THEN NAME ELSE '''' END) AS NAME_LEVEL'+CAST(LEVEL AS VARCHAR)+
+',MAX(CASE WHEN LEVEL='+CAST(LEVEL AS VARCHAR)+' THEN ID ELSE '''' END) AS ID_LEVEL'+CAST(LEVEL AS VARCHAR)
FROM (SELECT LEVEL FROM CTE_2 WHERE LEVEL<>1 GROUP BY LEVEL) AS ASET @SQL='WITH CTE_1
AS
(SELECT DISTINCT ID,NAME,MANAGERID FROM #T),CTE_2
AS
(SELECT *,ID AS GROUP_ID,NAME AS GROUP_NAME, 1 AS LEVEL FROM CTE_1
WHERE MANAGERID<>0
UNION ALL
SELECT A.*,B.GROUP_ID,B.GROUP_NAME,B.LEVEL+1
FROM CTE_1 A
JOIN CTE_2 B ON B.MANAGERID=A.ID) SELECT GROUP_ID AS ID,GROUP_NAME AS NAME,'+@SQL+' FROM CTE_2 GROUP BY GROUP_ID,GROUP_NAME'EXEC(@SQL)
大神,您这个语句的sql好像不是我想要的那个:
我想要的结果是这个:
请大神帮忙昨天那个有些问题,你再试试下面的DECLARE @SQL VARCHAR(MAX);WITH CTE_1
AS
(SELECT DISTINCT ID,NAME,MANAGERID FROM #T),CTE_2
AS
(SELECT *,ID AS GROUP_ID,NAME AS GROUP_NAME, 1 AS LEVEL FROM CTE_1
WHERE MANAGERID<>0
UNION ALL
SELECT A.*,B.GROUP_ID,B.GROUP_NAME,B.LEVEL+1
FROM CTE_1 A
JOIN CTE_2 B ON B.MANAGERID=A.ID)
SELECT @SQL=ISNULL(@SQL+',','')+'MAX(CASE WHEN LEVEL='+CAST(LEVEL AS VARCHAR)+' THEN NAME ELSE '''' END) AS LEVEL'+CAST(LEVEL AS VARCHAR)
FROM (SELECT LEVEL FROM CTE_2 WHERE LEVEL<>1 GROUP BY LEVEL) AS ASET @SQL='WITH CTE_1
AS
(SELECT DISTINCT ID,NAME,MANAGERID FROM #T),CTE_2
AS
(SELECT *,ID AS GROUP_ID,NAME AS GROUP_NAME, 1 AS LEVEL FROM CTE_1
WHERE MANAGERID<>0
UNION ALL
SELECT A.*,B.GROUP_ID,B.GROUP_NAME,B.LEVEL+1
FROM CTE_1 A
JOIN CTE_2 B ON B.MANAGERID=A.ID) SELECT GROUP_ID AS ID,GROUP_NAME AS NAME,'+@SQL+' FROM CTE_2 GROUP BY GROUP_ID,GROUP_NAME'EXEC(@SQL)
再次感谢大神,请问可以将每个上级的ID也添加到里面,分列显示,例如这样:DECLARE @SQL VARCHAR(MAX);WITH CTE_1
AS
(SELECT DISTINCT ID,NAME,MANAGERID FROM #T),CTE_2
AS
(SELECT *,ID AS GROUP_ID,NAME AS GROUP_NAME, 1 AS LEVEL FROM CTE_1
WHERE MANAGERID<>0
UNION ALL
SELECT A.*,B.GROUP_ID,B.GROUP_NAME,B.LEVEL+1
FROM CTE_1 A
JOIN CTE_2 B ON B.MANAGERID=A.ID)
SELECT @SQL=ISNULL(@SQL+',','')+'MAX(CASE WHEN LEVEL='+CAST(LEVEL AS VARCHAR)+' THEN NAME ELSE '''' END) AS NAME_LEVEL'+CAST(LEVEL AS VARCHAR)+
+',MAX(CASE WHEN LEVEL='+CAST(LEVEL AS VARCHAR)+' THEN ID ELSE '''' END) AS ID_LEVEL'+CAST(LEVEL AS VARCHAR)
FROM (SELECT LEVEL FROM CTE_2 WHERE LEVEL<>1 GROUP BY LEVEL) AS ASET @SQL='WITH CTE_1
AS
(SELECT DISTINCT ID,NAME,MANAGERID FROM #T),CTE_2
AS
(SELECT *,ID AS GROUP_ID,NAME AS GROUP_NAME, 1 AS LEVEL FROM CTE_1
WHERE MANAGERID<>0
UNION ALL
SELECT A.*,B.GROUP_ID,B.GROUP_NAME,B.LEVEL+1
FROM CTE_1 A
JOIN CTE_2 B ON B.MANAGERID=A.ID) SELECT GROUP_ID AS ID,GROUP_NAME AS NAME,'+@SQL+' FROM CTE_2 GROUP BY GROUP_ID,GROUP_NAME'EXEC(@SQL)要么就把结果插入到一张全局临时表,然后对这张全局临时表进行操作,但是用完后要记得及时把这张表删了。把给@SQL赋值语句的最后一句改成下面的 SELECT GROUP_ID AS ID,GROUP_NAME AS NAME,'+@SQL+' INTO ##A FROM CTE_2 GROUP BY GROUP_ID,GROUP_NAME