select da.* from
(
select count(*) as tcount ,
RECORD_TYPE ,
IMSI,
DIALLED_DIGITS,
SERVICE_TYPE,
SERVICE_ID ,
MSC_SMTMSC ,
CELL_ID_ORIG ,
MS_CLASS_MARK ,
CALL_START_DATE ,
CALL_START_TIME ,
CHARGEABLE_TIME ,
DATA_VOL ,
DATA_VOL_REF ,
PULSES ,
CALL_END_DATE ,
CALL_END_TIME ,
MSC_ID_TERM ,
CELL_ID_TERM ,
MSISDN ,
A_NUMBER ,
INCOMING_ROUTE ,
OUTGOING_ROUTE ,
IMEI ,
MSC_ID ,
BSS_MAP ,
CAUSE_LOC ,
EOS ,
DISCONNECT_PARTY ,
DURATION2G ,
DURATION3G ,
RANAP_CAUSE_CODE ,
FAULT_CODE ,
PRES_AND_SCN_IND ,
OCN ,
REDIRECT_NUM ,
TRANSLATED_NUMBER ,
SEIZURE_TIME ,
file_name
from gz_user.FL_D_ICB_MSC_CDR_1N
where create_ts >= '20090619'
group by
RECORD_TYPE ,
IMSI,
DIALLED_DIGITS,
SERVICE_TYPE,
SERVICE_ID ,
MSC_SMTMSC ,
CELL_ID_ORIG ,
MS_CLASS_MARK ,
CALL_START_DATE ,
CALL_START_TIME ,
CHARGEABLE_TIME ,
DATA_VOL ,
DATA_VOL_REF ,
PULSES ,
CALL_END_DATE ,
CALL_END_TIME ,
MSC_ID_TERM ,
CELL_ID_TERM ,
MSISDN ,
A_NUMBER ,
INCOMING_ROUTE ,
OUTGOING_ROUTE ,
IMEI ,
MSC_ID ,
BSS_MAP ,
CAUSE_LOC ,
EOS ,
DISCONNECT_PARTY ,
DURATION2G ,
DURATION3G ,
RANAP_CAUSE_CODE ,
FAULT_CODE ,
PRES_AND_SCN_IND ,
OCN ,
REDIRECT_NUM ,
TRANSLATED_NUMBER ,
SEIZURE_TIME ,
file_name
)da
where da.tcount > 1
order by da.tcount desc

使用having尝试也是同样的出错。

后来采取迂回的方法解除，如下：
1 首先建立一个与原表相同结构的临时表，然后重新load这些数据，load前在unix系统上使用sort -u删除重复数据，在load入sybase
2 用原来表数据与临时表数据比较，找出重复数据所在的file的名字：
select aa.file_name,aa.tcount,bb.tcount from
( select count(*) as tcount, file_name from gz_user.FL_D_ICB_MSC_CDR_1N
where create_ts >= '20090618'
group by file_name )aa,
( select count(*) as tcount, file_name from bill_cdr_1n_test
group by file_name )bb
where aa.file_name = bb.file_name
and aa.tcount <> bb.tcount

