swap(x1,x2) create procedure sp_test(id1 int,id2 int) begin declare temp1 int default 0; declare temp2 int default 0; select x into temp1 from tb where id =id1; select x into temp2 from tb where id =id2; update tb set x=temp2 where id=id1; update tb set x=temp1 where id=id2; end
UPDATE `swap_test` a1 INNER JOIN (SELECT a.id AS aid,b.id AS bid,a.x AS ax,a.y AS ay,b.x AS bx,b.y AS `by`FROM `swap_test` a LEFT JOIN `swap_test` b ON a.`x`=b.`y` WHERE b.`y`=1) c ON a1.id=c.aid OR a1.id=c.bid SET a1.x=IF(a1.id=aid,bx,ax),a1.y=IF(a1.id=aid,`by`,ay);
感谢大侠,是可以跟y=1的交换了。但可能是我真的没说清楚。“如果输入y=1 就和x=1的值交换”, 如果下次查询我想换 y=2,也是与x=1换呢? 所以我觉得ON a.`x`=b.`y` WHERE b.`y`=1不太符合。 没说明白真心不好意思。
你的要求是什么? WHERE b.`y`=1其中的1是要变动的? 如是,用SP,将参数传递进去 WHERE b.`y`=@cs
UPDATE `swap_test` d INNER JOIN (SELECT a.id id,b.x X,b.y Y FROM `swap_test` a LEFT OUTER JOIN `swap_test` b ON (a.y=1 AND b.x=1) OR (a.x=1 AND b.y=1) WHERE b.id IS NOT NULL) c ON d.id=c.id SET d.x=c.x,d.y=c.y
create procedure sp_test(id1 int,id2 int)
begin
declare temp1 int default 0;
declare temp2 int default 0;
select x into temp1 from tb where id =id1;
select x into temp2 from tb where id =id2;
update tb set x=temp2 where id=id1;
update tb set x=temp1 where id=id2;
end
》同一行数据的不同字段值交换了啊?你这都跨行啦,
看来你对这句话“条件是如果y=1 就和x=1的值交换” 没理解。
意思是where y = 1, 查询到x=1行,交换这两行的(x1 x2)(y1,y2), 并没有跨字段交换,》把你提供的数据,在提供数据的基础上想要的结果贴出来!mysql> select * from swap_test
-> ;
+----+------+------+
| id | x | y |
+----+------+------+
| 1 | 1 | 0 |
| 2 | 0 | 1 |
| 3 | 0 | 2 |
| 4 | 0 | 3 |
+----+------+------+
4 rows in set (0.00 sec)
期待结果,如果y=1, 交换x=1行内容+----+------+------+
| id | x | y |
+----+------+------+
| 1 | 0 | 1 |
| 2 | 1 | 0 |
| 3 | 0 | 2 |
| 4 | 0 | 3 |
+----+------+------+
INNER JOIN
(SELECT a.id AS aid,b.id AS bid,a.x AS ax,a.y AS ay,b.x AS bx,b.y AS `by`FROM `swap_test` a LEFT JOIN `swap_test` b ON a.`x`=b.`y` WHERE b.`y`=1) c
ON a1.id=c.aid OR a1.id=c.bid
SET a1.x=IF(a1.id=aid,bx,ax),a1.y=IF(a1.id=aid,`by`,ay);
感谢大侠,是可以跟y=1的交换了。但可能是我真的没说清楚。“如果输入y=1 就和x=1的值交换”, 如果下次查询我想换 y=2,也是与x=1换呢?
所以我觉得ON a.`x`=b.`y` WHERE b.`y`=1不太符合。 没说明白真心不好意思。
WHERE b.`y`=1其中的1是要变动的?
如是,用SP,将参数传递进去
WHERE b.`y`=@cs
INNER JOIN
(SELECT a.id id,b.x X,b.y Y
FROM `swap_test` a LEFT OUTER JOIN `swap_test` b ON (a.y=1 AND b.x=1) OR (a.x=1 AND b.y=1) WHERE b.id IS NOT NULL) c
ON d.id=c.id
SET d.x=c.x,d.y=c.y