精妙sql

某个字段为用户输入的时间的字符串，有的用户输入了错误的时间格式字符串，这时候可以写一个函数取出错误的字符串。

函数如下：create or replace function check_date(datestr in varchar2) return number is
  Result date;
begin
   begin
    Result := to_date(datestr, 'yyyy-mm-dd');
  exception when others then
    Result := to_date('01-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') - 900;
    return 0;
  end;
  return 1;
end check_date;

取错误字符串的sql为：select a.end_date
from firm_contract a where check_date(a.end_date)=0。

1.

selectstory.story_no,
       story.ap,
       story.status,
       story.cp,
       story.ep,
       story.tp,
       story.filed,
       story.defered_time,
       story.finished
  from Story story
 where story.area_Id = 86
   and story.source_From = 0
 order by decode(story.ap, 1,8,0,0) +decode(story.status,3,4,0) +
          decode(story.cp, 1,1,0,0) +decode(story.ep,1,1,0,0) +
          decode(story.tp, 1,1,0,0)desc,
          substr(story.defered_Time,0,10)desc,
          story.story_No desc;

2.

1.前后两次的区别就在于如果没有'NO_AUTO_VALUE_ON_ZERO'，MySQL会从1开始自增，对于想要插入的itemID为0的数据，会按照等等同于NULL值来处理，即按照'AUTO_INCREMENT=90'这一语句来记成90；如果设置了'NO_AUTO_VALUE_ON_ZERO'，对于想要插入的itemID为0的数据就会记成'0'.
2.删除重复记录delete relay_stories from relay_stories ,
(select story_id from relay_stories group by transmission_id having count(*)>1 )
as t2 where relay_stories.story_id=t2.story_id ;。

运行成功的删除重复记录只剩下一条的sql：delete a  from media_domain a ,
(select b.domain_root ,  b.nameid from media_domain b group by b.domain_root having count(b.nameid)>1) as t1
where a.nameid!=t1.nameid and a.domain_root=t1.domain_root；

2.mysql ERROR 1030 Got error 28 from storage engine一般是硬盘没有空间了。
3.insert into relay_story_to_geography
select b.story_id,d.geography_code,d.geography_name
from  relay_story_to_company b
,relay_companies  c ,relay_geographies  d
where b.company_id=c.company_id
and c.geography_code=d.geography_code;
4.insert into relay_story_to_stock
select b.story_id,c.exchange_code,c.ticker_code
from  relay_story_to_company b, relay_company_to_stock c
where b.company_id=c.company_id
5.mysql可以批量sql如：insert into relay_stories2
select * from relay_stories a where a.story_id in (307401,307400);
6.用mysql migration toolkit导入数据到mysql里面的时候，数据量太大，
出现错误Packet for query is too large (1050338 > 1048576). You can change this value on the server by setting the max_allowed_packet' variable.
解决办法：MySQL的一个系统参数：max_allowed_packet，其默认值为1048576(1M)，
查询：show VARIABLES like '%max_allowed_packet%';
可用下述方式启动mysql
　　mysql>mysql --max-allowed-packet=32M
　　在my.ini也可以更改，需要重新启动mysql
　　在my.ini加入[mysql]max_allowed_packet=16M

7.update relay_stories a set a.transmission_id = replace (a.transmission_id ,'_86_','_086_');--48882 更新transmission_id。
8.update relay_stories a set a.transmission_id =
concat(date_format(a.date_time,'%Y%m%d%H%i%S'),substr(a.transmission_id,instr(a.transmission_id,'_')))
更新transmission_id的时间部分。
9.truncate pcom_stories;
select count(*)  from pcom_stories a;
insert into pcom_stories
select *
from relay_stories1 ;

truncate pcom_story_content;
select count(*)  from pcom_story_content a;
insert into pcom_story_to_geography    
select *
from relay_story_to_geography ;

truncate pcom_story_to_geography;
select count(*)  from pcom_story_to_geography a;
insert into pcom_story_to_geography    
select *
from relay_story_to_geography ;

truncate pcom_story_to_stock;
select count(*)  from pcom_story_to_stock a;
insert into pcom_story_to_stock    
select *
from relay_story_to_stock ;

