我要将一个表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 非单组分组函数”错误请大家帮我想想这样解决这个问题?
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 非单组分组函数”错误请大家帮我想想这样解决这个问题?
select max(id)+1,name from t_good_student,t_student t
group by name
order by t.name
select (max(id) over())+1,distinct name
from t_good_student,t_student t order by t.name
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
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
参考一下这个
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上摘下来的
(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
(ID, NAME)
SELECT MAX(ID) + 1, NAME
FROM T_GOOD_STUDENT, T_STUDENT T
GROUP BY NAME
ORDER BY T.NAME
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;