MySQL Insert Into复杂语句

Question

我正走在这样的道路上：

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行使用near‘(选择member_id作为成员at，(MAX(family_id)+1)作为来自成员的最大家族)的正确语法

我假设这是来自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中做到这一点。

威廉姆斯的名字被忽略，因为它没有匹配的姓氏。

提亚

Answer 1

试一试。

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

Answer 2

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上的members表连接：

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