subject_25288.htm

一些关于vc的问答

<p>
序号：25288 发表者：rafael 发表日期：2002-12-20 12:00:04
<br>主题：非常奇怪的select 问题！！ 有数据库经验的朋友来帮看下呀！
<br>内容：SELECT tabl1.name, table1.age, table1.micro FROM tabl2 RIGHT OUTER JOIN table1 ON table1.name=table2.name WHERE tale1.name &lt;&gt; table2.name AND table1.age &lt;&gt; table2.age AND table1.micro &lt;&gt; table2.micro<BR><BR>我要的是在table1中有，且name,age,micro在table2 中都没有相同的记录，上面的这个select 语句很奇怪，得到的结果不全，只能得到部分结果.<BR>我的意思就是假如<BR>表1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;表2<BR>name&nbsp;&nbsp; age&nbsp;&nbsp;micro&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; name&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;age&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; micro<BR>aaa&nbsp;&nbsp;&nbsp;&nbsp;30&nbsp;&nbsp; test&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;aaa&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 30&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; test<BR>bbb&nbsp;&nbsp;&nbsp;&nbsp;20&nbsp;&nbsp; good&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;bbb&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 20&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; good<BR>ccc&nbsp;&nbsp;&nbsp;&nbsp;22&nbsp;&nbsp; okok&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ccc&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 21&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; okok<BR>ddd&nbsp;&nbsp;&nbsp;&nbsp;20&nbsp;&nbsp; nice&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;eee&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 35&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; OOOOO<BR><BR>那我要的结果就是选出<BR>ccc&nbsp;&nbsp;22 okok<BR>ddd&nbsp;&nbsp;20 nice <BR>这两条记录来<BR><BR>
<br><a href="javascript:history.go(-1)">返回上页</a><br><a href=http://www.copathway.com/cndevforum/>访问论坛</a></p>
<hr size=1>
<blockquote><p>
回复者：凯凯 回复日期：2002-12-20 12:04:31
<br>内容：SELECT tabl1.name, table1.age, table1.micro FROM tabl2 RIGHT OUTER JOIN table1 ON table1.name=table2.name WHERE table1.age &lt;&gt; table2.age AND table1.micro &lt;&gt; table2.micro
<br>
<a href="javascript:history.go(-1)">返回上页</a><br><a href=http://www.copathway.com/cndevforum/>访问论坛</a></p></blockquote>
<hr size=1>
<blockquote><p>
回复者：rafael 回复日期：2002-12-20 13:11:36
<br>内容：你的方法不行呀，结果漏掉了一些符合条件的呀
<br>
<a href="javascript:history.go(-1)">返回上页</a><br><a href=http://www.copathway.com/cndevforum/>访问论坛</a></p></blockquote>
<hr size=1>
<blockquote><p>
回复者：凯凯 回复日期：2002-12-20 13:34:03
<br>内容：漏掉是因为用了RIGHT OUTER JOIN table1 ON table1.name=table2.name ，所以象ddd就肯定会漏掉。<BR>要不你分两次，第二次按name找出tabl1中有但tabl2中没有的记录。
<br>
<a href="javascript:history.go(-1)">返回上页</a><br><a href=http://www.copathway.com/cndevforum/>访问论坛</a></p></blockquote>
<hr size=1>
<blockquote><p>
回复者：凯凯 回复日期：2002-12-20 13:34:58
<br>内容：你可能做一个存储过程，分两次找，将找到的记录全部放到一个临时表中，一次返回一个结果集。
<br>
<a href="javascript:history.go(-1)">返回上页</a><br><a href=http://www.copathway.com/cndevforum/>访问论坛</a></p></blockquote>
<hr size=1>
<blockquote><p>
回复者：rafael 回复日期：2002-12-20 14:16:06
<br>内容：哦，我试试
<br>
<a href="javascript:history.go(-1)">返回上页</a><br><a href=http://www.copathway.com/cndevforum/>访问论坛</a></p></blockquote>
<hr size=1>
<blockquote><p>
回复者：coni 回复日期：2002-12-20 18:53:44
<br>内容：SELECT t21.name, t21.age, t21.micro<BR>FROM t21<BR>WHERE t21.name not in(select t22.name from t22 where t21.age = t22.age AND t21.micro = t22.micro) and<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;t21.age not in(select t22.name from t22 where t21.name = t22.name AND t21.micro = t22.micro)&nbsp;&nbsp;and<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;t21.name not in(select t22.name from t22 where t21.age = t22.age AND t21.name = t22.name)
<br>
<a href="javascript:history.go(-1)">返回上页</a><br><a href=http://www.copathway.com/cndevforum/>访问论坛</a></p></blockquote>
<hr size=1>
<blockquote><p>
回复者：coni 回复日期：2002-12-20 19:07:48
<br>内容：SELECT t21.name, t21.age, t21.micro<BR>FROM t21<BR>WHERE t21.name not in(select t22.name from t22 where t21.age = t22.age AND t21.micro = t22.micro) and<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;t21.age not in(select t22.age from t22 where t21.name = t22.name AND t21.micro = t22.micro)&nbsp;&nbsp;and<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;t21.micro not in(select t22.micro from t22 where t21.age = t22.age AND t21.name = t22.name) <BR><BR>I 刚才打错了！这样运行的速度可能比较慢！如果数据不多效果应该还不错！<BR>
<br>
<a href="javascript:history.go(-1)">返回上页</a><br><a href=http://www.copathway.com/cndevforum/>访问论坛</a></p></blockquote>
<hr size=1>
<blockquote><p>
回复者：rafael 回复日期：2002-12-20 21:47:01
<br>内容：数据很多呀，没个表10万条记录，慢了点
<br>
<a href="javascript:history.go(-1)">返回上页</a><br><a href=http://www.copathway.com/cndevforum/>访问论坛</a></p></blockquote>
<hr size=1>
<blockquote><p>
回复者：coni 回复日期：2002-12-21 10:14:26
<br>内容：SELECT t21.name, t21.age, t21.micro<BR>FROM t21 left join t22 on t21.name=t22.name<BR>where t21.age&lt;&gt;t22.age or t22.age is null or t21.micro&lt;&gt;t22.micro or t22.micro is null<BR><BR>I 发现问题是有NULL值产生的。
<br>
<a href="javascript:history.go(-1)">返回上页</a><br><a href=http://www.copathway.com/cndevforum/>访问论坛</a></p></blockquote>
<hr size=1>
<blockquote><p>
<font color=red>答案被接受</font><br>回复者：骆驼之沙漠王子 回复日期：2002-12-21 17:20:54
<br>内容：试试这个：<BR>SELECT table1.name, table1.age, table1.micro<BR>FROM table1<BR>WHERE NOT EXISTS<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;(SELECT table2.name, table2.age, table2.micro<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FROM table2<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; WHERE table1.name = table2.name AND table1.age = table2.age AND <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; table1.micro = table2.micro)
<br>
<a href="javascript:history.go(-1)">返回上页</a><br><a href=http://www.copathway.com/cndevforum/>访问论坛</a></p></blockquote>