我有两张桌子:
desc students
+-----------------------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+---------+------+-----+---------+----------------+
| student_id | int(11) | NO | PRI | NULL | auto_increment |
| student_ticket_number | int(11) | YES | | 0 | |
+-----------------------+---------+------+-----+---------+----------------+
desc studentdates
+-----------------------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+---------+------+-----+---------+----------------+
| student_date_id | int(11) | NO | PRI | NULL | auto_increment |
| student_id | int(11) | YES | | NULL | |
| student_ticket_number | int(11) | YES | | 0 | |
+-----------------------+---------+------+-----+---------+----------------+
我想将列
students.student_ticket_number
移到字段studentdates.student_ticket_number
匹配的位置。因此,如果用户John有
student_id
=1,那么将他的ticketnumber(例如1234)从student_id
移动到students.student_ticket_number
。在
studentdates.student_ticket_number WHERE student_id = '1'
表中,可以有多个相同的记录,因此我希望使用最低的studentdates.student_id
并跳过其他记录。如果不清楚的话告诉我。我想我需要做一个子查询,但怎么做呢?
最佳答案
您需要在子查询中获取最小值student_date_ID
,然后将其与其他表联接。试试这个,
UPDATE students a
INNER JOIN studentdates b
ON a.student_ID = b.student_ID
INNER JOIN
(
SELECT student_ID, MIN(student_date_ID) minID
FROM studentDates
GROUP BY student_ID
) c ON b.student_ID = c.student_ID AND
b.student_date_id = c.minID
SET a.student_ticket_number = b.student_ticket_number
WHERE a.student_id = '1'
UPDATE students a
INNER JOIN studentdates b
ON a.student_ID = b.student_ID
INNER JOIN
(
SELECT student_ID, MIN(student_date_ID) minID
FROM studentDates
GROUP BY student_ID
) c ON b.student_ID = c.student_ID AND
b.student_date_id = c.minID
SET b.student_ticket_number = a.student_ticket_number
WHERE a.student_id = '1'
关于mysql - 合并MySQL列排除重复项,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/12596098/