具体功能是指带3个参数的sql查询语句,
1,开始时间(fromdate)不为空则应该大于该时间
2,结束时间(todate)不为空则应该小于该时间
3,工作组(workclass)为空选出所有的组
三者都可能为空的。
问题有没有更简单的SQL,应该怎么优化?SELECT *
FROM DR_GROUP_CONFIG
WHERE
(:fromdate IS NOT NULL) AND (:todate IS NOT NULL) AND (:workclass IS NOT NULL) AND (FROM_DATE >= :fromdate) AND (TO_DATE <= :todate) AND (WORK_CLASS = :workclass) OR (:fromdate IS NOT NULL) AND (:todate IS NULL) AND (:workclass IS NOT NULL) AND (FROM_DATE >= :fromdate) AND (WORK_CLASS = :workclass) OR (:fromdate IS NULL) AND (:todate IS NOT NULL) AND (:workclass IS NOT NULL) AND (TO_DATE <= :todate) AND (WORK_CLASS = :workclass) OR (:fromdate IS NULL) AND (:todate IS NULL) AND (:workclass IS NOT NULL) AND (WORK_CLASS = :workclass) OR (:fromdate IS NOT NULL) AND (:todate IS NOT NULL) AND (:workclass IS NULL) AND (FROM_DATE >= :fromdate) AND (TO_DATE <= :todate) OR (:fromdate IS NOT NULL) AND (:todate IS NULL) AND (:workclass IS NULL) AND (FROM_DATE >= :fromdate) OR (:fromdate IS NULL) AND (:todate IS NOT NULL) AND (:workclass IS NULL) AND (TO_DATE <= :todate) OR (:fromdate IS NULL) AND (:todate IS NULL) AND (:workclass IS NULL) AND (1 = 1)ORDER BY FROM_DATEFROM后面的语句,有没有可以优化的?
1,开始时间(fromdate)不为空则应该大于该时间
2,结束时间(todate)不为空则应该小于该时间
3,工作组(workclass)为空选出所有的组
三者都可能为空的。
问题有没有更简单的SQL,应该怎么优化?SELECT *
FROM DR_GROUP_CONFIG
WHERE
(:fromdate IS NOT NULL) AND (:todate IS NOT NULL) AND (:workclass IS NOT NULL) AND (FROM_DATE >= :fromdate) AND (TO_DATE <= :todate) AND (WORK_CLASS = :workclass) OR (:fromdate IS NOT NULL) AND (:todate IS NULL) AND (:workclass IS NOT NULL) AND (FROM_DATE >= :fromdate) AND (WORK_CLASS = :workclass) OR (:fromdate IS NULL) AND (:todate IS NOT NULL) AND (:workclass IS NOT NULL) AND (TO_DATE <= :todate) AND (WORK_CLASS = :workclass) OR (:fromdate IS NULL) AND (:todate IS NULL) AND (:workclass IS NOT NULL) AND (WORK_CLASS = :workclass) OR (:fromdate IS NOT NULL) AND (:todate IS NOT NULL) AND (:workclass IS NULL) AND (FROM_DATE >= :fromdate) AND (TO_DATE <= :todate) OR (:fromdate IS NOT NULL) AND (:todate IS NULL) AND (:workclass IS NULL) AND (FROM_DATE >= :fromdate) OR (:fromdate IS NULL) AND (:todate IS NOT NULL) AND (:workclass IS NULL) AND (TO_DATE <= :todate) OR (:fromdate IS NULL) AND (:todate IS NULL) AND (:workclass IS NULL) AND (1 = 1)ORDER BY FROM_DATEFROM后面的语句,有没有可以优化的?
解决方案 »
- 知道类名,不知道窗体名称,怎么获取窗体句柄?
- 数据库语句影响
- 请教高手呀,Apache & SubVersion 文件可以Update,但是不能commit!!!
- 对于object类型,其实际类型可能是Single、Decimal、Double三种,如何将object转为保留2位小数的字符串。
- 不在同一服务器上 如何 读取exchange server 上的邮件
- C#能不能讀取記事本裡指定的字符串?
- 索引超出范围。必须为非负值并小于集合大小????新手
- 怎样用C#读取INI文件?
- EXCEL打开导入datagrid的问题
- 高分请教!搜索一些数据,然后读入数据库
- 求助:我做了一个登录页面,为什么2003系统验证就正确,其它系统就不正确.晕死
- Repeater分页问题~在线等!
什么意思?麻烦解释一下,最好就本例说明一下,谢谢
FROM DR_GROUP_CONFIG
WHERE 1=1if .....if .....
FROM DR_GROUP_CONFIG
WHERE (:fromdate is null or FROM_DATE>:fromdate) and (:todate is null or TO_DATE <= :todate) and (:workclas is null or WORK_CLASS= :workclas)
这样应该就可以啦
string sql = "SELECT * FROM DR_GROUP_CONFIG WHERE 1=1";
string where = "";if(fromdate != null) where += " AND (FROM_DATE >= fromdate) ";
if(todate != null) where += " AND (TO_DATE <= todate) ";
if(workclass != null) where += " AND (WORK_CLASS = workclass) ";sql += where;
using (SELECT nvl(:fromdate,'19010101') begin_date,nvl(:todate end_date,'20991231'),
:workclass workclass FROM dual) t2
on (t1.FROM_DATE>=t2.begin_date and t2.workclass is null
)
when matched then
select * from DR_GROUP_CONFIG;
WHEN NOT MATCHED THEN
MERGE INTO DR_GROUP_CONFIG t1
using (SELECT nvl(:fromdate,'19010101') begin_date,nvl(:todate end_date,'20991231'),
:workclass workclass FROM dual) t2
on (t1.FROM_DATE>=t2.begin_date and t2.workclass is not null
)
when matched then
select * from DR_GROUP_CONFIG t where t.WORK_CLASS=t2.workclass;