记录自己常写的一些sql语句

select a._id,a.innerId,(select entpName From android_pucEntp c where c.innerID=a.innerId) entpName,b.checkUnit,c._id troubleId,a.entpTypeId,a.checkItemId,c.improveStep,c.improveUnit,c.finishDate,c.closeFlag,c.checkDate From android_govCheckInspectCheckContent a ,android_insRecord b,android_govtroubleregcheck c where a.inspectID=b._ID  and a._id=c.inspectId
and (c.checkDate>date() or c.finishDate>date() or c.checkDate='')and c.closeFlag is null and improveunit is null and a.innerId=8129         


and (c.checkDate<date() and c.checkDate<>'' and c.closeFlag is null or c.finishDate<date() and c.finishDate<>'' and c.closeFlag is null)  and a.innerId=8129 
and c.finishDate<date() and c.finishDate<>'' and c.closeFlag is null  or c.checkDate<date() and c.checkDate<>'' and c.closeFlag is null


update  android_govTroubleRegCheck set checkdate='2014-05-20' where _id=119


select  improveunit,finishDate,* from android_govTroubleRegCheck where  finishDate>date()
finishDate<date() and finishDate<>'' and closeFlag is null or checkDate<date() and checkDate<>'' and closeFlag is null




select c.savetime,a._id,a.innerId,(select entpName From android_pucEntp c where c.innerID=a.innerId) entpName,b.checkUnit,c._id troubleId,a.entpTypeId,a.checkItemId,c.improveStep,c.improveUnit,c.finishDate,c.closeFlag,c.checkDate,b.assistExecuter from android_govCheckInspectCheckContent a ,android_insRecord b,android_govtroubleregcheck c where 1=1 and a.inspectID=b._ID  and a._id=c.inspectId and a.troubleType=1 and a.innerId=528959 order by c.saveTime desc


Select * From MAIN.[android_govTroubleRegCheck] Limit 1000
               
select * from android_govCheckInspectCheckContent
select * from android_pucEntp


select * from android_insRecord


select * from android_govtroubleregcheck


select c.savetime,a._id,a.innerId,(select entpName From android_pucEntp c where c.innerID=a.innerId) entpName,b.checkUnit,c._id troubleId,a.entpTypeId,a.checkItemId,c.improveStep,c.improveUnit,c.finishDate,c.closeFlag,c.checkDate,b.assistExecuter from android_govCheckInspectCheckContent a ,android_insRecord b,android_govtroubleregcheck c where 1=1 and a.inspectID=b._ID  and a._id=c.inspectId and a.troubleType=1 and a.innerId=528959 order by c.saveTime desc




Select * From MAIN.[android_govCheckInspectCheckContent] Limit 1000


select c.saveTime,a._id,a.innerId,(select entpName From android_pucEntp c where c.innerID=a.innerId) entpName,b.checkUnit,c._id troubleId,a.entpTypeId,a.checkItemId,c.improveStep,c.improveUnit,c.finishDate,c.closeFlag,c.checkDate from android_govCheckInspectCheckContent a ,android_insRecord b,android_govtroubleregcheck c  where 1=1 and a.inspectID=b._ID  and a._id=c.inspectId and a.troubleType=1 and (c.checkDate<=date() and c.checkDate<>'') and (c.finishDate<=date() or c.finishDate is null or c.finishDate='') and (c.closeFlag is null or c.closeFlag='') and (c.closeFlag is null or c.closeFlag='') order by c.saveTime desc                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  


delete from  android_govtroubleregcheck  
delete from  android_insRecord
delete from  android_pucEntp   
delete from  android_govCheckInspectCheckContent
               
select c.saveTime,a._id,a.innerId,(select entpName From android_pucEntp c where c.innerID=a.innerId) entpName,b.checkUnit,c._id troubleId,a.entpTypeId,a.checkItemId,c.improveStep,c.improveUnit,c.finishDate,c.closeFlag,c.checkDate from android_govCheckInspectCheckContent a ,android_insRecord b,android_govtroubleregcheck c where 1=1 and a.inspectID=b._ID  and a._id=c.inspectId and a.troubleType=1 and (c.checkDate>date() or c.finishDate>date() or c.checkDate='')and c.closeFlag is null and improveunit is null order by c.saveTime desc




