几个重要的sql语句

查询当前表中具有重复行的行数

SELECT count(channel_mid) FROM t_channel group by channel_mid

查询当前表中有重复行的数据

SELECT channel_mid FROM t_channel group by channel_mid having count(channel_mid)>1

分页和日期转换成字符查询

select * from (     
select row_.* , rownum row_num from (      
select * from T_AN_UNIQUE_NEW 
where to_char(NEWS_DATE,'yyyy-mm-dd hh24:mi:ss')>='2010-12-23 00:00:01' 
and to_char(NEWS_DATE,'yyyy-mm-dd hh24:mi:ss')<='2010-12-30 23:59:59' 
order by NEWS_DATE DESC  
) row_        
where rownum <=30 ) 
where row_num >=1  

左连接查询

SELECT N.*,M.IS_LINK,M.IS_TOP,M.LOCK_ORDER,M.WINDOW_ID,M.CHANNEL_ID   
		 FROM T_AN_UNIQUE_LINK N LEFT JOIN T_AN_UNIQUE_MNG M ON N.ID=M.NEWS_ID 
		 WHERE    M.LOCK_ORDER >0 AND M.IS_LINK=1 AND M.CHANNEL_ID='82Z0000' AND M.WINDOW_ID =1872     
		 ORDER BY M.LOCK_ORDER ASC NULLS LAST,   N.NEWS_DATE DESC ,  N.CREATE_TIME DESC

创建序列、关联触发器

create sequence SEQ_GS_TRADESITE
minvalue 1
maxvalue 999999999999999999999999999
start with 1001
increment by 1
cache 20;
-- Create trigger 关联 sequence
create or replace trigger TR_GS_TRADESITE
before insert on GS_TRADESITE for each row
begin
select SEQ_GS_TRADESITE.nextval into :new.id from dual;
end;