truncate pcom_companies;
select count(*)  from pcom_companies a;
insert into pcom_companies    
select *
from relay_companies ;

truncate pcom_company_to_stock;
select count(*)  from pcom_company_to_stock a;
insert into pcom_company_to_stock    
select *
from relay_company_to_stock ;


truncate pcom_object_to_tag;
select count(*)  from pcom_object_to_tag a;
insert into pcom_object_to_tag    
select *
from relay_object_to_tag ;

delete relay_object_to_tag  from relay_object_to_tag  where obj_id=38605 and obj_type=1 and tag_id=5;
insert into relay_object_to_tag
values(1,38605,5);
select a.obj_type ,  a.obj_id,a.tag_id , count(*)
from relay_object_to_tag a group by a.obj_type ,  a.obj_id , a.tag_id having count(*)>1 order by 3 desc ;
10.pcom项目里面的sql优化。
select *
from pcom_stories a , pcom_story_content b
where a.story_id=b.story_id
and exists (
select 1
from pcom_object_to_tag c where c.tag_id in (24,111)
and c.obj_id = a.story_id
);
11.测试story接口是否正确插入

delete pcom_story_to_stock from pcom_story_to_stock ;
select * from pcom_story_to_stock a where a.story_id=62727 order by a.story_id desc;

select * from pcom_companies a order by a.company_id desc ;
select * from pcom_feed_archives a order by a.farchive_id desc ;
select * from pcom_stories a where a.story_id=62704;
select * from pcom_tags a where a.tag_id in (10,118)
delete pcom_stories from pcom_stories  where story_id=62704 ;
select * from pcom_stories a order by a.story_id desc limit 0,3;   62702
select * from pcom_story_content a order by a.story_id desc limit 0,3;
select * from pcom_object_to_tag a where a.obj_type=1 and a.obj_id=62703 order by a.obj_id desc limit 0,3;
select * from pcom_story_to_company a where a.story_id=62704 -- 有问题 没有记录  order by a.story_id desc limit 0,3;
select * from pcom_story_to_geography a where a.story_id=62703 -- 这个多了一个china 没有问题 order by a.story_id desc limit 0,3;
select * from pcom_story_to_stock a where a.story_id=62703 order by a.story_id desc limit 0,3;
select * from pcom_files a where a.story_id=62703 order by a.story_id desc limit 0,3;
select * from pcom_feed_archives a where a.; -- fsourceid有问题  order by a.story_id desc limit 0,3;

12.批量更新
update pcom_stories a set a.photos=0;
update pcom_stories a set a.videos=0;
update pcom_stories a set a.photos=
(select count(*)
from pcom_files b where a.story_id=b.story_id
and b.file_type=1 group by b.story_id,b.file_type);
update pcom_stories a set a.photos=a.photos+
IFNULL((select count(*)
from pcom_files b where a.story_id=b.story_id
and b.file_type=2 group by b.story_id,b.file_type),0);
update pcom_stories a set a.videos=
(select count(*)
from pcom_files b where a.story_id=b.story_id
and b.file_type=2 group by b.story_id,b.file_type);
select a.story_id ,a.photos, a.photos+
IFNULL((select count(*)
from pcom_files b where a.story_id=b.story_id
and b.file_type=4 group by b.story_id,b.file_type),0)
from  pcom_stories a where a.story_id=63229;
13.快速见表：
create table b as
select *   from pcom_stories where 1<>1;只复制结构
14.快速插入记录：
insert into b(story_id, fsource_id, headline)
select story_id,fsource_id,headline from pcom_stories ;
15.这个函数比较牛：
select a.title,a.username,b.adddate from table a,
(select max(adddate) adddate from table where table.title=a.title) b，一对多的时候，取一方的基本值和多方的最大值。

