1.递归sql
WITH corp (CORPID, corpname, parentcorpid,corpstate) AS ( SELECT
corp1.CORPID, corp1.corpname,corp1.parentcorpid,corp1.corpstate FROMt_corpinfo corp1
where corp1.CORPID='null'
UNION ALL
SELECT CHILD.CORPID,CHILD.corpname,CHILD.parentcorpid,CHILD.corpstate
FROM corp PARENT, t_corpinfo CHILD
WHERE PARENT.CORPID = CHILD.parentcorpid )
select
corp.CORPID from corp where corp.corpstate = '0'
2.分页sql
SELECT * FROM (
SELECT B.*, ROWNUMBER() OVER() AS TN FROM
( SELECT * FROM TBL_STORE ) AS B ) AS A
WHERE A.TN BETWEEN #{startNum} AND #{endNum}
3.行转列
select max(case when char(crdate)='2016-06' then amt end )as amt1,max(case when char(crdate)='2016-07' then amt end )as amt1,
max( case when char(crdate)='2016-08' then amt end) as amt2,flag
from (
select VALUE(sum(amt),0) amt,SUBSTR(char(t1.CRTDAT),1,7) as CRDATE ,t1.FLAG from t_order t1 where t1.FLAG='1' and SUBSTR(char(t1.CRTDAT),1,7) between '2016-05' and '2016-10' group by SUBSTR(char(t1.CRTDAT),1,7) ,t1.flag
union all
select VALUE(sum(amt),0) amt,SUBSTR(char(t2.CRTDAT),1,7) as CRDATE,t2.FLAG from t_order t2 where t2.FLAG='2' and SUBSTR(char(t2.CRTDAT),1,7) between '2016-05' and '2016-10' group by SUBSTR(char(t2.CRTDAT),1,7) ,t2.flag
) group by flag