insert into b (spec,type,date,quantity) values( select spec,type,date,sum(quantity) from a group by spec,date,type order by spec,date )
不能那样写的,如果不对createdate进行格式的话,那样连select spec,type,date,sum(quantity) from a group by spec,date,type order by spec,date 这句语句都执行不了.
主要我对日期格式的处理不懂.我像这样写的 createdate ---日期 productcode ---产品 num ---数量在查找中将日期格式化为字符,但是B表中字段是日期型的所以插不进去 如果像这样写的话: insert into B (createdate,productcode,num) select to_char(createdate,'yyyy-mm-dd'),productcode,sum(num) from A group by productcode,to_char(createdate,'yyyy-mm-dd') order by to_char(createdate,'yyyy-mm-dd'),productcode 但是像这样写的话: insert into B (createdate,productcode,num) select to_date(createdate,'yyyy-mm-dd'),productcode,sum(num) from A group by productcode,to_date(createdate,'yyyy-mm-dd') order by to_date(createdate,'yyyy-mm-dd'),productcode 导致日期是乱码,像这样的格式0001-08-17,实际应该是2005-08-17
insert into b (spec,type,date,quantity) values( select spec,type,to_date(to_char(date,'yyyy-mm-dd'),'yyyy-mm-dd'),sum(quantity) from a group by spec,to_char(date,'yyyy-mm-dd'),type order by spec,to_char(date,'yyyy-mm-dd') )
提示说: not a Group By expression 在select spec,type,to_date(to_char(date,'yyyy-mm-dd'),'yyyy-mm-dd'),sum(quantity) 处的date反白显示
values(
select spec,type,date,sum(quantity)
from a
group by spec,date,type
order by spec,date
)
from a group by spec,date,type order by spec,date
这句语句都执行不了.
createdate ---日期
productcode ---产品
num ---数量在查找中将日期格式化为字符,但是B表中字段是日期型的所以插不进去
如果像这样写的话:
insert into B (createdate,productcode,num)
select to_char(createdate,'yyyy-mm-dd'),productcode,sum(num) from A
group by productcode,to_char(createdate,'yyyy-mm-dd')
order by to_char(createdate,'yyyy-mm-dd'),productcode
但是像这样写的话:
insert into B (createdate,productcode,num)
select to_date(createdate,'yyyy-mm-dd'),productcode,sum(num) from A
group by productcode,to_date(createdate,'yyyy-mm-dd')
order by to_date(createdate,'yyyy-mm-dd'),productcode
导致日期是乱码,像这样的格式0001-08-17,实际应该是2005-08-17
values(
select spec,type,to_date(to_char(date,'yyyy-mm-dd'),'yyyy-mm-dd'),sum(quantity)
from a
group by spec,to_char(date,'yyyy-mm-dd'),type
order by spec,to_char(date,'yyyy-mm-dd')
)
not a Group By expression
在select spec,type,to_date(to_char(date,'yyyy-mm-dd'),'yyyy-mm-dd'),sum(quantity)
处的date反白显示