SQL语句过滤结果

发布于 2024-09-27 13:11:13 字数 630 浏览 2 评论 0原文

我需要根据3个条件过滤SQL查询结果：
1. 地点
2. 医生姓名
3. 专业名称

下面是 3 个条件都不为空时的 sql 查询：

if (location != "" && doctor!="" && specialty!="")

select Location, ...
from clinicdoctors
where Location = @Location and DoctorName = @DoctorName and SpecialtyName = @SpecialtyName

}

如果只有 location 为空，

if (location == "" && doctor!="" && specialty!="")
select Location, ...
from clinicdoctors
where Location is not null and DoctorName = @DoctorName and SpecialtyName = @SpecialtyName

...

如果我想检查所有条件，我需要编写 8 个 if 语句。在这种情况下我应该做什么来简化代码？

原文

I need to filter the SQL query result according to 3 conditions:
1. Location
2. Doctor Name
3. Specialty Name

Below is the sql query if all 3 conditions are not empty:

if (location != "" && doctor!="" && specialty!="")

select Location, ...
from clinicdoctors
where Location = @Location and DoctorName = @DoctorName and SpecialtyName = @SpecialtyName

}

if only location is empty,

if (location == "" && doctor!="" && specialty!="")
select Location, ...
from clinicdoctors
where Location is not null and DoctorName = @DoctorName and SpecialtyName = @SpecialtyName

...

If I wanna check all the conditions, I need to write eight if statements.
What should I do to simplify the code in this situation?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

灯角 2024-10-04 13:11:13

select Location, ...
from clinicdoctors
where 
 ISNULL(@Location,Location) = Location
 and ISNULL(@DoctorName,DoctorName) = DoctorName
 and ISNULL(@SpecialtyName,SpecialtyName) = SpecialtyName

select Location, ...
from clinicdoctors
where 
 ISNULL(@Location,Location) = Location
 and ISNULL(@DoctorName,DoctorName) = DoctorName
 and ISNULL(@SpecialtyName,SpecialtyName) = SpecialtyName

回复收藏 0 原文

献世佛 2024-10-04 13:11:13

我认为在代码中执行此操作比像您所做的那样在 sql 中执行此操作更好。由于查询如此不同，实际上没有任何解决办法，但一种简洁的方法是：

$loc_where = empty($loc) ? 'Location IS NOT NULL' : "Location = $loc";
$doc_where = empty($doc) ? 'AND DoctorName IS NOT NULL' : "AND DoctorName = $doc";
$spec_where = empty($spec) ? 'AND SpecialtyName IS NOT NULL' : "AND SpecialtyName = $spec";

query ... WHERE $loc_where $doc_where $spec_where

I think it would be better to do this in the code than sql as you have done. Not really any way around it since the queries are so different, but a terse way to do it would be:

$loc_where = empty($loc) ? 'Location IS NOT NULL' : "Location = $loc";
$doc_where = empty($doc) ? 'AND DoctorName IS NOT NULL' : "AND DoctorName = $doc";
$spec_where = empty($spec) ? 'AND SpecialtyName IS NOT NULL' : "AND SpecialtyName = $spec";

query ... WHERE $loc_where $doc_where $spec_where

回复收藏 0 原文

~没有更多了~