select * from View_ShopMedicine where exists ( select MedicineId,ShopId,Standard from ShopMedicine sm where sm.MedicineId = View_ShopMedicine.MedicineId and sm.ShopId = View_ShopMedicine.ShopId and sm.Standard=View_ShopMedicine.ShopMedicineStandard group by MedicineId,ShopId,Standard having(COUNT(*)) >1)后面接的and比较多.........只要时间都用在这个上面了 View_ShopMedicine 为视图,你也可以当做是一个表
解决方案 »
- asp.net中如何回车触发指定按钮的事件(实现按回车提交)
- 快急死了 怎么动态取得app_code下的class?
- AspNetPage分页问题
- 分析器错误!!!谁能看一下!
- 关于随机数生成的疑惑
- 为什么退回上一个页面时会出现:"警告: 网页已经过期"
- 搞.NET的兄弟们,大家上来留个MSN交流学习一下,让.NET发扬光大!!!!!
- 急救!如何用JS对Grid中的模板列中的textbox进行取值和赋值?
- 请问一下, 小弟在用VS2005做一个PDA项目,请问该如何对其
- 类似于招商银行的密码框,只有安装了以后才能使用这种,是如何实现的??
- 发布IIS后 COM 组件报错
- 谁能给我一个可以用的,以gridview展现树形式的控件
select * from
View_ShopMedicine
where exists
(
select MedicineId,ShopId,Standard from ShopMedicine sm
where sm.MedicineId = View_ShopMedicine.MedicineId
and sm.ShopId = View_ShopMedicine.ShopId
and sm.Standard=View_ShopMedicine.ShopMedicineStandard
group by MedicineId,ShopId,Standard having(COUNT(*)) >1
)
--不知道你要实现一个什么样的效果
from View_ShopMedicine v
inner join
(
select sm.MedicineId,sm.ShopId,sm.Standard,COUNT(1) as RdCount
from ShopMedicine sm
group by sm.MedicineId,sm.ShopId,sm.Standard
) sm on (sm.MedicineId = v.MedicineId and and sm.ShopId = v.ShopId and and sm.Standard = v.ShopMedicineStandard)
where sm.RdCount > 1
select MedicineId,ShopId,Standard from ShopMedicine
group by MedicineId,ShopId,Standard having(COUNT(*)) >1在查询视图中,MedicineId,ShopId,Standard这三列匹配的数据注:只能添加条件查询,不能两个数据集链接查询
select v.*
from View_ShopMedicine v
inner join
(
select sm.MedicineId,sm.ShopId,sm.Standard,COUNT(1) as RdCount
from ShopMedicine sm
where 1 = 1
...
group by sm.MedicineId,sm.ShopId,sm.Standard
) sm on (sm.MedicineId = v.MedicineId and and sm.ShopId = v.ShopId and and sm.Standard = v.ShopMedicineStandard)
where sm.RdCount > 1...处动态添加你的查询条件,这样不可以吗?
if object_id('[View_ShopMedicine]') is not null drop table [View_ShopMedicine]
create table [View_ShopMedicine] (MedicineId int,ShopId int,ShopMedicineStandard int)
insert into [View_ShopMedicine]
select 1,1,1 union all
select 2,2,2 union all
select 3,3,3 union all
select 1,1,1 union all
select 2,2,2 union all
select 4,4,4if object_id('[ShopMedicine]') is not null drop table [ShopMedicine]
create table [ShopMedicine] (MedicineId int,ShopId int,Standard int)
insert into [ShopMedicine]
select 1,1,1 union all
select 3,3,3 union all
select 1,1,1 union all
select 2,2,2 union all
select 4,4,4
--你原来的写法
select * from
View_ShopMedicine
where exists
(
select MedicineId,ShopId,Standard from ShopMedicine sm
where sm.MedicineId = View_ShopMedicine.MedicineId
and sm.ShopId = View_ShopMedicine.ShopId
and sm.Standard=View_ShopMedicine.ShopMedicineStandard
group by MedicineId,ShopId,Standard having(COUNT(*)) >1
)--你的结果
/*
MedicineId ShopId ShopMedicineStandard
----------- ----------- --------------------
1 1 1
1 1 1
*/
你原来的写法是这个意思?
select * from t1 where x in ( select y from t2 )
事实上可以理解为:
select *
from t1, ( select distinct y from t2 ) t2
where t1.x = t2.y;
——如果你有一定的SQL优化经验,从这句很自然的可以想到t2绝对不能是个大表,因为需要对t2进行全表的“唯一排序”,如果t2很大这个排序的性能是不可忍受的。但是t1可以很大,为什么呢?最通俗的理解就是因为t1.x=t2.y可以走索引。但这并不是一个很好的解释。试想,如果t1.x和t2.y都有索引,我们知道索引是种有序的结构,因此t1和t2之间最佳的方案是走merge join。另外,如果t2.y上有索引,对t2的排序性能也有很大提高。
select * from t1 where exists ( select null from t2 where y = x )
可以理解为:
for x in ( select * from t1 )
loop
if ( exists ( select null from t2 where y = x.x )
then
OUTPUT THE RECORD!
end if
end loop
——这个更容易理解,t1永远是个表扫描!因此t1绝对不能是个大表,而t2可以很大,因为y=x.x可以走t2.y的索引。
综合以上对IN/EXISTS的讨论,我们可以得出一个基本通用的结论:IN适合于外表大而内表小的情况;EXISTS适合于外表小而内表大的情况。
我们要根据实际的情况做相应的优化,不能绝对的说谁的效率高谁的效率低,所有的事都是相对的