select a.title , a.story_no , a.area_id , a.defered_time ,
(select count(b.industry_id) nums
from story_industry b where b.story_no=a.story_no and b.area_id=a.area_id)
from story a ;可以在oracle执行，
select a.title , a.story_no , a.area_id , a.defered_time , t1.nums
from story a ,
(select count(b.industry_id) nums
from story_industry b where b.story_no=a.story_no and b.area_id=a.area_id ) t1 这条sql在oracle执行出错。
16.插入pcom_story_to_stock表：
insert into pcom_story_to_stock
select distinct  b.story_id,a.exchange_code,a.ticker_code
from pcom_company_to_stock a ,pcom_story_to_company b
where a.company_id=b.company_id;
17时间函数diff：
delete pcom_stories from pcom_stories where date_time >  now()-interval 1 day  ;now()同sysdate()
18.SELECT (CASE WHEN EXISTS(SELECT * FROM Handle b WHERE b.HandleID = 1) THEN MIN(HandleID) + 1 ELSE 1 END) as HandleID

　　FROM Handle

　　WHERE NOT HandleID IN (SELECT a.HandleID - 1 FROM Handle a)
19.修改industry为subject。
select * from pcom_tags a where a.string1 in ('LAW','TDS','POL','MAV');

update pcom_object_to_tag a set a.obj_type=2 and a.tag_id = 157
where a.tag_id = 3126;
update pcom_object_to_tag a set a.obj_type=2 and a.tag_id = 187
where a.tag_id = 3127;
update pcom_object_to_tag a set a.obj_type=2 and a.tag_id = 139
where a.tag_id = 3128;
update pcom_object_to_tag a set a.obj_type=2 and a.tag_id = 160
where a.tag_id = 3129;
delete pcom_tags from pcom_tags where tag_id in (3126,3127,3128,3129)

update pcom_object_to_tag a set a.obj_type=1
where a.tag_id = 157;
update pcom_object_to_tag a set a.obj_type=1
where a.tag_id = 187;
update pcom_object_to_tag a set a.obj_type=1
where a.tag_id = 139;
update pcom_object_to_tag a set a.obj_type=1 and a.tag_id = 160
where a.tag_id = 160;
20.update pcom.pcom_stories a set a.transmission_id = substr(a.transmission_id,instr(a.transmission_id,'_')+1)
where a.story_id<62710;

21.检查pcom_stories的插入情况：
select count(*) from pcom_stories a ；--55177

select a.transmission_id , count(*)
from pcom_stories a group by a.transmission_id having count(*) >1 order by 2 desc ;无
22.检查pcom_story_content的导入情况：
select count(*)    --72138
from pcom_story_content a ;
select a.story_id , a.language ,count(*)
from pcom_story_content a group by a.story_id , a.language  having count(*)>1 order by 2 desc ;无
22.pcom_object_to_tag的验证：
select count(*) from pcom_object_to_tag;



select * from pcom_stories a where a.story_id = 7;
select *
from pcom_object_to_tag b where b.tag_id in (
select a.tag_id
from pcom_tags a where a.type =2)

23.pcom_story_to_company 的验证：select count(*) from pcom_story_to_company a
24.update pcom_page_navigators a set a.navigator_leadpath='cn';
update pcom_page_segments a set a.segment_leadpath='cn'
25.测试接口：
mysql：select * from pcom_stories a where a.transmission_id = 'prna_086_1112551';
select * from pcom_story_content a where a.story_id=63263 ;
select * from pcom_story_to_company a where a.story_id=63263;
select * from pcom_companies a where a.company_id=1011 ;
select * from pcom_files a where a.story_id=63263 ;
select * from pcom_object_to_tag a where a.obj_type=1 and a.obj_id=63263;
select * from pcom_tags a where a.tag_id in (10,139);
select * from pcom_story_to_stock a where a.story_id=63263;
select * from pcom_feed_archives a where a.transmission_id='prna_086_1112551';

select *
from pcom_stories a where a.story_id not in
(
select b.story_id
from pcom_story_content b
)
oracle：select *
from getstoryandinfo a where a.story_no='1112551';
select * from getstorycompanyinfo a where a.story_no='1112551';
select * from getstoryfile a where a.story_no ='1112551';
select * from getstoryindustryinfo a where a.story_no='1112551';
select * from getstorystock a where a.story_no='1112551';
select * from getstorysubjectinfo a where a.story_no ='1112551';

select a.firm_id
from story a where a.story_no='1112551';

