mysql对不同项计数_mysql-为每个记录计数不同的元素并更新它们

这是我的学生记录表,我试图为每个学生查找具有相同名字或姓氏的其他学生人数,并将其保存在每个学生的同名和姓氏相同处.

+-----------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-----------+-------------+------+-----+---------+-------+

| first | varchar(20) | YES | | NULL | |

| last | varchar(25) | YES | | NULL | |

| samefirst | int(11) | YES | | NULL | |

| samelast | int(11) | YES | | NULL | |

+-----------+-------------+------+-----+---------+-------+

select a.first as af, a.last as al, b.first as bf, b.last as bl

from StudRec a inner join

StudRec b

on a.first = b.first and a.last <> b.last) or

a.first <> b.first and a.last = b.last;

上面的查询为我提供了每个学生及其与另一个学生的匹配的第一个或最后一个匹配项,但是我很难对每个学生进行计数并将其插入到samefirst和samelast中.谢谢你

解决方法:

使用相关子查询为每个学生查找名字或姓氏相似的学生.

SELECT SR.first,

SR.last,

(SELECT COUNT(*) FROM StudRec AS SR2

WHERE SR2.first = SR.first) AS samefirst,

(SELECT COUNT(*) FROM StudRec AS SR2

WHERE SR2.last = SR.last) AS samelast

FROM StudRec AS SR

标签：mysql

来源： https://codeday.me/bug/20191119/2034701.html