MYSQL 变更值

Question

--测试数据
create table tbl_test(
orderNumber int,
textinput varchar(100),
refid varchar(50)
)insert into tbl_test values
(1,'','123456789'),(2,'','123456789'),
(3,'111','123456789'),
(1,'','1234567890'),(2,'','1234567890'),
(3,'222','1234567890'),
(1,'','12345678900'),(2,'','12345678900'),
(3,'333','12345678900'),
(1,'','123456789000'),(2,'','123456789000'),
(3,'444','123456789000')--现在的结果
+-------------+-----------+--------------+
| orderNumber | textinput | refid        |
+-------------+-----------+--------------+
|           1 |           | 123456789    |
|           2 |           | 123456789    |
|           3 | 111       | 123456789    |
|           1 |           | 1234567890   |
|           2 |           | 1234567890   |
|           3 | 222       | 1234567890   |
|           1 |           | 12345678900  |
|           2 |           | 12345678900  |
|           3 | 333       | 12345678900  |
|           1 |           | 123456789000 |
|           2 |           | 123456789000 |
|           3 | 444       | 123456789000 |
+-------------+-----------+--------------+--想要的结果
+-------------+-----------+--------------+
| orderNumber | textinput | refid        |
+-------------+-----------+--------------+
|           1 | 111       | 123456789    |
|           2 |           | 123456789    |
|           3 | 111       | 123456789    |
|           1 | 222       | 1234567890   |
|           2 |           | 1234567890   |
|           3 | 222       | 1234567890   |
|           1 | 333       | 12345678900  |
|           2 |           | 12345678900  |
|           3 | 333       | 12345678900  |
|           1 | 444       | 123456789000 |
|           2 |           | 123456789000 |
|           3 | 444       | 123456789000 |
+-------------+-----------+--------------+  在线等  谢谢 

Answer 1

忘记说了  MYSQL的语法   O(∩_∩)O~   谢谢

Answer 2

不知道是不是你要求，至少结果符合你的要求。mysql> select * from tbl_test;
+-------------+-----------+--------------+
| orderNumber | textinput | refid        |
+-------------+-----------+--------------+
|           1 |           | 123456789    |
|           2 |           | 123456789    |
|           3 | 111       | 123456789    |
|           1 |           | 1234567890   |
|           2 |           | 1234567890   |
|           3 | 222       | 1234567890   |
|           1 |           | 12345678900  |
|           2 |           | 12345678900  |
|           3 | 333       | 12345678900  |
|           1 |           | 123456789000 |
|           2 |           | 123456789000 |
|           3 | 444       | 123456789000 |
+-------------+-----------+--------------+
12 rows in set (0.00 sec)mysql> update tbl_test a inner join
    ->  (select refid,max(textinput) as m_textinput from tbl_test group by refid) b
    ->  on a.refid=b.refid
    -> set a.textinput=b.m_textinput
    -> where a.orderNumber=1;
Query OK, 4 rows affected (0.06 sec)
Rows matched: 4  Changed: 4  Warnings: 0mysql> select * from tbl_test;
+-------------+-----------+--------------+
| orderNumber | textinput | refid        |
+-------------+-----------+--------------+
|           1 | 111       | 123456789    |
|           2 |           | 123456789    |
|           3 | 111       | 123456789    |
|           1 | 222       | 1234567890   |
|           2 |           | 1234567890   |
|           3 | 222       | 1234567890   |
|           1 | 333       | 12345678900  |
|           2 |           | 12345678900  |
|           3 | 333       | 12345678900  |
|           1 | 444       | 123456789000 |
|           2 |           | 123456789000 |
|           3 | 444       | 123456789000 |
+-------------+-----------+--------------+
12 rows in set (0.00 sec)mysql>

Answer 3

把orderNumber=3的textinput值根据refid对应添加到orderNumber=1的textinput中
orderNumber的最大值不一定是3

Answer 4

orderNumber的最大值不一定是3   把orderNumber=3与orderNumber=2的想过应该是相同的 
谢谢 

Answer 5

update tbl_test a inner join tbl_test b
on a.refid=b.refid  
set a.textinput=b.textinput
where a.orderNumber=1
and b.orderNumber=3;
mysql> update tbl_test a inner join tbl_test b
    ->  on a.refid=b.refid
    -> set a.textinput=b.textinput
    -> where a.orderNumber=1
    -> and b.orderNumber=3;
Query OK, 0 rows affected (0.06 sec)
Rows matched: 4  Changed: 0  Warnings: 0mysql> select * from tbl_test;
+-------------+-----------+--------------+
| orderNumber | textinput | refid        |
+-------------+-----------+--------------+
|           1 | 111       | 123456789    |
|           2 |           | 123456789    |
|           3 | 111       | 123456789    |
|           1 | 222       | 1234567890   |
|           2 |           | 1234567890   |
|           3 | 222       | 1234567890   |
|           1 | 333       | 12345678900  |
|           2 |           | 12345678900  |
|           3 | 333       | 12345678900  |
|           1 | 444       | 123456789000 |
|           2 |           | 123456789000 |
|           3 | 444       | 123456789000 |
+-------------+-----------+--------------+
12 rows in set (0.00 sec)mysql>

Answer 6

orderNumber的最大值不一定是3  把orderNumber=3与orderNumber=2的效果应该是相同的 
谢谢 

Answer 7

看不懂你的说明了，建议你举例说明，或者用一些数据以说明上面SQL语句的错误。