thinkphp6使用union的话,可以直接union('select .....'),也可以先使用buildsql()来构造子查询,然后使用union做合并表查询。经验证基于oracle的数据库,thinkphp6 的union不生效,是因为Oracle的数据库链接模板里,缺少了union的关键字替换,需要修改vendor\topthink\think-orm\src\db\builder\Oracle.php,在%HAVING%后面增加个%UNION%就可以了。
一、buildsql()构造子查询
构造子查询之后,在执行的时候,出现了ORA-00918报错。原因是TP生成的oracle查询语句,会带上numrow,子查询的numrow和执行子查询的numrow出现字段的重复,通过使用field进行字段过滤即可。
错误的代码:
//没做字段过滤
$sqlOne = Db::connect('ora')->name('data.t_one')->limit(5)->buildSql();
$data = Db::connect('ora')->table($sqlOne. ' a')->select();
-- 查询语句中numrow字段出现重复
SELECT * FROM (SELECT thinkphp.*, rownum AS numrow FROM (SELECT * FROM ( SELECT * FROM (SELECT thinkphp.*, rownum AS numrow FROM (SELECT id,xm,'一区' as qy FROM data.t_one) thinkphp ) WHERE (numrow>0 AND numrow<=5) ) a) thinkphp )
增加字段过滤即可正确执行:
//没做字段过滤
$fieldsOne = "id,xm,'1区' as qy";
$sqlOne = Db::connect('ora')->name('data.t_one')->field($fieldsOne)->limit(5)->buildSql();
$fieldsQy = "id,xm,qy";
$data = Db::connect('ora')->table($sqlOne . ' a')->field($fieldsQy)->select();
-- 子查询中numrow字段在最终的结果集中过滤掉了
SELECT * FROM (SELECT thinkphp.*, rownum AS numrow FROM (SELECT id,xm,qy FROM ( SELECT * FROM (SELECT thinkphp.*, rownum AS numrow FROM (SELECT id,xm,'一区' as qy FROM data.t_one) thinkphp ) WHERE (numrow>0 AND numrow<=5) )) thinkphp )
二、使用thinkphp的union
默认thinkphp6的oralce数据库连接存在问题,模板缺少union关键字的替换。生成的查询语句,只有sqlTwo,没有union的部分。结果集也只有sqlTwo的结果集。需要修改vendor\topthink\think-orm\src\db\builder\Oracle.php,在%HAVING%后面增加个%UNION%就可以了
//没做字段过滤
$fieldsOne = "id,xm,'1区' as qy";
$sqlOne = Db::connect('ora')->name('data.t_one')->field($fieldsOne)->limit(5)->buildSql();
$fieldsTwo = "id,xm,'2区' as qy";
$sqlTwo = Db::connect('ora')->name('data.t_two')->field($fieldsTwo )->limit(5)->buildSql();
$fieldsQy = "id,xm,qy";
$data = Db::connect('ora')->table($sqlTwo)->field($fieldsQy)->unionAll($sqlOne )->select();