select *
from firm a where a.firm_id=9573282462 ;
26.
删除与story相关联的无用数据：
delete  a
from pcom_story_content a
where not  exists
(
select 1
from pcom_stories b where a.story_id=b.story_id
);
delete  a
from pcom_story_to_company a
where not  exists
(
select 1
from pcom_stories b where a.story_id=b.story_id
);
delete  a
from pcom_story_to_geography a
where not  exists
(
select 1
from pcom_stories b where a.story_id=b.story_id
);
delete  a
from pcom_story_to_stock a
where not  exists
(
select 1
from pcom_stories b where a.story_id=b.story_id
);
delete  a
from pcom_object_to_tag a
where not  exists
(
select 1
from pcom_stories b where a.obj_id=b.story_id and a.obj_type=1
);

27.检查导入数据的正确性sql：
--story content中的story id在stories表中不存在
 select count(*)
from pcom_story_content a
where not  exists
(
select 1
from pcom_stories b where a.story_id=b.story_id
);
  --pcom_story_to_company中的story id在stories表中不存在
 select count(*)
from pcom_story_to_company a
where not  exists
(
select 1
from pcom_stories b where a.story_id=b.story_id
);
  --pcom_story_to_geography中的story id在stories表中不存在
 select count(*)
from pcom_story_to_geography a
where not  exists
(
select 1
from pcom_stories b where a.story_id=b.story_id
);
  --pcom_story_to_stock中的story id在stories表中不存在
 select count(*)
from pcom_story_to_stock a
where not  exists
(
select 1
from pcom_stories b where a.story_id=b.story_id
);
  --pcom_object_to_tag中的story id在stories表中不存在
 select count(*)
from pcom_object_to_tag a
where not  exists
(
select 1
from pcom_stories b where a.obj_id=b.story_id and a.obj_type=1
);

 --story content中的story id在stories表中不存在
  select *
from pcom_story_content a
where not  exists
(
select 1
from pcom_stories b where a.story_id=b.story_id
);
--以上的处理结果
delete a
from pcom_story_content a
where not  exists
(
select 1
from pcom_stories b where a.story_id=b.story_id
);
delete  a
from pcom_story_to_company a
where not  exists
(
select 1
from pcom_stories b where a.story_id=b.story_id
);
delete  a
from pcom_story_to_geography a
where not  exists
(
select 1
from pcom_stories b where a.story_id=b.story_id
);
delete  a
from pcom_story_to_stock a
where not  exists
(
select 1
from pcom_stories b where a.story_id=b.story_id
);
delete  a
from pcom_object_to_tag a
where not  exists
(
select 1
from pcom_stories b where a.obj_id=b.story_id and a.obj_type=1
);
  --检测sql,stories中, photos, videos或files大于0但是在files表中无记录
  select a.photos , a.videos , a.files ,
  (select count(*)
from pcom_files b where a.story_id=b.story_id
and b.file_type=1) photofiles ,
(select count(*)
from pcom_files b where a.story_id=b.story_id
and b.file_type=2) videofiles,
(select count(*)
from pcom_files b where a.story_id=b.story_id
and b.file_type=3) files
 from pcom_stories a
 where a.photos!= (select count(*)
from pcom_files b where a.story_id=b.story_id
and b.file_type=1) or  
a.videos!=(select count(*)
from pcom_files b where a.story_id=b.story_id
and b.file_type=2) or
a.files!=(select count(*)
from pcom_files b where a.story_id=b.story_id
and b.file_type=3)
 ;
 

28.mysql中检查数据正确性的函数：
CREATE FUNCTION checkbatchdatacorrect () RETURNS VARCHAR(255)

BEGIN
    DECLARE rsStr VARCHAR(255);
  DECLARE nums   INT;
  set nums = 0;
  set rsStr = '';
   select count(*) into nums
from pcom_story_content a
where not  exists
(
select 1
from pcom_stories b where a.story_id=b.story_id
);
if(nums>0) then
  set rsStr = concat(rsStr,'&&pcom_story_content error!');
end if;
 select count(*) into nums