3 再用这些文件名去在原表中找出重复数据，并删除
select da.* from
(
select count(*) as tcount ,
RECORD_TYPE ,
IMSI,
DIALLED_DIGITS,
SERVICE_TYPE,
SERVICE_ID ,
MSC_SMTMSC ,
CELL_ID_ORIG ,
MS_CLASS_MARK ,
CALL_START_DATE ,
CALL_START_TIME ,
CHARGEABLE_TIME ,
DATA_VOL ,
DATA_VOL_REF ,
PULSES ,
CALL_END_DATE ,
CALL_END_TIME ,
MSC_ID_TERM ,
CELL_ID_TERM ,
MSISDN ,
A_NUMBER ,
INCOMING_ROUTE ,
OUTGOING_ROUTE ,
IMEI ,
MSC_ID ,
BSS_MAP ,
CAUSE_LOC ,
EOS ,
DISCONNECT_PARTY ,
DURATION2G ,
DURATION3G ,
RANAP_CAUSE_CODE ,
FAULT_CODE ,
PRES_AND_SCN_IND ,
OCN ,
REDIRECT_NUM ,
TRANSLATED_NUMBER ,
SEIZURE_TIME ,
file_name
from gz_user.FL_D_ICB_MSC_CDR_1N
where file_name in (
'MSC10-7459-412228.DAT',
'MSC12-2772-380377.DAT',
'MSC12-3220-381329.DAT',
'MSC12-3261-381977.DAT',
'MSC12-3263-382014.DAT',
'MSC12-3385-383835.DAT',
'MSC12-3403-384039.DAT',
'MSC12-3437-384513.DAT',
'MSC12-3473-384921.DAT',
'MSC12-3494-385158.DAT',
'MSC12-3506-385289.DAT',
'MSC12-3598-386504.DAT',
'MSC12-3631-386960.DAT',
'MSC12-3666-387366.DAT',
'MSC12-3763-388564.DAT',
'MSC12-3764-388573.DAT',
'MSC12-3791-388878.DAT',
'MSC12-3835-389475.DAT',
'MSC12-3926-390771.DAT',
'MSC12-3936-390916.DAT',
'MSC12-3940-390973.DAT',
'MSC12-4073-392688.DAT',
'MSC12-4075-392709.DAT',
'MSC12-4111-393200.DAT',
'MSC12-4147-393625.DAT',
'MSC12-4184-394065.DAT',
'MSC12-4207-394401.DAT',
'MSC12-4210-394434.DAT',
'MSC12-4213-394476.DAT',
'MSC12-4215-394499.DAT',
'MSC12-4255-394951.DAT',
'MSC12-4339-396013.DAT',
'MSC12-4434-397179.DAT',
'MSC12-4557-398815.DAT',
'MSC12-4594-399332.DAT',
'MSC12-4622-399751.DAT',
'MSC12-4832-402362.DAT',
'MSC12-4921-403461.DAT',
'MSC12-4927-403526.DAT',
'MSC12-4940-403672.DAT',
'MSC12-4965-403955.DAT',
'MSC12-5048-404996.DAT',
'MSC12-5123-405947.DAT',
'MSC12-5154-406292.DAT',
'MSC12-5253-407665.DAT',
'MSC12-5442-410174.DAT',
'MSC12-5445-410207.DAT',
'MSC12-5470-410589.DAT',
'MSC12-5622-412437.DAT',
'MSC12-5723-413653.DAT',
'MSC12-5788-414547.DAT',
'MSC12-5865-415471.DAT',
'MSC12-5892-415840.DAT',
'MSC12-5913-416102.DAT',
'MSC12-5972-416931.DAT',
'MSC12-5974-416957.DAT',
'MSC12-6039-417822.DAT',
'MSC12-6100-418473.DAT',
'MSC13-4646-380203.DAT',
'MSC13-4653-380280.DAT',
'MSC13-4688-380846.DAT',
'MSC13-4709-381085.DAT',
'MSC13-4721-381215.DAT',
'MSC13-4781-382041.DAT',
'MSC13-4822-382497.DAT',
'MSC13-4829-382572.DAT',
'MSC13-4898-383520.DAT',
'MSC13-4926-383834.DAT',
'MSC13-4965-384441.DAT',
'MSC13-4972-384527.DAT',
'MSC13-5080-385920.DAT',
'MSC13-5113-386300.DAT',
'MSC13-5224-387715.DAT',
'MSC13-5442-390678.DAT',
'MSC13-5448-390751.DAT',
'MSC13-5469-391001.DAT',
'MSC13-5492-391251.DAT',
'MSC13-5499-391329.DAT',
'MSC13-5562-392220.DAT',
'MSC13-5644-393325.DAT',
'MSC13-5646-393349.DAT',
'MSC13-5656-393468.DAT',
'MSC13-5661-393522.DAT',
'MSC13-5724-394424.DAT',
'MSC13-5819-395677.DAT',
'MSC13-5872-396292.DAT',
'MSC13-5905-396807.DAT',
'MSC13-5954-397402.DAT',
'MSC13-6021-398345.DAT',
'MSC13-6056-398734.DAT',
'MSC13-6073-398926.DAT',
'MSC13-6128-399727.DAT',
'MSC13-6195-400631.DAT',
'MSC13-6231-401059.DAT',
'MSC13-6257-401354.DAT',
'MSC13-6327-402319.DAT',
'MSC13-6403-403342.DAT',
'MSC13-6413-403450.DAT',
'MSC13-6441-403759.DAT',
'MSC13-6462-403996.DAT',
'MSC13-6520-404830.DAT',
'MSC13-6526-404900.DAT',
'MSC13-6572-405575.DAT',
'MSC13-6605-405972.DAT',
'MSC13-6651-406486.DAT',
'MSC13-6692-407123.DAT',
'MSC13-6707-407289.DAT',
'MSC13-6721-407443.DAT',
'MSC13-6723-407464.DAT',
'MSC13-6744-407700.DAT',
'MSC13-6746-407732.DAT',
'MSC13-6760-408037.DAT',
'MSC13-6762-408069.DAT',
'MSC13-6764-408091.DAT',
'MSC13-6765-408105.DAT',
'MSC13-6766-408114.DAT',
'MSC13-6798-408492.DAT',
'MSC13-6834-408896.DAT',
'MSC13-6872-409506.DAT',
'MSC13-6878-409574.DAT',
'MSC13-6879-409588.DAT',
'MSC13-6917-410013.DAT',
'MSC13-6935-410215.DAT',
'MSC13-6948-410498.DAT',
'MSC13-6951-410566.DAT',
'MSC13-6958-410652.DAT',
'MSC13-6972-410824.DAT',
'MSC13-6982-410935.DAT',
'MSC13-7046-411818.DAT',
'MSC13-7060-411998.DAT',
'MSC13-7105-412510.DAT',
'MSC13-7116-412636.DAT',
'MSC13-7160-413313.DAT',
'MSC13-7260-414632.DAT',
'MSC13-7264-414674.DAT',
'MSC13-7280-414839.DAT',
'MSC13-7545-418173.DAT',
'MSC13-7559-418322.DAT',
'MSC14-0058-403262.DAT',
'MSC14-0066-403346.DAT',
'MSC14-0071-403402.DAT',
'MSC14-0139-404323.DAT',
'MSC14-0145-404404.DAT',
'MSC14-0192-404943.DAT',
'MSC14-0202-405058.DAT',
'MSC14-0238-405625.DAT',
'MSC14-0311-406457.DAT',
'MSC14-0349-407068.DAT',
'MSC14-0351-407092.DAT',
'MSC14-0365-407250.DAT',
'MSC14-0471-408607.DAT',
'MSC14-0520-409308.DAT',
'MSC14-0529-409423.DAT',
'MSC14-0565-409839.DAT',
'MSC14-0621-410640.DAT',
'MSC14-0629-410749.DAT',
'MSC14-0728-412043.DAT',
'MSC14-0736-412148.DAT',
'MSC14-0753-412339.DAT',
'MSC14-0780-412639.DAT',
'MSC14-0804-413074.DAT',
'MSC14-0922-414626.DAT',
'MSC14-0926-414664.DAT',
'MSC14-0942-414833.DAT',
'MSC14-1056-415731.DAT',
'MSC14-1081-415985.DAT',
'MSC14-1108-416260.DAT',
'MSC14-1125-416627.DAT',
'MSC14-1139-416797.DAT',
'MSC14-1190-417330.DAT',
'MSC14-1231-417962.DAT',
'MSC14-8248-379511.DAT',
'MSC14-8324-380371.DAT',
'MSC14-8352-380867.DAT',
'MSC14-8357-380923.DAT',
'MSC14-8367-381032.DAT',
'MSC14-8370-381068.DAT',
'MSC14-8378-381155.DAT',
'MSC14-8424-381673.DAT',
'MSC14-8505-382711.DAT',
'MSC14-8506-382725.DAT',
'MSC14-8521-382921.DAT',
'MSC14-8569-383615.DAT',
'MSC14-8572-383650.DAT',
'MSC14-8575-383684.DAT',
'MSC14-8580-383741.DAT',
'MSC14-8637-384557.DAT',
'MSC14-8652-384728.DAT',
'MSC14-8708-385354.DAT',
'MSC14-8750-386008.DAT',
'MSC14-8754-386058.DAT',
'MSC14-8759-386113.DAT',
'MSC14-8788-386442.DAT',
'MSC14-8796-386529.DAT',
'MSC14-8802-386602.DAT',
'MSC14-8814-386859.DAT',
'MSC14-8865-387478.DAT',
'MSC14-8918-388234.DAT',
'MSC14-8923-388303.DAT',
'MSC14-8924-388316.DAT',
'MSC14-8984-388982.DAT',
'MSC14-9024-389611.DAT',
'MSC14-9043-389823.DAT',
'MSC14-9044-389833.DAT',
'MSC14-9101-390589.DAT',
'MSC14-9296-393167.DAT',
'MSC14-9304-393264.DAT',
'MSC14-9643-397652.DAT',
'MSC14-9645-397672.DAT',
'MSC14-9651-397739.DAT',
'MSC14-9654-397769.DAT',
'MSC14-9683-398278.DAT',
'MSC14-9810-399874.DAT',
'MSC14-9865-400644.DAT',
'MSC14-9870-400709.DAT',
'MSC14-9914-401221.DAT',
'MSC14-9971-402049.DAT',
'MSC14-9974-402080.DAT'
)
group by
RECORD_TYPE ,
IMSI,
DIALLED_DIGITS,
SERVICE_TYPE,
SERVICE_ID ,
MSC_SMTMSC ,
CELL_ID_ORIG ,
MS_CLASS_MARK ,
CALL_START_DATE ,
CALL_START_TIME ,
CHARGEABLE_TIME ,
DATA_VOL ,
DATA_VOL_REF ,
PULSES ,
CALL_END_DATE ,
CALL_END_TIME ,
MSC_ID_TERM ,
CELL_ID_TERM ,
MSISDN ,
A_NUMBER ,
INCOMING_ROUTE ,
OUTGOING_ROUTE ,
IMEI ,
MSC_ID ,
BSS_MAP ,
CAUSE_LOC ,
EOS ,
DISCONNECT_PARTY ,
DURATION2G ,
DURATION3G ,
RANAP_CAUSE_CODE ,
FAULT_CODE ,
PRES_AND_SCN_IND ,
OCN ,
REDIRECT_NUM ,
TRANSLATED_NUMBER ,
SEIZURE_TIME ,
file_name
)da
where da.tcount > 1
order by da.tcount desc

总结，写sql时候除了要求高效外，采用的方法也很重要。