这样的sql 语句如何写？

[tyrbl]

我要将一个表t_student 中的记录插入到t_good_student 中去，假设这t_student 表只有两个字段name,score.
t_good_student 表也只有2个字段id,name.
现在我面临这样的困难：（1）因为t_student没有主键，所以一个学生可能重复多次。但一个学生只插入到 t_good_student 中一次。
（2）每插入一条记录就在t_good_student 的当前最大id 上加1

我是这样写的；insert into t_good_student (id,name) select （max(id)+1）,distinct name from t_good_student,t_student t order by t.name .
执行的时候报错：“ORA-00937 非单组分组函数”错误

请大家帮我想想这样解决这个问题？

[adrianlynn]

insert into t_good_student (id,name)
select max(id)+1,name from t_good_student,t_student t
group by name
order by t.name

[hebo2005]

insert into t_good_student (id,name)
select （max(id) over())+1,distinct name
from t_good_student,t_student t order by t.name

[hebo2005]

不过你这样还是不对的啊，id还是只有一个的啊，要做循环才行啊

[hxmsammi]

楼上回答的好快啊

[hebo2005]

建议你这样子

INSERT INTO t_good_student
            (ID, NAME)
   SELECT a.ID + ROWNUM, t.NAME
     FROM (SELECT MAX (ID) ID
             FROM t_good_student) a,
          (SELECT   NAME
               FROM t_student
           GROUP BY NAME
           ORDER BY t.NAME) t

[hebo2005]

保险点，应该这样

INSERT INTO t_good_student
            (ID, NAME)
   SELECT a.ID + ROWNUM, t.NAME
     FROM (SELECT nvl(MAX (ID),0) ID
             FROM t_good_student) a,
          (SELECT   NAME
               FROM t_student
           GROUP BY NAME
           ORDER BY t.NAME) t

[阿三]

你的问题可以用rownumber与分析函数来完成
参考一下这个
with t as (select t.*,row_number() over(partition by GUEST_ID order by ARRIVAL_DATE desc) rn from YKT_VISIT )
select * from t where rn=1
这是从itpub上摘下来的

[xllxllx]

先创建一个序列,即
create sequence sqe_id
minvalue 1
maxvalue 999999999
start with 1
increment by 1
cache 20
nocycle;
然后插入数据,即:
insert into T_GOOD_STUDENT(id,name)
select sqe_id.nextval,name from T_STUDENT;

[oraclelogan]

INSERT INTO t_good_student
(ID, NAME)
SELECT a.ID + ROWNUM, t.NAME
FROM (SELECT nvl(MAX (ID),0) ID
FROM t_good_student) a,
(SELECT NAME
FROM t_student
GROUP BY NAME
ORDER BY t.NAME) t

[chensi05]

INSERT INTO T_GOOD_STUDENT
    (ID, NAME)
    SELECT MAX(ID) + 1, NAME
      FROM T_GOOD_STUDENT, T_STUDENT T
     GROUP BY NAME
     ORDER BY T.NAME

[snowy_howe]

我感觉在数据库设计上来说，应当先有学生编码，再有学生分数。
请考虑。