from pcom_story_to_company a
where not  exists
(
select 1
from pcom_stories b where a.story_id=b.story_id
);
if(nums>0) then
  set rsStr = concat(rsStr,'&&pcom_story_to_company error!');
end if;
 select count(*) into nums
from pcom_story_to_geography a
where not  exists
(
select 1
from pcom_stories b where a.story_id=b.story_id
);
if(nums>0) then
  set rsStr = concat(rsStr,'&&pcom_story_to_geography error!');
end if;
 select count(*) into nums
from pcom_story_to_stock a
where not  exists
(
select 1
from pcom_stories b where a.story_id=b.story_id
);
if(nums>0) then
  set rsStr = concat(rsStr,'&&pcom_story_to_stock error!');
end if;
 select count(*) into nums
from pcom_object_to_tag a
where not  exists
(
select 1
from pcom_stories b where a.obj_id=b.story_id and a.obj_type=1
);
if(nums>0) then
  set rsStr = concat(rsStr,'&&pcom_object_to_tag error!');
end if;
  select count(*) into nums
 from pcom_stories a
 where a.photos!= (select count(*)
from pcom_files b where a.story_id=b.story_id
and b.file_type=1) or  
a.videos!=(select count(*)
from pcom_files b where a.story_id=b.story_id
and b.file_type=2) or
a.files!=(select count(*)
from pcom_files b where a.story_id=b.story_id
and b.file_type=3);
if(nums>0) then
  set rsStr = concat(rsStr,'&&photosorvediosorfiles error!');
end if;
    RETURN rsStr;
END


29.插入 MySQL 时，斜杠被当作转义字符，所以要插入斜杠应该将插入“\\”，否则斜杠会丢失。

也就是说在用 SQL 语句向 MySQL 插入、更新记录时，不仅要考虑将单引号替换为两个单引号，还要考虑将斜杠替换成两个斜在杠。
在java中用replace("\\", "/")。
30.insert into pcom_light_news(headline,content,create_time,CHANNEL_ID,editor,
source,blurb,status)
select a.headline,a.content , a.create_time,a.CHANNEL_ID,
a.editor,a.source,a.blurb,a.status
from pcom_light_news_all a
where a.CHANNEL_ID in
(
75,
35,
63,
66,
64,
65,
74
)
30.insert into pcom_channel(id,channel_name)
select a.id
,a.channel_name
from pcom_channel_all a
where a.id in
(
75,
35,
63,
66,
64,
65,
74
);
31.添加冗余字段来加快sql的执行速度.
32.某个字段如果要表示有哪些类型和这些类型的个数的话，可以用位表示类型，例如pcom_story表里面的ext1字段。
33.同步程序需要执行的segment和navigator：
select a.segment_leadpath ,  a.segment_filepath , a.segment_name ,a.segment_params,
b.template_name , c.function_name
from pcom_page_segments a ,pcom_page_templates b , pcom_page_functions c
where a.segment_id in
(124,125,36,20,24,139,30,82,91,84,89,26,90,86,19,28,34,39,37,94,92,88,93,41,102,
    126,127,60,61,62,63,112,111,110,109,64,108,107,65,67,68,47,48,106,105,104,123,46,103,
    191,192,168,176,170,171,190,174,180,186,181,184,172,185,182,169,173,175,178,177,189,187,183,188,179)
  and a.template_id=b.template_id
  and b.function_id=c.function_id
  order by 1,2,3;
 
  update pcom_page_segments a set a.generate_interval=0 ;
  update pcom_page_navigators a set a.generate_interval =0 ;
  update pcom_page_segments a set a.generate_interval=1 where a.segment_id in
  (
  124,125,36,20,24,139,30,82,91,84,89,26,90,86,19,28,34,39,37,94,92,88,93,41,102,
    126,127,60,61,62,63,112,111,110,109,64,108,107,65,67,68,47,48,106,105,104,123,46,103,
    191,192,168,176,170,171,190,174,180,186,181,184,172,185,182,169,173,175,178,177,189,187,183,188,179
  );
 
  update pcom_page_navigators a set a.generate_interval =1 where a.navigator_id in
  (
  80,53,54
  )



