首页 > 代码库 > 一条大而全SQL与函数之前的抉择：谁是更为有效的SQL？

一条大而全SQL与函数之前的抉择：谁是更为有效的SQL？

2024-07-21 05:09:35   214人阅读

转载请注明出处：转载请注明出处：jiq?钦‘s technical Blog

特别注意，这里讨论的是Oracle11g数据库，对于SQL Server，MYSQL等其他数据库不一定适用。


1、问题描述

当前系统中有两张表，一张是产品表，一张是附件表。

PRODUCT（PRODUCTID，PRODUCTNAME，PRODUCTPRICE，PICTURE）

ATTACHMENT（ATTACHMENTID，FILEPATH，BUSINESSTYPE，BUSINESSID）

其中产品表包含产品ID，产品名称，产品价格，图片等字段，附件表包含附件ID，附件全路径，附件所属模块编号（比如产品模块是105），附件所属记录ID（这里就是产品ID）。

需求是在查询全部产品界面显示所有产品的列表，并且每一项记录后面都需要一个“是否有视频”的字段指示当前产品记录是否包含视频。此外还要在上方的查询条件区域能够按照有无视频进行产品的查询。


2、解决思路

涉及左链接，正则表达式、函数、去重、ROWNUM等概念

方式1： 一条大而全的SQL语句

我做这个的时候，首先想到要用一个比较复杂的SQL语句拼出来一条SQL语句，能够查出最终要显示的所有产品记录。

所以首先想到左链接，因为需要保障左边产品表的所有记录都能够显示出来，不会有任何遗漏，所以写了这样一个SQL语句：

select T.*,A.BUSINESSTYPE
from PRODUCT T left join ATTACHMENT A
on
T.PRODUCTID=A.BUSINESSID and regexp_like(A.FILEPATH, '^.*.(rmvb|avi|mtv|3gp|flv|mpeg|mov)')

将产品表和附件表进行左链接，右边的附件表只有在满足BUSINESSID等于左边产品ID，并且附件是视频文件的情况下才连接到左表记录。

注意：其中where条件中用到了正则表达式判断，即regexp_like函数。


但是当一条产品有两个视频文件的时候问题就出来了，比如ID为331的产品有两个视频附件，那么ATTACHMENT表中就有两条附件记录满足：

T.PRODUCTID=A.BUSINESSID and regexp_like(A.FILEPATH, '^.*.(rmvb|avi|mtv|3gp|flv|mpeg|mov)')
where条件，那么这两条附件记录都会连接到左表，因为左链接并不是说以左表为基准，左表有多少条，连接出来就是多少条，在这种情况下连接出来的记录数目就会比原有的左表的记录数目多。这就导致连接结果是这样：

PRODUCTID  ......   BUSINESSID BUSINESSTYPE 

331                                331                    105

331                                331                    105

你可能已经想到了，我们可以进行去重处理，于是出现下面语句：

select distinct T.*,A.BUSINESSTYPE
from PRODUCT T left join ATTACHMENT A
on
T.PRODUCTID=A.BUSINESSID and regexp_like(A.FILEPATH, '^.*.(rmvb|avi|mtv|3gp|flv|mpeg|mov)')

因为看似上面的重复的记录一模一样，所以使用distinct 关键字来进行记录去重处理。

很不幸这条语句编译出错，我开始也以为一个产品有两个以上视频的话，连接出来的重复的记录是一模一样的。

可惜在我这个场景下不一样，因为产品表里面有个PICTURE字段，是BLOB类型，存储图片的二进制数据，不允许用distinct关键字去重。


可以通过下面的方式进行去重：

select * from
(
	select distinct T.*,A.BUSINESSTYPE,ROW_NUMBER() OVER(PARTITION BY T.PRODUCTID order by T.PRODUCTNAME) rk
	from PRODUCT T left join ATTACHMENT A
	on
	T.PRODUCTID=A.BUSINESSID and regexp_like(A.FILEPATH, '^.*.(rmvb|avi|mtv|3gp|flv|mpeg|mov)')
)
where rk=1

即根据重复的PRODUCTID为基准，只要重复都只取第一条记录。

就这样一个满足条件的SQL就已经构造出来了，查询的结果就是产品表所有记录有且仅列出来一次，每条产品记录后面跟上一个字段BUSINESSTYPE，如果这个字段有值，标识这条产品记录有视频附件，如果没有值标识没有视频附件。

至于查询的时候，以有无视频作为查询条件，只需要在上面这条SQL外面再套一层，加上where BUSINESSTYPE=‘105‘这个条件即可。


方式2： 借助函数等高级特性解决

将上面的方法与项目组中更加有经验的同事交流后发现自己这个方式不够优雅，更多地是觉得自己在数据库方面经验还太浅，完全可以有很多优雅的方式解决这个问题。

我上面的解决方式首先太过复杂，其次是通过连接之后记录中BUSINESSTYPE字段有没有值，是不是105来判断该产品有无视频。

更多时候我们可能不仅仅是需要保留连接后右边表的某个字段的值，而更多可能是需要针对某些列进行值的转换！！！

比如我们这里就想在产品表后面加一列，这列的值如果是0标识没有视频附件，如果是1标识有视频附件。另外的场景还有我们就想在产品表后面加上一列，这列的值表示产品拥有的附件的个数，这些信息哪儿来，肯定是从附件表来，但是光靠两个表的链接能做到么？


这个时候我们要借助函数来实现：

select distinct T.*,FUNC_HASATTACHMENT(t.PRODUCTID)
from PRODUCT T

看这种方式多优雅，可拓展性多强，后面还可以加更多函数，你想要知道什么信息都可以，理论上函数都能实现。

下面看这个函数如何根据产品ID的值，返回是否有附件（0-没有有视频附件，1-有视频附件）

create OR REPLACE function FUNC_HASATTACHMENT(productID in decimal)
return number
is
	num number;
	result number;
BEGIN
	result:=0;
	select count(ATTACHMENTID) into num from ATTACHMENT 
	where BUSINESSTYPE='105' and BUSINESSID=productID and regexp_like(A.FILEPATH, '^.*.(rmvb|avi|mtv|3gp|flv|mpeg|mov)');

	if(num>0) then result:=1;
	else result:=0;
	endif;

	return result;
END;

你看我们只需要传入产品ID，根据这个ID在附件表里面搞一搞就能搞出来这个产品有没有视频附件。

看来我们考虑一个数据库操作，并且感觉到稍微有点麻烦的时候，就要尝试跳出“大而全SQL”这个怪圈，尝试考虑用过程/函数，触发器，视图等高级特性来辅助解决！


下面附上函数的介绍：

CREATE [OR REPLACE] FUNCTION function_name
 (arg1 [ { IN | OUT | IN OUT }] type1 [DEFAULT value1],
 [arg2 [ { IN | OUT | IN OUT }] type2 [DEFAULT value1]],
 ......
 [argn [ { IN | OUT | IN OUT }] typen [DEFAULT valuen]])
 [ AUTHID DEFINER | CURRENT_USER ]
RETURN return_type 
 IS | AS
    <类型.变量的声明部分> 
BEGIN
    执行部分
    RETURN expression
EXCEPTION
    异常处理部分
END function_name;


更多细节参考：http://blog.csdn.net/jiyiqinlovexx/article/details/13417455


一条大而全SQL与函数之前的抉择：谁是更为有效的SQL？


联系
我们