类似下面的问题: 根据其中几个字段判断重复,只保留一条记录,但是要显示全部字段,怎么查询,谢谢!!比如
字段1 字段2 字段3 字段4
a b c 1
a b c 1a b d 2
a b d 3b b d 2想得到的结果为
a b c 1
a b d 2(或者3)
b b d 2
说明,根据字段1,2,3组合不重复,字段4 不考虑,得到了3个记录
但是也要显示字段4。
我在VC中写下面的SQL语句:
CString strSQL_paichong;
strSQL_paichong+="delete from SHEET2 where ID not in ";
// strSQL_paichong+="(select max(Sheet2.ID) from Sheet2 group by A,B)"; (A和B是判断是否重复的字段)
m_db.ExecuteSQL(strSQL_paichong);我还写了另外的语句如下:/strSQL_paichong+="select * from Sheet2 where ";
strSQL_paichong+="(select min(ID) from Sheet2 where OUT_FILE_NAME=Sheet2.OUT_FILE_NAME and NUM_01=Sheet2.NUM_01)";
m_db.ExecuteSQL(strSQL_paichong);
可是两种方法都没有用,只要一加上GROUP BY语句,运行VC之后,就死在那,cpu效率100%。如果没有group by,则重复的数据还在那,原来的表没动。请教各位高手,怎么解决?我还试用DISTINCT语句,也没有用。谢谢了!我用的是VC,数据源用access,采用ODBC连接方式。
字段1 字段2 字段3 字段4
a b c 1
a b c 1a b d 2
a b d 3b b d 2想得到的结果为
a b c 1
a b d 2(或者3)
b b d 2
说明,根据字段1,2,3组合不重复,字段4 不考虑,得到了3个记录
但是也要显示字段4。
我在VC中写下面的SQL语句:
CString strSQL_paichong;
strSQL_paichong+="delete from SHEET2 where ID not in ";
// strSQL_paichong+="(select max(Sheet2.ID) from Sheet2 group by A,B)"; (A和B是判断是否重复的字段)
m_db.ExecuteSQL(strSQL_paichong);我还写了另外的语句如下:/strSQL_paichong+="select * from Sheet2 where ";
strSQL_paichong+="(select min(ID) from Sheet2 where OUT_FILE_NAME=Sheet2.OUT_FILE_NAME and NUM_01=Sheet2.NUM_01)";
m_db.ExecuteSQL(strSQL_paichong);
可是两种方法都没有用,只要一加上GROUP BY语句,运行VC之后,就死在那,cpu效率100%。如果没有group by,则重复的数据还在那,原来的表没动。请教各位高手,怎么解决?我还试用DISTINCT语句,也没有用。谢谢了!我用的是VC,数据源用access,采用ODBC连接方式。
strSQL_paichong+="select distinct A,B,....as.....,.....as.....,.......into newtable from table";
// strSQL_paichong+="order by A,B";记住一定要用ORDER BY!
id(主建) f1 f2 f3 f4
1 a b c 1
2 a b c 13 a b d 2
4 a b d 35 b b d 2 ——————————————————————————————
DELETE *
FROM t1
WHERE id not in
(
select max(id) from t1 group by f1,f2,f3
)
———————————————————————————————id(主建) f1 f2 f3 f4
2 a b c 14 a b d 35 b b d 2
// 删除重复列的方法
(1) DELETE FROM table_name A WHERE ROWID > (
SELECT min(rowid) FROM table_name B
WHERE A.key_values = B.key_values);
(2) create table table2 as select distinct * from table1;
drop table1;
rename table2 to table1;
(3) Delete from mytable where rowid not in(
select max(rowid) from mytable
group by column_name );
(4) delete from mytable t1
where exists (select 'x' from my_table t2
where t2.key_value1 = t1.key_value1
and t2.key_value2 = t1.key_value2
...
and t2.rowid > t1.rowid);
delete from yourtable where ID not in ( select min(ID) from yourtable group by other_field1, other_field2,....);
delete from forecast_n where ID not in ( select min(ID) from forecast_n group by DTIME1, TIME_AREA ,TIME_CLOCK, NAME)
and to_char(dtime1,'yyyy-mm-dd')=to_char(sysdate,'yyyy-mm-dd');
// 删除重复记录(ID不同,但其他所有内容都相同的记录)
delete from forecast_n where ID not in ( select min(ID) from forecast_n group by DTIME1, TIME_AREA, TIME_CLOCK, NAME)
and to_char(dtime1,'yyyy-mm-dd')=to_char(sysdate,'yyyy-mm-dd');
select * from yourtable where hym in (select hym from yourtable group by hym having count(hym)>1)
然后在程序中调用这个查询,一定不会出错。