select year,sum(nvl(case when jclx='1' then jccs end,0)) as rcjcjccs,sum(nvl(case when jclx='1' then jccs end,0))-sum(nvl(case when jclx='1' then hgs end,0)) as rcjcbhgs ,sum(nvl(case when jclx='2' then jccs end,0)) as zxjcjccs,sum(nvl(case when jclx='2' then jccs end,0))-sum(nvl(case when jclx='2' then hgs end,0)) as zxjcbhgs ,sum(nvl(case when jclx='3' then jccs end,0)) as fxjcjccs,sum(nvl(case when jclx='3' then jccs end,0))-sum(nvl(case when jclx='3' then hgs end,0)) as fxjcbhgs ,sum(nvl(case when jclx='4' then jccs end,0)) as xkjcjccs,sum(nvl(case when jclx='4' then jccs end,0))-sum(nvl(case when jclx='4' then hgs end,0)) as xkjcbhgs ,sum(nvl(case when jclx='5' then jccs end,0)) as zgfccs,sum(nvl(case when jclx='5' then jccs end,0))-sum(nvl(case when jclx='5' then hgs end,0)) as zgfcbhgs ,sum(nvl(case when jclx='7' then jccs end,0)) as txjcjccs,sum(nvl(case when jclx='7' then jccs end,0))-sum(nvl(case when jclx='7' then hgs end,0)) as txjcbhgs ,sum(nvl(case when jclx='9' then jccs end,0)) as sjjcjccs,sum(nvl(case when jclx='9' then jccs end,0))-sum(nvl(case when jclx='9' then hgs end,0)) as sjjcbhgs ,sum(nvl(case when jclx='10' then jccs end,0)) as yyjcjccs,sum(nvl(case when jclx='10' then jccs end,0))-sum(nvl(case when jclx='10' then hgs end,0)) as yyjcbhgs ,sum(nvl(case when jclx='11' then jccs end,0)) as zchcjccs,sum(nvl(case when jclx='11' then jccs end,0))-sum(nvl(case when jclx='11' then hgs end,0)) as zchcbhgs ,sum(nvl(case when jclx='12' then jccs end,0)) as bahcjccs,sum(nvl(case when jclx='12' then jccs end,0))-sum(nvl(case when jclx='12' then hgs end,0)) as bahcbhgs from ( select case when jcbz='1' then '5' else jclx end as jclx, year,jccs,hgs from ( select rec.jclx,rec.jcbz, rec.year, COUNT( rec.ID ) AS jccs, SUM( CASE aa10.AAA103 WHEN '不符合' THEN '0' ELSE '1' END) as hgs from t_inspect_record rec join AMR_ENTERPRISE.t_pub_enterprise ent on rec.ztlsh = ent.id JOIN aa10a1 aa10 ON ( rec.JCJG = aa10.AAA102 AND aa10.AAA100 = 'JCJG' ) WHERE rec.ZT = 1 AND rec.STATUS = 1 and (rec.sszy='10' or rec.sszy='11') group by rec.jclx,rec.jcbz,rec.year order by rec.jclx,rec.jcbz,rec.year) ) where 1=1 and year=':year' group by year order by year 以上是一个sql,我想通过正则表达式,以from、join为关键字,分析出查询sql中涉及的表名
时间: 2023-12-02 10:06:06 浏览: 140
Oracle用decode函数或CASE-WHEN实现自定义排序
5星 · 资源好评率100%
可以使用正则表达式来解析出SQL查询语句中涉及到的表名。以下是一个示例正则表达式:
```
/(from|join)\s+(\w+)/i
```
该正则表达式可以匹配以`from`和`join`关键字开始后面跟着一个或多个单词的字符串。其中,`\s+`匹配空格或制表符,`\w+`匹配一个或多个字母、数字或下划线。
使用该正则表达式,可以在PHP中使用`preg_match_all`函数来解析出SQL查询语句中涉及到的表名,如下所示:
```php
$sql = "SELECT year, sum(...) FROM t_inspect_record rec JOIN AMR_ENTERPRISE.t_pub_enterprise ent ON rec.ztlsh = ent.id WHERE 1=1 AND year=':year' GROUP BY year ORDER BY year";
preg_match_all('/(from|join)\s+(\w+)/i', $sql, $matches);
$tables = array_unique($matches[2]);
print_r($tables);
```
该代码会输出SQL查询语句中涉及到的所有表名,如`"t_inspect_record"`,`"AMR_ENTERPRISE.t_pub_enterprise"`等。其中,`array_unique`函数用于去重。
阅读全文