有一个表,似乎在结构设计上不合理,但现在没有办法了,只能适当优化一下语句。表t:autoid adid cityid typeid columnid
1 2 4 null null
2 2 null 3 null
3 2 null null 24
4 3 null 7 null
5 8 9 null null其中autoid为自增列,adid为广告id.
cityid、typeid和columnid在同一行中只会有一个有值,其它两个为null,现在我要查询出 cityid为4,typeid为3,columnid为24的广告id.
要求查询效率尽量高一点,我用语句
select adid from t where cityid=4 and adid in(select adid form t where typeid=3) and adid in(select adid from t where columnid=24)
感觉好像绕了很多弯路,不知道还有没有更好点的语句?
谢谢!
1 2 4 null null
2 2 null 3 null
3 2 null null 24
4 3 null 7 null
5 8 9 null null其中autoid为自增列,adid为广告id.
cityid、typeid和columnid在同一行中只会有一个有值,其它两个为null,现在我要查询出 cityid为4,typeid为3,columnid为24的广告id.
要求查询效率尽量高一点,我用语句
select adid from t where cityid=4 and adid in(select adid form t where typeid=3) and adid in(select adid from t where columnid=24)
感觉好像绕了很多弯路,不知道还有没有更好点的语句?
谢谢!
union
select adid from t where typeid=3
union
select adid from t where columnid=24或者
select adid from t where cityid=4 or typeid=3 or columnid=24
insert @t select 1, 2, 4, null, null
insert @t select 2, 2, null, 3, null
insert @t select 3, 2, null, null, 24
insert @t select 4, 3, null, 7 , null
insert @t select 5, 8, 9 , null , null
select adid from @t where cityid=4 or typeid=3 or columnid=24adid
-----------
2
2
2(3 行受影响)
select adid from t where cityid=4 or typeid=3 or columnid=24
要同时满足三个条件的adid,示例中查询出来的应该只有一个2,可能是我没有表达清楚。见谅
declare @t table(autoid int,adid int,cityid int,typeid int,columnid int)
insert @t select 1, 2, 4, null, null
insert @t select 2, 2, null, 3, null
insert @t select 3, 2, null, null, 24
insert @t select 4, 3, null, 7 , null
insert @t select 5, 8, 4 , null , null
insert @t select 6, 8, null , 3 , null
insert @t select 7, 8, null , null , 24 select adid from
(
select adid from @t where cityid=4 union all
select adid from @t where typeid=3 union all
select adid from @t where columnid=24
) t
group by adid
having count(1)=3
/*
adid
2
8
*/