update pcom_page_segments a set a.generate_time = now()-interval 5 day  ;
update pcom_page_navigators a set a.generate_time = now()-interval 5 day  ;

select a.generate_time
from pcom_page_segments a ;


34.
select date_format('2011-12-26 16:48:30','%Y-%m-%d %H:%i:%s') ;
35.取出所有没有story的行业select *
from pcom_tags a
where a.type=1 and  not exists
(
select 1
from pcom_object_to_tag b where a.tag_id=b.tag_id
and b.obj_type=1
) and a.tag_id in
(
select substr(c.segment_params,instr(c.segment_params,'tags')+7,length(substr(c.segment_params,instr(c.segment_params,'tags')+7))-
length(substr(c.segment_params,instr(c.segment_params,'with_stocks')-3)))
from pcom_page_segments c
where c.segment_id=24
)： 这个sql有问题，总是只能取得第一个不存在的id，可能是in操作符对结果集集合和字符串处理方式不一致，具体什么原因还不清楚。
改为：select *
from pcom_tags a
where a.type=1 and  not exists
(
select 1
from pcom_object_to_tag b where a.tag_id=b.tag_id
and b.obj_type=1
) and exists
(
select   1
from pcom_page_segments c
where c.segment_id=26
and FIND_IN_SET(a.tag_id, substr(c.segment_params,instr(c.segment_params,'tags')+7,length(substr(c.segment_params,instr(c.segment_params,'tags')+7))-
length(substr(c.segment_params,instr(c.segment_params,'with_stocks')-3)))  )>0
);因为segment_params除了第一个id其他都是在id前面有一个空格，所有修改为：
select *
from pcom_tags a
where a.type=1 and  not exists
(
select 1
from pcom_object_to_tag b where a.tag_id=b.tag_id
and b.obj_type=1
) and exists
(
select   substr(c.segment_params,instr(c.segment_params,'tags')+7,
length(substr(c.segment_params,instr(c.segment_params,'tags')+7))-
length(substr(c.segment_params,instr(c.segment_params,'with_stocks')-3)))
from pcom_page_segments c
where c.segment_id=82
and FIND_IN_SET(concat(' ',a.tag_id), concat(' ',substr(c.segment_params,instr(c.segment_params,'tags')+7,
length(substr(c.segment_params,instr(c.segment_params,'tags')+7))-
length(substr(c.segment_params,instr(c.segment_params,'with_stocks')-3))) )  )>0
);

36.date_format('2011-12-26 16:48:30','%Y-%m-%d %H:%i:%s')


37.精妙的sql：REPLACE INTO sph_counter SELECT 1, MAX(story_id) FROM pcom_story_content2。

38.mysql中用于搜索引擎的视图代码：  create or replace view content2 as
  select (case when language='en_US' then concat(story_id,'01')
  when language='zh_CN' then concat(story_id,'02') when language='zh_TW' then concat(story_id,'03') else
  concat(story_id,'04') end) AS id, UNIX_TIMESTAMP(create_time) AS date_added ,  headline ,  content_text ,
  story_id
  FROM pcom_story_content;
38.SELECT SUBSTRING_INDEX('123-890-ibees-zxj','-',-1) ;  

39.替换回车：update new_journalists a set a.weibo_addr = replace(a.weibo_addr,'\n','');


40.在new_journalists_xprn和t_journalists_xprn的name字段上面添加索引后，搜读明显的提升了，头一次感到索引真的很神奇啊。
41. mysql集合的差：select member_uid from
(select a.member_uid
from  media_members a where a.roles=1 group by a.email having count(*)>1   
union
select b.member_uid
from media_members b where b.roles=1 group by b.email having count(*)=1
union all
select c.member_uid
from media_members c where c.roles=1 )t1 group by member_uid having count(*)=1

