数据库设计及SQL查询 ??? ( 积分: 30 )

表AAA：<br>ID&nbsp;&nbsp;&nbsp;String<br>NAME&nbsp;String<br><br>表BBB：<br>ID&nbsp;&nbsp;string<br>A&nbsp;&nbsp;&nbsp;string<br>B&nbsp;&nbsp;&nbsp;string<br>C&nbsp;&nbsp;&nbsp;string&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;A/B/C字段分别存表A.ID<br>------------------------------------------------------------------<br>SELECT&nbsp;A.ID,&nbsp;B.name&nbsp;as&nbsp;Aname,&nbsp;C.name&nbsp;as&nbsp;Bname,&nbsp;D.name&nbsp;as&nbsp;Cname&nbsp;<br>FROM&nbsp;AAA&nbsp;A&nbsp;LEFT&nbsp;JOIN&nbsp;BBB&nbsp;B&nbsp;ON&nbsp;A.A=B.ID<br>LEFT&nbsp;JOIN&nbsp;BBB&nbsp;C&nbsp;ON&nbsp;A.B=C.ID&nbsp;<br>LEFT&nbsp;JOIN&nbsp;BBB&nbsp;D&nbsp;ON&nbsp;A.C=D.ID<br><br>这样会影响查询速度吗?

select&nbsp;GetDate()<br>SELECT&nbsp;A.ID,&nbsp;Case&nbsp;when&nbsp;b.a&nbsp;is&nbsp;null&nbsp;then&nbsp;null&nbsp;else&nbsp;B.name&nbsp;end&nbsp;as&nbsp;Aname,&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Case&nbsp;when&nbsp;b.b&nbsp;is&nbsp;null&nbsp;then&nbsp;null&nbsp;else&nbsp;B.name&nbsp;end&nbsp;as&nbsp;Bname,&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Case&nbsp;when&nbsp;b.c&nbsp;is&nbsp;null&nbsp;then&nbsp;null&nbsp;else&nbsp;B.name&nbsp;end&nbsp;as&nbsp;Cname&nbsp;<br>FROM&nbsp;AAA&nbsp;A&nbsp;left&nbsp;join&nbsp;bbB&nbsp;B&nbsp;on&nbsp;(A.id=B.A&nbsp;OR&nbsp;A.ID=b.B&nbsp;OR&nbsp;A.ID=b.C)<br>go<br>select&nbsp;GetDate()<br>go<br>select&nbsp;GetDate()<br>SELECT&nbsp;A.ID,&nbsp;B.name&nbsp;as&nbsp;Aname,&nbsp;C.name&nbsp;as&nbsp;Bname,&nbsp;D.name&nbsp;as&nbsp;Cname&nbsp;<br>FROM&nbsp;AAA&nbsp;A&nbsp;LEFT&nbsp;JOIN&nbsp;BBB&nbsp;B&nbsp;ON&nbsp;A.ID=B.A<br>LEFT&nbsp;JOIN&nbsp;BBB&nbsp;C&nbsp;ON&nbsp;A.ID=C.B&nbsp;<br>LEFT&nbsp;JOIN&nbsp;BBB&nbsp;D&nbsp;ON&nbsp;A.ID=D.C<br>go<br>select&nbsp;GetDate()

你作为查询&nbsp;&nbsp;可以用视图

levi:&nbsp;这样可以提高速度效力&nbsp;<br>admin2008&nbsp;可以说下去...

呵呵，我照你的模样建了两个表，然后添了10来条记录，基本上两个语句执行的速度差不多，大约17MS，所以发个给你，看在几万条记录的情况下，效率会差多少？理论上，应该有所提高。判断的速度肯定要比几个表Join要高。<br><br>admin2008&nbsp;的意思，应该是你可以把上述语句保存为一个视图，如<br>CREATE&nbsp;VIEW&nbsp;ABC&nbsp;AS<br>SELECT&nbsp;A.ID,&nbsp;Case&nbsp;when&nbsp;b.a&nbsp;is&nbsp;null&nbsp;then&nbsp;null&nbsp;else&nbsp;B.name&nbsp;end&nbsp;as&nbsp;Aname,&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Case&nbsp;when&nbsp;b.b&nbsp;is&nbsp;null&nbsp;then&nbsp;null&nbsp;else&nbsp;B.name&nbsp;end&nbsp;as&nbsp;Bname,&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Case&nbsp;when&nbsp;b.c&nbsp;is&nbsp;null&nbsp;then&nbsp;null&nbsp;else&nbsp;B.name&nbsp;end&nbsp;as&nbsp;Cname&nbsp;<br>FROM&nbsp;AAA&nbsp;A&nbsp;left&nbsp;join&nbsp;bbB&nbsp;B&nbsp;on&nbsp;(A.id=B.A&nbsp;OR&nbsp;A.ID=b.B&nbsp;OR&nbsp;A.ID=b.C)<br>GO<br>SELECT&nbsp;*&nbsp;From&nbsp;ABC<br>这样即使语句内容有些许改变，直接在SQL&nbsp;server上更改view即可，无需对程序作调整。另一方面，还确保安全。但这些，对提高效率并没多大帮助。

我的peizmbj@163.com<br>可否留下你们的EMAIL