create table abc(Column1 int ,Column2 varchar(5),Column3 varchar(5),Column4 int ) insert abc(Column1,Column2,Column3,Column4) values(2,'b','a1',1) insert abc(Column1,Column2,Column3,Column4) values(1,'a','a1',1) insert abc(Column1,Column2,Column3,Column4) values(3,'a','a2',3) insert abc(Column1,Column2,Column3,Column4) values(4,'b','a2',4) insert abc(Column1,Column2,Column3,Column4) values(5,'a','a3',3) insert abc(Column1,Column2,Column3,Column4) values(6,'b','a3',4) go update abc set column4 = 2 * (select column4 from abc where column2 = 'a' and column3 = t.column3) from abc t where Column2 = 'B'select * from abc order by column1drop table abc/* Column1 Column2 Column3 Column4 ----------- ------- ------- ----------- 1 a a1 1 2 b a1 2 3 a a2 3 4 b a2 6 5 a a3 3 6 b a3 6(所影响的行数为 6 行) */
create table abc(Column1 int ,Column2 varchar(5),Column3 varchar(5),Column4 int ) insert abc(Column1,Column2,Column3,Column4) values(2,'b','a1',1) insert abc(Column1,Column2,Column3,Column4) values(1,'a','a1',1) insert abc(Column1,Column2,Column3,Column4) values(3,'a','a2',3) insert abc(Column1,Column2,Column3,Column4) values(4,'b','a2',4) insert abc(Column1,Column2,Column3,Column4) values(5,'a','a3',3) insert abc(Column1,Column2,Column3,Column4) values(6,'b','a3',4)update b set Column4=a.Column4*2 from abc a,abc b where a.Column3=b.Column3 and b.Column2='b' select * from abc drop table abc /*----------- ------- ------- ----------- 2 b a1 2 1 a a1 1 3 a a2 3 4 b a2 6 5 a a3 3 6 b a3 6(6 行受影响)*/
if object_id('abc')is not null drop table abc go create table abc(Column1 int ,Column2 varchar(5),Column3 varchar(5),Column4 int ) insert abc(Column1,Column2,Column3,Column4) values(2,'b','a1',1) insert abc(Column1,Column2,Column3,Column4) values(1,'a','a1',1) insert abc(Column1,Column2,Column3,Column4) values(3,'a','a2',3) insert abc(Column1,Column2,Column3,Column4) values(4,'b','a2',4) insert abc(Column1,Column2,Column3,Column4) values(5,'a','a3',3) insert abc(Column1,Column2,Column3,Column4) values(6,'b','a3',4) update a set column4=4*b.column4 from abc a,abc b where a.column3=b.column3 and b.column2='b' and a.column2!='b'select * from abc Column1 Column2 Column3 Column4 ----------- ------- ------- ----------- 2 b a1 1 1 a a1 4 3 a a2 16 4 b a2 4 5 a a3 16 6 b a3 4
create table abc(Column1 int ,Column2 varchar(5),Column3 varchar(5),Column4 int ) insert abc(Column1,Column2,Column3,Column4) values(2,'b','a1',1) insert abc(Column1,Column2,Column3,Column4) values(1,'a','a1',1) insert abc(Column1,Column2,Column3,Column4) values(3,'a','a2',3) insert abc(Column1,Column2,Column3,Column4) values(4,'b','a2',4) insert abc(Column1,Column2,Column3,Column4) values(5,'a','a3',3) insert abc(Column1,Column2,Column3,Column4) values(6,'b','a3',4)SELECT Column1,Column2,Column3,2*(SELECT Column4 FROM abc WHERE column2 = 'a' AND A.column3 = column3 ) AS Column4 FROM abc A WHERE Column2 = 'B' UNION SELECT * FROM abc WHERE Column2 = 'A'drop table abc /*Column1 Column2 Column3 Column4 ----------- ------- ------- ----------- 1 a a1 1 2 b a1 2 3 a a2 3 4 b a2 6 5 a a3 3 6 b a3 6(6 row(s) affected)
断网了 IF OBJECT_ID('ABC') IS NOT NULL DROP TABLE ABC GO create table abc(Column1 int ,Column2 varchar(5),Column3 varchar(5),Column4 int ) insert abc(Column1,Column2,Column3,Column4) values(2,'b','a1',1) insert abc(Column1,Column2,Column3,Column4) values(1,'a','a1',1) insert abc(Column1,Column2,Column3,Column4) values(3,'a','a2',3) insert abc(Column1,Column2,Column3,Column4) values(4,'b','a2',4) insert abc(Column1,Column2,Column3,Column4) values(5,'a','a3',3) insert abc(Column1,Column2,Column3,Column4) values(6,'b','a3',4)UPDATE ABC SET COLUMN4=( select max(COLUMN4*2) FROM ABC T2 WHERE T2.COLUMN3=ABC.COLUMN3 AND T2.COLUMN2='a' ) WHERE COLUMN2='b' SELECT * FROM ABC /* 2 b a1 2 1 a a1 1 3 a a2 3 4 b a2 6 5 a a3 3 6 b a3 6 */
create table abc(Column1 int ,Column2 varchar(5),Column3 varchar(5),Column4 int ) insert abc(Column1,Column2,Column3,Column4) values(2,'b','a1',1) insert abc(Column1,Column2,Column3,Column4) values(1,'a','a1',1) insert abc(Column1,Column2,Column3,Column4) values(3,'a','a2',3) insert abc(Column1,Column2,Column3,Column4) values(4,'b','a2',4) insert abc(Column1,Column2,Column3,Column4) values(5,'a','a3',3) insert abc(Column1,Column2,Column3,Column4) values(6,'b','a3',4)SELECT Column1,Column2,Column3,2*(SELECT Column4 FROM abc WHERE column2 = 'a' AND A.column3 = column3 ) AS Column4 FROM abc A WHERE Column2 = 'B' UNION SELECT * FROM abc WHERE Column2 = 'A'drop table abc /*Column1 Column2 Column3 Column4 ----------- ------- ------- ----------- 1 a a1 1 2 b a1 2 3 a a2 3 4 b a2 6 5 a a3 3 6 b a3 6(6 row(s) affected)
create table abc(Column1 int ,Column2 varchar(5),Column3 varchar(5),Column4 int ) insert abc(Column1,Column2,Column3,Column4) values(2,'b','a1',1) insert abc(Column1,Column2,Column3,Column4) values(1,'a','a1',1) insert abc(Column1,Column2,Column3,Column4) values(3,'a','a2',3) insert abc(Column1,Column2,Column3,Column4) values(4,'b','a2',4) insert abc(Column1,Column2,Column3,Column4) values(5,'a','a3',3) insert abc(Column1,Column2,Column3,Column4) values(6,'b','a3',4) update a set Column4=2*b.Column4 from abc a,abc b where a.column3=b.column3 and a.column2='b' and a.column2!=b.column2select * from abc
update abc set Column4= (select t1.Column4*2 from abc t1 where t.Column3=t1.Column3 and t.Column1!=t1.Column1 and t.Column2='b') from abc t where t.Column2='b' 效率还凑合
update b set Column4=a.Column4*2 from abc a,abc b where a.Column3=b.Column3 and b.Column2='b' select * from abc drop table abc还是小F的SQL帅 学习
create table abc(Column1 int ,Column2 varchar(5),Column3 varchar(5),Column4 int ) insert abc(Column1,Column2,Column3,Column4) values(2,'b','a1',1) insert abc(Column1,Column2,Column3,Column4) values(1,'a','a1',1) insert abc(Column1,Column2,Column3,Column4) values(3,'a','a2',3) insert abc(Column1,Column2,Column3,Column4) values(4,'b','a2',4) insert abc(Column1,Column2,Column3,Column4) values(5,'a','a3',3) insert abc(Column1,Column2,Column3,Column4) values(6,'b','a3',4)update abc set Column4 = 2*(select Column4 from abc where Column3 = t.Column3 and Column2 <> t.Column2) from abc t where Column2 = 'b' select * from abc order by Column1drop table abc --结果 --------------------- 1 a a1 1 2 b a1 2 3 a a2 3 4 b a2 6 5 a a3 3 6 b a3 6
if OBJECT_ID('tb') is not null drop table tb GO create table tb(Column1 int ,Column2 varchar(5),Column3 varchar(5),Column4 int ) GO insert tb(Column1,Column2,Column3,Column4) values(2,'b','a1',1) insert tb(Column1,Column2,Column3,Column4) values(1,'a','a1',1) insert tb(Column1,Column2,Column3,Column4) values(3,'a','a2',3) insert tb(Column1,Column2,Column3,Column4) values(4,'b','a2',4) insert tb(Column1,Column2,Column3,Column4) values(5,'a','a3',3) insert tb(Column1,Column2,Column3,Column4) values(6,'b','a3',4) goupdate a set a.Column4=b.Column4*2 from tb a left join tb b on a.Column3=b.Column3 where a.Column2='b' and b.Column2='a' GO select * from tb order by Column1 ---Result---- Column1 Column2 Column3 Column4 ----------- ------- ------- ----------- 1 a a1 1 2 b a1 2 3 a a2 3 4 b a2 6 5 a a3 3 6 b a3 6
insert abc(Column1,Column2,Column3,Column4) values(2,'b','a1',1)
insert abc(Column1,Column2,Column3,Column4) values(1,'a','a1',1)
insert abc(Column1,Column2,Column3,Column4) values(3,'a','a2',3)
insert abc(Column1,Column2,Column3,Column4) values(4,'b','a2',4)
insert abc(Column1,Column2,Column3,Column4) values(5,'a','a3',3)
insert abc(Column1,Column2,Column3,Column4) values(6,'b','a3',4)
go
update abc
set column4 = 2 * (select column4 from abc where column2 = 'a' and column3 = t.column3)
from abc t where Column2 = 'B'select * from abc order by column1drop table abc/*
Column1 Column2 Column3 Column4
----------- ------- ------- -----------
1 a a1 1
2 b a1 2
3 a a2 3
4 b a2 6
5 a a3 3
6 b a3 6(所影响的行数为 6 行)
*/
insert abc(Column1,Column2,Column3,Column4) values(2,'b','a1',1)
insert abc(Column1,Column2,Column3,Column4) values(1,'a','a1',1)
insert abc(Column1,Column2,Column3,Column4) values(3,'a','a2',3)
insert abc(Column1,Column2,Column3,Column4) values(4,'b','a2',4)
insert abc(Column1,Column2,Column3,Column4) values(5,'a','a3',3)
insert abc(Column1,Column2,Column3,Column4) values(6,'b','a3',4)update b set Column4=a.Column4*2 from abc a,abc b where a.Column3=b.Column3 and b.Column2='b'
select * from abc
drop table abc
/*----------- ------- ------- -----------
2 b a1 2
1 a a1 1
3 a a2 3
4 b a2 6
5 a a3 3
6 b a3 6(6 行受影响)*/
go
create table abc(Column1 int ,Column2 varchar(5),Column3 varchar(5),Column4 int )
insert abc(Column1,Column2,Column3,Column4) values(2,'b','a1',1)
insert abc(Column1,Column2,Column3,Column4) values(1,'a','a1',1)
insert abc(Column1,Column2,Column3,Column4) values(3,'a','a2',3)
insert abc(Column1,Column2,Column3,Column4) values(4,'b','a2',4)
insert abc(Column1,Column2,Column3,Column4) values(5,'a','a3',3)
insert abc(Column1,Column2,Column3,Column4) values(6,'b','a3',4)
update a
set column4=4*b.column4
from abc a,abc b
where a.column3=b.column3 and b.column2='b' and a.column2!='b'select * from abc
Column1 Column2 Column3 Column4
----------- ------- ------- -----------
2 b a1 1
1 a a1 4
3 a a2 16
4 b a2 4
5 a a3 16
6 b a3 4
create table abc(Column1 int ,Column2 varchar(5),Column3 varchar(5),Column4 int )
insert abc(Column1,Column2,Column3,Column4) values(2,'b','a1',1)
insert abc(Column1,Column2,Column3,Column4) values(1,'a','a1',1)
insert abc(Column1,Column2,Column3,Column4) values(3,'a','a2',3)
insert abc(Column1,Column2,Column3,Column4) values(4,'b','a2',4)
insert abc(Column1,Column2,Column3,Column4) values(5,'a','a3',3)
insert abc(Column1,Column2,Column3,Column4) values(6,'b','a3',4)SELECT Column1,Column2,Column3,2*(SELECT Column4 FROM abc WHERE column2 = 'a' AND A.column3 = column3 ) AS Column4
FROM abc A
WHERE Column2 = 'B'
UNION
SELECT *
FROM abc
WHERE Column2 = 'A'drop table abc
/*Column1 Column2 Column3 Column4
----------- ------- ------- -----------
1 a a1 1
2 b a1 2
3 a a2 3
4 b a2 6
5 a a3 3
6 b a3 6(6 row(s) affected)
IF OBJECT_ID('ABC') IS NOT NULL DROP TABLE ABC
GO
create table abc(Column1 int ,Column2 varchar(5),Column3 varchar(5),Column4 int )
insert abc(Column1,Column2,Column3,Column4) values(2,'b','a1',1)
insert abc(Column1,Column2,Column3,Column4) values(1,'a','a1',1)
insert abc(Column1,Column2,Column3,Column4) values(3,'a','a2',3)
insert abc(Column1,Column2,Column3,Column4) values(4,'b','a2',4)
insert abc(Column1,Column2,Column3,Column4) values(5,'a','a3',3)
insert abc(Column1,Column2,Column3,Column4) values(6,'b','a3',4)UPDATE ABC SET COLUMN4=(
select max(COLUMN4*2) FROM ABC T2 WHERE T2.COLUMN3=ABC.COLUMN3 AND T2.COLUMN2='a'
)
WHERE COLUMN2='b'
SELECT * FROM ABC
/*
2 b a1 2
1 a a1 1
3 a a2 3
4 b a2 6
5 a a3 3
6 b a3 6
*/
create table abc(Column1 int ,Column2 varchar(5),Column3 varchar(5),Column4 int )
insert abc(Column1,Column2,Column3,Column4) values(2,'b','a1',1)
insert abc(Column1,Column2,Column3,Column4) values(1,'a','a1',1)
insert abc(Column1,Column2,Column3,Column4) values(3,'a','a2',3)
insert abc(Column1,Column2,Column3,Column4) values(4,'b','a2',4)
insert abc(Column1,Column2,Column3,Column4) values(5,'a','a3',3)
insert abc(Column1,Column2,Column3,Column4) values(6,'b','a3',4)SELECT Column1,Column2,Column3,2*(SELECT Column4 FROM abc WHERE column2 = 'a' AND A.column3 = column3 ) AS Column4
FROM abc A
WHERE Column2 = 'B'
UNION
SELECT *
FROM abc
WHERE Column2 = 'A'drop table abc
/*Column1 Column2 Column3 Column4
----------- ------- ------- -----------
1 a a1 1
2 b a1 2
3 a a2 3
4 b a2 6
5 a a3 3
6 b a3 6(6 row(s) affected)
insert abc(Column1,Column2,Column3,Column4) values(2,'b','a1',1)
insert abc(Column1,Column2,Column3,Column4) values(1,'a','a1',1)
insert abc(Column1,Column2,Column3,Column4) values(3,'a','a2',3)
insert abc(Column1,Column2,Column3,Column4) values(4,'b','a2',4)
insert abc(Column1,Column2,Column3,Column4) values(5,'a','a3',3)
insert abc(Column1,Column2,Column3,Column4) values(6,'b','a3',4)
update a set Column4=2*b.Column4 from abc a,abc b
where a.column3=b.column3 and a.column2='b' and a.column2!=b.column2select * from abc
set Column4=
(select t1.Column4*2
from abc t1
where
t.Column3=t1.Column3 and t.Column1!=t1.Column1
and t.Column2='b')
from abc t
where
t.Column2='b'
效率还凑合
select * from abc
drop table abc还是小F的SQL帅 学习
create table abc(Column1 int ,Column2 varchar(5),Column3 varchar(5),Column4 int )
insert abc(Column1,Column2,Column3,Column4) values(2,'b','a1',1)
insert abc(Column1,Column2,Column3,Column4) values(1,'a','a1',1)
insert abc(Column1,Column2,Column3,Column4) values(3,'a','a2',3)
insert abc(Column1,Column2,Column3,Column4) values(4,'b','a2',4)
insert abc(Column1,Column2,Column3,Column4) values(5,'a','a3',3)
insert abc(Column1,Column2,Column3,Column4) values(6,'b','a3',4)update abc
set Column4 = 2*(select Column4 from abc where Column3 = t.Column3 and Column2 <> t.Column2)
from abc t
where Column2 = 'b'
select * from abc order by Column1drop table abc
--结果
---------------------
1 a a1 1
2 b a1 2
3 a a2 3
4 b a2 6
5 a a3 3
6 b a3 6
if OBJECT_ID('tb') is not null drop table tb
GO
create table tb(Column1 int ,Column2 varchar(5),Column3 varchar(5),Column4 int )
GO
insert tb(Column1,Column2,Column3,Column4) values(2,'b','a1',1)
insert tb(Column1,Column2,Column3,Column4) values(1,'a','a1',1)
insert tb(Column1,Column2,Column3,Column4) values(3,'a','a2',3)
insert tb(Column1,Column2,Column3,Column4) values(4,'b','a2',4)
insert tb(Column1,Column2,Column3,Column4) values(5,'a','a3',3)
insert tb(Column1,Column2,Column3,Column4) values(6,'b','a3',4)
goupdate a
set
a.Column4=b.Column4*2
from tb a left join tb b on a.Column3=b.Column3 where a.Column2='b' and b.Column2='a'
GO
select * from tb order by Column1
---Result----
Column1 Column2 Column3 Column4
----------- ------- ------- -----------
1 a a1 1
2 b a1 2
3 a a2 3
4 b a2 6
5 a a3 3
6 b a3 6