我在的路径上
INSERT INTO family (family_id, member_id)
VALUES (maxfamily, membersid)
(SELECT member_id as membersid, (MAX(family_id)+1) as maxfamily
FROM members
GROUP BY lname
HAVING COUNT (lname)>1)
当前错误:请查看与MySQL服务器版本对应的手册,以获得在第3行的"(SELECT member_id as membersid,(MAX(family_id)+1)as maxfamily FROM member"附近使用的正确语法
我假设这是来自MAX(family_id),因为它不知道当前的最大值,因为它没有引用族表?
意向:
从成员表中查找姓氏相同的人。使用此查询可以将新记录插入族表中。
Example:
members table
Andersen, Fred, id=288
Andersen, Smith, id=289
Smith, Peter, id=385
Smith, Elizabeth, id=404
Williams, Kevin, id=408
family table (currently empty)
family_id, member_id
1, 288
1, 289 (this covers the Anderson family)
2, 385
2, 404 (this covers the Smith family)
问题:如何为此任务创建正确的查询?请注意,族id会根据上一个最大值自动递增。
我知道如何在编程中做到这一点,我只想在直接SQL中做到这。
威廉姆斯的名字被忽略,因为它没有匹配的姓氏。
TIA
请尝试一下。
SET @prev_lname = '', @id = 0;
INSERT INTO family(family_id,member_id)
SELECT family_id, id
FROM
(SELECT members.lname,members.id,
CASE WHEN @prev_lname = members.lname THEN @id
ELSE @id := IFNULL(@id,0) + 1
END as family_id,
@prev_lname := members.lname
FROM
members,(SELECT lname
FROM members
GROUP BY lname
HAVING COUNT(*)>1) AS qualify
WHERE members.lname = qualify.lname
ORDER BY members.lname ASC
)AS T;
sqlfiddle
1)获取家族名称:
select lname
from members
group by lname
having count(lname) > 1;
2) 枚举家族姓氏:
select lname, (@fid := @fid + 1) as family_id
from members, (select @fid := 0) dummy
group by lname
having count(lname) > 1;
3) 加入lname:上的成员表
select f.family_id, m.member_id
from (
select lname, (@fid := @fid + 1) as family_id
from members, (select @fid := 0) dummy
group by lname
having count(lname) > 1
) f
join members m on m.lname = f.lname;
4) 结果(创建插入语句):
insert into family (family_id, member_id)
select f.family_id, m.member_id
from (
select lname, (@fid := @fid + 1) as family_id
from members, (select @fid := 0) dummy
group by lname
having count(lname) > 1
) f
join members m on m.lname = f.lname
http://sqlfiddle.com/#!9/209a55/2