//alter table android_govTroubleRegCheck add column saveTime  datetime


select c.saveTime,a._id,a.innerId,(select entpName From android_pucEntp c where c.innerID=a.innerId) entpName,b.checkUnit,c._id troubleId,a.entpTypeId,a.checkItemId,c.improveStep,c.improveUnit,c.finishDate,c.closeFlag,c.checkDate,b.assistExecuter from android_govCheckInspectCheckContent a ,android_insRecord b,android_govtroubleregcheck c where 1=1 and a.inspectID=b._ID  and a._id=c.inspectId and a.troubleType=1 and a.innerId=6382408 and b._id=71 order by c.saveTime desc




select c.saveTime,a._id,a.innerId,(select entpName From android_pucEntp c where c.innerID=a.innerId) entpName,b.checkUnit,c._id troubleId,a.entpTypeId,a.checkItemId,c.improveStep,c.improveUnit,c.finishDate,c.closeFlag,c.checkDate,b.assistExecuter from android_govCheckInspectCheckContent a ,android_insRecord b,android_govtroubleregcheck c where 1=1 and a.inspectID=b._ID  and a._id=c.inspectId and a.troubleType=1 and a.innerId=5533 and closeFlag=1 order by c.saveTime desc




Select * From android_pucEntp where innerID in(select innerID from android_govTroubleRegCheck where innerID in(select innerID from android_govCheckInspectCheckContent where troubleType=1))


select a._id,a.innerId,(select entpName From android_pucEntp c where c.innerID=a.innerId) entpName,b.checkUnit,c._id troubleId,a.entpTypeId,a.checkItemId,c.improveStep,c.improveUnit,c.finishDate,c.closeFlag,c.checkDate,b.assistExecuter  from android_govCheckInspectCheckContent a ,android_insRecord b,android_govtroubleregcheck c  where a.inspectID=b._ID  and a._id=c.inspectId


select * from android_insRecord


select a.troubleType,b.entpName,a._id,a.innerId,c._id troubleId,a.entpTypeId,a.checkItemId,c.improveStep,c.improveUnit,c.finishDate,c.closeFlag,c.checkDate,b.assistExecuter from android_govCheckInspectCheckContent a ,android_insRecord b,android_govtroubleregcheck c,android_pucentp d where a.inspectID=b._ID  and a._id=c.inspectId and a.troubleType=1 and a.innerID=2027291






select * from android_pucentp




(select * from a,b
如果你的a表有 x行记录，
b表y行记录，
结果有x*y行记录)




select b.entpName,b.innerID,a._id from android_insRecord a,android_pucEntp b where 1=1 and b.innerID in(select c.innerID from android_govTroubleRegCheck c where c.innerID in(select d.innerID from android_govCheckInspectCheckContent d where d.troubleType=1)) and b.entpName like '%%' order by a.savetime desc


//提出重复
//select distinct  _id,innerId,(select entpName From android_pucEntp c where c.innerID=a.innerId) entpName from


select distinct  _id,innerId,(select entpName From android_pucEntp c where c.innerID=a.innerId) entpName from(select b._id,a._id,a.innerId,(select entpName From android_pucEntp c where c.innerID=a.innerId) entpName,b.checkUnit,c._id troubleId,a.entpTypeId,a.checkItemId,c.improveStep,c.improveUnit,c.finishDate,c.closeFlag,c.checkDate from android_govCheckInspectCheckContent a ,android_insRecord b,android_govtroubleregcheck c where a.inspectID=b._ID  and a._id=c.inspectId and a.troubleType=1 order by b.savetime desc )a




//判断该字段时间为当前月份，当前年份


month(checkDate) = month(getdate()) and  year(checkDate) = year(getdate())


//查询该表所有字段的字段类型和字段长度


select ordinal_position,column_name,data_type,character_maximum_length from information_schema.columns where table_name = '表名'






//触发器
create trigger TR_insRecord_Delete before delete on android_insRecord for each row 
begin
   delete from android_govCheckInspectCheckContent where inspectID = old._id;
end