怎样Sql语句中避免使用IN或者NOT IN?

2004-08-12 03:47:59 822 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

EV1527、PT2262傻傻分不清？一文讲透常见315/433MHz遥控编码协议区别与选型本文深度对比了EV1527、PT2262和SC5262三种常见的315/433MHz遥控编码协议，详细解析了它们的技术特点、成本效益和适用场景。EV1527作为低成本方案的首选，特别适合智能家居和简易安防设备；PT2262在稳定性和兼容性上表现优异；而SC5262则在安全性方面具有显著优势。文章还提供了实用的选型框架和工程建议，帮助开发者在不同应用场景中做出最优决策。阅读详情

我在SQL Best Practices中看到要尽量避免使用In或者Not in, 因为这样index不起作用,可是在实际写程序时碰到条件筛选语句,如下条语句:
select * from Region where regionid in (Select regionid from Sales_Region where userid=@UserID)
or regionid in (Select regionid from Region_RegionOffice where roid in (Select roid from Sales_RO where userid=@UserID))

这样的sql语句应该怎样改写呢?
可能我写的语句不太好,影响数据库性能,请大家指教.

...全文

相控阵雷达仿真入门：TWS与TAS模式实战解析（附代码示例）本文深入解析相控阵雷达仿真中的TWS（边搜索边跟踪）与TAS（搜索加跟踪）模式，提供实战代码示例和优化策略。通过简化模型设计和对象化建模，帮助开发者快速构建高效仿真系统，适用于军事推演和装备评估。文章涵盖核心逻辑、数据结构设计及性能优化技巧，提升仿真效率与准确性。阅读详情

zlmark

应用实例 27,579 成员 68,540 内容

+ 关注 + 加入社区

切换为时间正序

rouqu 2004-08-12

打赏
举报

select * from Region R where
exists (select regionid from Sales_Region where userid = @userID
and regionid = R.regionid)
or
exists (select rr.regionid from Region_RegionOffice rr,Sales_RO s
where s.userid = @userID and rr.roid = s.roid and rr.regionid = R.regionid)

rouqu 2004-08-12

打赏
举报

试试
select * from Region R where
exists (select regionid from Sales_Region where userid = @userID
and regionid = R.gegionid)
or
exists (select rr.regionid from Region_RegionOffice rr,Sales_RO s
where s.userid = @userID and rr.roid = s.roid)

LoveSQL 2004-08-12

打赏
举报

select a.* from Region a ,Sales_Region b where a.regionid=b.regionid and b.userid=@UserID

union all

select a.* from Region a,Region_RegionOffice b,Sales_RO c where a.regionid=b.regionid and b.roid=c.roid and c.userid=@UserID

AntingZ 2004-08-12

打赏
举报

我才打了两行字，高手插到前面去了

zjcxc 2004-08-12

打赏
举报

--上面错了,是这样:
select a.* from Region a,(
Select regionid from Sales_Region
where userid=@UserID
union
Select regionid from Region_RegionOffice bb
where exists(
Select roid from Sales_RO
where userid=@UserID
and roid=bb.regionid)
)b where a.regionid=b.regionid

zjcxc 2004-08-12

打赏
举报

--或者:
select a.* from Region a,(
Select regionid from Sales_Region
where userid=@UserID
union
Select regionid from Region_RegionOffice bb
where regionid=a.regionid
and exists(
Select roid from Sales_RO
where userid=@UserID
and roid=bb.regionid)
)b where a.regionid=b.regionid

AntingZ 2004-08-12

打赏
举报

这样改的话，感觉性能不会提高。
还是听高手来讲。wait

zjcxc 2004-08-12

打赏
举报

declare @UserID int
select * from Region a
where exists(
Select regionid from Sales_Region
where userid=@UserID
and regionid=a.regionid)
or exists(
Select regionid from Region_RegionOffice bb
where regionid=a.regionid
and exists(
Select roid from Sales_RO
where userid=@UserID
and roid=bb.regionid))

yesterday2000 2004-08-12

打赏
举报

select a.* from Region a,Sales_Region b regionid c,Region_RegionOffice d where (a.regionid=b.regionid and b.userid=@UserID)
or (a.regionid=c.regionid and c.roid=roid and d.userid=@userid)

AntingZ 2004-08-12

打赏
举报

select * from Region where regionid in (Select regionid from Sales_Region where userid=@UserID)

可以换成：

select * from Region a where exists (Select 1 from Sales_Region b where userid=@UserID and b.regionid = a.regionid )

怎样Sql语句中避免使用IN或者NOT IN?

相关推荐