关于一张表的sql语句

[qq_36593316]

我想查询
id name type super
1 admin 1 -1
2 user2 1 1
3 user3 1 1
4 user4 1 3
5 用户 0 4
就是这样的一个结构
所有用户（type =0的）
然后在super上本来是4的，但是我想把他换成user4
这样描述应该可以了吧，请问这样的sql语句怎么搞，在1条里面不会..

[吉普赛的歌]

不清楚你意思， 你把你想实现的， 用多条写出来吧。 这样我们用一条实现才有参考。

[qq_36593316]

//id,名字,账号类型，账号上一级部门
id name type super
1 admin  1    -1
2 user2  1     1
3 user3  1     1
4 user4  1     3
5 用户    0    4
select  id ,username,type,spr  from user where type=0
我查询出了全部的用户
然后里面的数据是
5  用户    0     4
其实我想要的数据是
5  用户    0     user4

[二月十六]

user4是个什么东西？

[qq_36593316]

引用 3 楼 sinat_28984567 的回复:

user4是个什么东西？

user4是他上级部门的名字

[二月十六]

这样？

--测试数据
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([id] int,[name] nvarchar(25),[type] int,[super] NVARCHAR(100))
Insert #T
select 1,N'admin',1,-1 union all
select 2,N'user2',1,1 union all
select 3,N'user3',1,1 union all
select 4,N'user4',1,3 union all
select 5,N'用户',0,4
Go
--测试数据结束
UPDATE #T SET super='user4' WHERE type=0 AND super=4

SELECT * FROM #T

[顺势而为1]

if object_id('tempdb..#Tmp_User') is not null drop table #Tmp_User CREATE TABLE #Tmp_User ( id int, name nvarchar(10), type int, super int) Insert into #Tmp_User Select 1 ,'admin', 1, -1 union select 2,'user2', 1, 1 union select 3, 'user3', 1, 1 union select 4,'user4', 1, 3 union select 5, '用户', 0, 4 Select a.id,a.name,a.type,a.super,b.name From #Tmp_User a Join #Tmp_User b on a.super=b.id Where a.id=5

[二月十六]

好吧，明白了

--测试数据
if not object_id(N'Tempdb..#T') is null
    drop table #T
Go
Create table #T([id] int,[name] nvarchar(25),[type] int,[super] NVARCHAR(100))
Insert #T
select 1,N'admin',1,-1 union all
select 2,N'user2',1,1 union all
select 3,N'user3',1,1 union all
select 4,N'user4',1,3 union all
select 5,N'用户',0,4
Go
--测试数据结束
UPDATE  a
SET     super = ( SELECT TOP 1
                            name
                  FROM      #T
                  WHERE     a.super = #T.id
                )
FROM    #T a
WHERE   a.type = 0
        AND a.super = 4
 
SELECT * FROM #T

[qq_36593316]

引用 5 楼 sinat_28984567 的回复:

这样？

--测试数据
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([id] int,[name] nvarchar(25),[type] int,[super] NVARCHAR(100))
Insert #T
select 1,N'admin',1,-1 union all
select 2,N'user2',1,1 union all
select 3,N'user3',1,1 union all
select 4,N'user4',1,3 union all
select 5,N'用户',0,4
Go
--测试数据结束
UPDATE #T SET super='user4' WHERE type=0 AND super=4

SELECT * FROM #T

我是查询。。不是修改。。

[qq_36593316]

就是我的数据还是 5 用户 0 4 但是我这是虚的数据，吧4转换为他的上一级名字。。

[qq_36593316]

id和super是上下级关系的

[二月十六]

--测试数据
if not object_id(N'Tempdb..#T') is null
    drop table #T
Go
Create table #T([id] int,[name] nvarchar(25),[type] int,[super] NVARCHAR(100))
Insert #T
select 1,N'admin',1,-1 union all
select 2,N'user2',1,1 union all
select 3,N'user3',1,1 union all
select 4,N'user4',1,3 union all
select 5,N'用户',0,4
Go
--测试数据结束
SELECT  [id] ,
        [name] ,
        [type] ,
        CASE WHEN a.type = 0
                  AND a.super = 4 THEN ( SELECT TOP 1
                                                name
                                         FROM   #T
                                         WHERE  a.super = #T.id
                                       )
             ELSE super
        END AS super
FROM    #T a

[qq_36593316]

引用 6 楼 appetizing_fish1 的回复:

if object_id('tempdb..#Tmp_User') is not null drop table #Tmp_User CREATE TABLE #Tmp_User ( id int, name nvarchar(10), type int, super int) Insert into #Tmp_User Select 1 ,'admin', 1, -1 union select 2,'user2', 1, 1 union select 3, 'user3', 1, 1 union select 4,'user4', 1, 3 union select 5, '用户', 0, 4 Select a.id,a.name,a.type,a.super,b.name From #Tmp_User a Join #Tmp_User b on a.super=b.id Where a.id=5

