--> 测试数据:#tb IF OBJECT_ID('TEMPDB.DBO.#tb') IS NOT NULL DROP TABLE #tb GO CREATE TABLE #tb([id] INT,[name] VARCHAR(4),[user_priv] VARCHAR(5)) INSERT #tb SELECT 1,'张三','1,2,3' UNION ALL SELECT 2,'李四',NULL --------------开始查询--------------------------SELECT [id],[name],(SELECT TOP 1 [user_priv] FROM #tb WHERE [user_priv] IS NOT NULL ) FROM #tb ----------------结果---------------------------- /* id name (无列名) 1 张三 1,2,3 2 李四 1,2,3 */
CREATE TABLE tba (id INT , NAME varchar(10), user_priv varchar(10)) INSERT INTO tba SELECT 1, '张三' ,'1,2,3' UNION ALL SELECT 2, '李四', NULL
SELECT * FROM tba
UPDATE Tba SET user_priv=(select user_priv FROM TBa b WHERE id=1) WHERE id=2 SELECT * FROM tba /*
(2 行受影响) id NAME user_priv ----------- ---------- ---------- 1 张三 1,2,3 2 李四 NULL
(2 行受影响)
(1 行受影响)
id NAME user_priv ----------- ---------- ---------- 1 张三 1,2,3 2 李四 1,2,3
(2 行受影响)
*/
-------借用啊汤哥的测试数据 IF OBJECT_ID('TEMPDB.DBO.#tb') IS NOT NULL DROP TABLE #tbCREATE TABLE #tb([id] INT,[name] VARCHAR(4),[user_priv] VARCHAR(5)) INSERT #tb SELECT 1,'张三','1,2,3' UNION ALL SELECT 2,'李四',NULL union all select 3,'王五',null update #tb set user_priv= (select MAX(a.user_priv)from #tb a where a.user_priv is not null) where user_priv is nullselect * from #tb /*id name user_priv ----------- ---- --------- 1 张三 1,2,3 2 李四 1,2,3 3 王五 1,2,3(3 row(s) affected)*/
IF object_id('tab') IS NOT NULL DROP TABLE #tb GOCREATE TABLE tab ( [id] INT, [name] VARCHAR(4), [user_priv] VARCHAR(5))INSERT tab SELECT 1, '张三', '1,2,3' UNION ALL SELECT 2, '李四', NULL --select * from tabUPDATE tab SET [user_priv] = (SELECT [user_priv] FROM tab b WHERE [user_priv] IS NOT NULL ) WHERE [user_priv] IS NULL --drop table tab
是啊。我理解的也很简单if OBJECT_ID('a') is not null drop table a go create table a(id int,name varchar(10),user_priv varchar(10)) insert into a select 1,'张三','1,2,3' union all select 2,'李四',null go select * from a update a set user_priv=(select user_priv from a where name='张三') where name='李四' select * from a
IF OBJECT_ID('TEMPDB.DBO.#tb') IS NOT NULL DROP TABLE #tb
GO
CREATE TABLE #tb([id] INT,[name] VARCHAR(4),[user_priv] VARCHAR(5))
INSERT #tb
SELECT 1,'张三','1,2,3' UNION ALL
SELECT 2,'李四',NULL
--------------开始查询--------------------------SELECT [id],[name],(SELECT TOP 1 [user_priv] FROM #tb WHERE [user_priv] IS NOT NULL ) FROM #tb
----------------结果----------------------------
/*
id name (无列名)
1 张三 1,2,3
2 李四 1,2,3
*/
INSERT INTO tba
SELECT 1, '张三' ,'1,2,3'
UNION ALL
SELECT 2, '李四', NULL
SELECT * FROM tba
UPDATE Tba
SET user_priv=(select user_priv FROM TBa b WHERE id=1)
WHERE id=2
SELECT * FROM tba
/*
(2 行受影响)
id NAME user_priv
----------- ---------- ----------
1 张三 1,2,3
2 李四 NULL
(2 行受影响)
(1 行受影响)
id NAME user_priv
----------- ---------- ----------
1 张三 1,2,3
2 李四 1,2,3
(2 行受影响)
*/
IF OBJECT_ID('TEMPDB.DBO.#tb') IS NOT NULL DROP TABLE #tbCREATE TABLE #tb([id] INT,[name] VARCHAR(4),[user_priv] VARCHAR(5))
INSERT #tb
SELECT 1,'张三','1,2,3' UNION ALL
SELECT 2,'李四',NULL union all
select 3,'王五',null update #tb set user_priv= (select MAX(a.user_priv)from #tb a where a.user_priv is not null)
where user_priv is nullselect * from #tb
/*id name user_priv
----------- ---- ---------
1 张三 1,2,3
2 李四 1,2,3
3 王五 1,2,3(3 row(s) affected)*/
DROP TABLE #tb GOCREATE TABLE tab (
[id] INT,
[name] VARCHAR(4),
[user_priv] VARCHAR(5))INSERT tab
SELECT 1,
'张三',
'1,2,3'
UNION ALL
SELECT 2,
'李四',
NULL
--select * from tabUPDATE tab
SET [user_priv] = (SELECT [user_priv]
FROM tab b
WHERE [user_priv] IS NOT NULL )
WHERE [user_priv] IS NULL
--drop table tab
drop table a
go
create table a(id int,name varchar(10),user_priv varchar(10))
insert into a
select 1,'张三','1,2,3' union all
select 2,'李四',null
go
select * from a
update a set user_priv=(select user_priv from a where name='张三')
where name='李四'
select * from a