以上为执行语句后截图
建了以下两个表
CREATE TABLE dgclientdata(id NUMBER(7) CONSTRAINT table_id_nn NOT NULL,date_time VARCHAR2(40),client_mac VARCHAR2(15),adver_id VARCHAR2(15),CONSTRAINT table_pk PRIMARY KEY (date_time,client_mac,adver_id));
CREATE TABLE countend(mdate VARCHAR2(15), adver_id VARCHAR2(15), clicks NUMBER(7),CONSTRAINT countend_pk PRIMARY KEY (mdate,adver_id));
表中有数据,现执行
插入语句:
INSERT INTO countend select date_time,adver_id,count(*) from dgclientdata group by adver_id having date_time=to_char(sysdate-1);
这条插入语句哪里出错?
select date_time,adver_id,count(*) over ( partition by adver_id)
from dgclientdata
where date_time=to_char(sysdate-1);
CONSTRAINT countend_pk PRIMARY KEY (mdate,adver_id));
如果去掉约束条件的话:会有如下结果:要求adver_id和mdate组合只能出现一次
加一个distinct就好