哥，这不是修改。。

[二月十六]

--测试数据
if not object_id(N'Tempdb..#T') is null
    drop table #T
Go
Create table #T([id] int,[name] nvarchar(25),[type] int,[super] NVARCHAR(100))
Insert #T
select 1,N'admin',1,-1 union all
select 2,N'user2',1,1 union all
select 3,N'user3',1,1 union all
select 4,N'user4',1,3 union all
select 5,N'用户',0,4
Go
--测试数据结束
SELECT  [id] ,
        [name] ,
        [type] ,
        CASE WHEN a.type = 0
                   THEN ( SELECT TOP 1
                                                name
                                         FROM   #T
                                         WHERE  a.super = #T.id
                                       )
             ELSE super
        END AS super
FROM    #T a

[顺势而为1]

引用 12 楼 qq_36593316 的回复:

[quote=引用 6 楼 appetizing_fish1 的回复:] if object_id('tempdb..#Tmp_User') is not null drop table #Tmp_User CREATE TABLE #Tmp_User ( id int, name nvarchar(10), type int, super int) Insert into #Tmp_User Select 1 ,'admin', 1, -1 union select 2,'user2', 1, 1 union select 3, 'user3', 1, 1 union select 4,'user4', 1, 3 union select 5, '用户', 0, 4 Select a.id,a.name,a.type,a.super,b.name From #Tmp_User a Join #Tmp_User b on a.super=b.id Where a.id=5

哥，这不是修改。。[/quote] 我做的是查询啊

[顺势而为1]

引用 14 楼 appetizing_fish1 的回复:

[quote=引用 12 楼 qq_36593316 的回复:] [quote=引用 6 楼 appetizing_fish1 的回复:] if object_id('tempdb..#Tmp_User') is not null drop table #Tmp_User CREATE TABLE #Tmp_User ( id int, name nvarchar(10), type int, super int) Insert into #Tmp_User Select 1 ,'admin', 1, -1 union select 2,'user2', 1, 1 union select 3, 'user3', 1, 1 union select 4,'user4', 1, 3 union select 5, '用户', 0, 4 Select a.id,a.name,a.type,a.super,b.name From #Tmp_User a Join #Tmp_User b on a.super=b.id Where a.id=5

哥，这不是修改。。[/quote] 我做的是查询啊[/quote] 或者改成这样 if object_id('tempdb..#Tmp_User') is not null drop table #Tmp_User CREATE TABLE #Tmp_User ( id int, name nvarchar(10), type int, super int) Insert into #Tmp_User Select 1 ,'admin', 1, -1 union select 2,'user2', 1, 1 union select 3, 'user3', 1, 1 union select 4,'user4', 1, 3 union select 5, '用户', 0, 4 Select a.id,a.name,a.type,b.name as super From #Tmp_User a Join #Tmp_User b on a.super=b.id Where a.id=5

[qq_36593316]

引用 11 楼 sinat_28984567 的回复:

--测试数据
if not object_id(N'Tempdb..#T') is null
    drop table #T
Go
Create table #T([id] int,[name] nvarchar(25),[type] int,[super] NVARCHAR(100))
Insert #T
select 1,N'admin',1,-1 union all
select 2,N'user2',1,1 union all
select 3,N'user3',1,1 union all
select 4,N'user4',1,3 union all
select 5,N'用户',0,4
Go
--测试数据结束
SELECT  [id] ,
        [name] ,
        [type] ,
        CASE WHEN a.type = 0
                  AND a.super = 4 THEN ( SELECT TOP 1
                                                name
                                         FROM   #T
                                         WHERE  a.super = #T.id
                                       )
             ELSE super
        END AS super
FROM    #T a

哥，看看这里是什么情况

[二月十六]

用这个

--测试数据
if not object_id(N'Tempdb..#T') is null
    drop table #T
Go
Create table #T([id] int,[name] nvarchar(25),[type] int,[super] NVARCHAR(100))
Insert #T
select 1,N'admin',1,-1 union all
select 2,N'user2',1,1 union all
select 3,N'user3',1,1 union all
select 4,N'user4',1,3 union all
select 5,N'用户',0,4
Go
--测试数据结束
SELECT  [id] ,
        [name] ,
        [type] ,
        CASE WHEN a.type = 0
                   THEN ( SELECT TOP 1
                                                name
                                         FROM   #T
                                         WHERE  a.super = #T.id
                                       )
             ELSE super
        END AS super
FROM    #T a

[qq_36593316]

解决了，感谢各位的帮忙