一个有点难度的SQL语句

czp98069806 2003-10-15 10:24:33

select a.* from Acentuser a,Aowin b,Abulk c,Aletball d,Astandard e,Awave f where (a.Auser='222' and a.Abw is null and a.Acontest is null and b.Areset is null and b.Auenvelop=false and b.Adenvelop=false) or (a.Auser='222' and a.Abw is null and a.Acontest is null and c.Areset is null and c.Auenvelop=false and c.Adenvelop=false) or (a.Auser='222' and a.Abw is null and a.Acontest is null and d.Areset is null and d.Auenvelop=false and d.Adenvelop=false) or (a.Auser='222' and a.Abw is null and a.Acontest is null and e.Areset is null and e.Auenvelop=false and e.Adenvelop=false) or (a.Auser='222' and a.Abw is null and a.Acontest is null and f.Areset is null and f.Auenvelop=false and f.Adenvelop=false) order by a.Adate desc

其中只有Acentuser 和Aowin 暂时有值，但这两个表里符合条件(a.Auser='222' and a.Abw is null and a.Acontest is null and b.Areset is null and b.Auenvelop=false and b.Adenvelop=false)的值也不显示，而单独用这个sql="select a.* from Acentuser a,Aowin b where a.Auser='"&Auser&"' and a.Abw is null and a.Acontest is null and b.Areset is null and b.Auenvelop=false and b.Adenvelop=false order by a.Adate desc"
又可以找得到值，不知道为什么，还请各位高手帮忙，小弟在此先谢啦！

...全文

97 15 打赏收藏转发到动态举报

写回复

用AI写文章

15 条回复

切换为时间正序

请发表友善的回复…

发表回复

czp98069806 2003-10-20

打赏
举报

嗯，可以啦，谢谢各位

czp98069806 2003-10-20

打赏
举报

我试试

cf9806 2003-10-20

打赏
举报

Acentuser你在这个表中加上这个（Areset）字段不就得啦

yonghengdizhen 2003-10-15

打赏
举报

写成外连接语法
1.对于oracle 8i以下用(+) 语法,对于or的连接条件通过decode转换到派生表中,通过派生表条件连接
2.对于sql server或oracle 8i以上使用left join语法
将非连接条件写到WHERE子句中,连接条件写到 ON子句中

to carrotbill(萝卜)
使用连接至少比在asp中反复访问数据要高效得多,且更能充分利用数据库服务器的运算以及优化能力.

to xuzuning(唠叨)
在 sql server中是否建立关系并不影响连接语句的使用,只是建立一个由数据库维护的关系应该是数据库设计人员的好的习惯

不老书生 2003-10-15

打赏
举报

是啊，你没有a.XX=b.XX之类！

blues-star 2003-10-15

打赏
举报

这个不是有难度，这个简直就是变态啊。

萝卜波 2003-10-15

打赏
举报

一次查询连接6个表也夸张了点

连接查询的运算量是* * * * * *

如果每个表10条纪录运算都 10的6次方了

tigerwen01 2003-10-15

打赏
举报

select a.* from [Acentuser a],[Aowin b],[Abulk c],[Aletball d],[Astandard e],[Awave f] where (a.Auser='222' and a.Abw is null and a.Acontest is null and b.Areset is null and b.Auenvelop=false and b.Adenvelop=false) or (a.Auser='222' and a.Abw is null and a.Acontest is null and c.Areset is null and c.Auenvelop=false and c.Adenvelop=false) or (a.Auser='222' and a.Abw is null and a.Acontest is null and d.Areset is null and d.Auenvelop=false and d.Adenvelop=false) or (a.Auser='222' and a.Abw is null and a.Acontest is null and e.Areset is null and e.Auenvelop=false and e.Adenvelop=false) or (a.Auser='222' and a.Abw is null and a.Acontest is null and f.Areset is null and f.Auenvelop=false and f.Adenvelop=false) order by a.Adate desc