42.oracle方面的优化问题：
下面两个sql是同样的功能，但是速度确差很多：
select count(date_month) as amount,date_month from
(
  select to_char(to_date(subStr(t.clear_time,1,10),'yyyy-mm-dd'),'yyyy q') date_month from story t
  where exists(
       select 1
       from firm_contract fc, agreement_service ags, account_detail ad, keep_accounts ka
       where t.story_no=ka.story_no
       and ka.accounts_id=ad.accounts_id
       and ad.service_id=ags.service_id
       and ags.firmcontract_id=fc.contract_id
       and fc.is_renew=1 and ka.status != 3
       and fc.firm_id not in (817, 924, 8822, 25263)
    )
    and length(subStr(t.clear_Time,1,10)) = 10
    and subStr(t.clear_time,1,10) >= '2012-01-01'
    and subStr(t.clear_time,1,10) < '2012-05-01'
    and t.area_id=86
) group by date_month order by date_month asc
用时：37.032秒   在执行计划里面看到 这个用到了filter操作
select count(distinct t2.story_no) as amount,date_month from
(
  select to_char(to_date(subStr(t.clear_time,1,10),'yyyy-mm-dd'),'yyyy q') date_month , t.story_no from story t
  ,(
       select ka.story_no  
       from firm_contract fc, agreement_service ags, account_detail ad, keep_accounts ka
       where  ka.accounts_id=ad.accounts_id
       and ad.service_id=ags.service_id
       and ags.firmcontract_id=fc.contract_id
       and fc.is_renew=1 and ka.status != 3
       and fc.firm_id not in (817, 924, 8822, 25263)
    ) t1
    where t.story_no=t1.story_no
    and length(subStr(t.clear_Time,1,10)) = 10
    and subStr(t.clear_time,1,10) >= '2012-01-01'
    and subStr(t.clear_time,1,10) < '2012-05-01'
    and t.area_id=86
)t2 group by date_month order by date_month asc;
用时：2秒
用到了hash join。

43.mysql修改字段名称：
ALTER TABLE `media_platform`.`media_stories` CHANGE COLUMN `billed_account
billed_account
billed_account
billed_account` `billed_account` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '公司的firm_code，可能一个firm_code对应多个firm_name';
44.MYSQL的分区字段，必须包含在主键字段内。 说明：http://www.diybl.com/course/7_databases/mysql/myxl/20100721/474690.html


45.SELECT b.firm_id
from FIRM_CONTRACT a , firm b
where a.firm_id=b.firm_id
group by b.firm_id having  count(distinct a.is_renew)*count(a.is_renew)=sum(a.is_renew)这个是oracle数据库里面取出所有合同都是新合同的公司id，
表示新合同的字段是is_renew为1，根据firm_id分组，如果is_renew的种类个数乘以is_renew等于 sum(is_renew)表示这个公司的所有的is_renew都是1.

46.mysql中导入dump文件的时候，报错，[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'USING BTREE,
  KEY `language` (`language`) USING BTREE,
  KEY `create_time` (`' at line 32
打开要导入的文件在里面搜索 BTREE 找到如下内容

UNIQUE KEY `user_name` (`suppliers_name`) USING BTREE ，修改为

UNIQUE KEY `user_name` USING BTREE  (`suppliers_name`) ，

即把USING BTREE 放到索引字段前面即可.

再次导入顺利通过了

47.mysql中时间加两天表示select ADDDATE(now(),interval 2 day);

48.mysql中update操作中用子查询的写法：
update media_stories as a ,
(select b.bloomberg_info  from media_stories b where b.story_id=136) as c
set a.bloomberg_info=c.bloomberg_info where a.story_id=134;

49.mysql中的联合索引，对字段的顺序是敏感的，有最左前缀匹配原则，

50.select story.story_no,
       story.ap,
       story.status,
       story.cp,
       story.ep,
       story.tp,
       story.filed,
       story.defered_time,
       story.finished
  from Story story
 where story.area_Id = 86
   and story.source_From = 0
 order by decode(story.ap, 1, 16, 0, 0) + decode(story.status, 3, 8, 0) +
          decode(story.cp, 1, 2, 0, 0) + decode(story.ep, 1, 2, 0, 0) +
          decode(story.tp, 1, 2, 0, 0) +
          (case
             when (story.filed = 1 and story.status = 1 and
                  story.finished = '' and story.defered_time < to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')) then
              1
             else
              0
           end)
          
          desc,
          substr(story.defered_Time, 0, 10) desc,
          story.story_No desc;