select a.id,a.type1,b.type_name,a.type2, c.type_name,a.type3,d.type_name from tabA as a Left Join tabB as b on a.type1=b.id Left join tabB as c on a.type2=c.id Left join tabB as d on a.type3=d.id
select a.id,a.type1,type_name1=(select type_name from tab b where a.type1=b.id),a.type2, type_name2=(select type_name from tab b where a.type2=b.id),a.type3, type_name3=(select type_name from tab b where a.type3=b.id) from taba a
select a.id, a.type1, type_name = max(case b.id when a.type1 then b.type_name end), a.type2, type_name = max(case b.id when a.type2 then b.type_name end), a.type3 type_name = max(case b.id when a.type3 then b.type_name end) from 表A a, 表B b group by a.id,a.type1,a.type2,a.type3
我测试了一下select a.[id],a.type1,type1_name = (select [type_name] from @t_2 b where a.type1=b.[id]) ,a.type2,type2_name = (select [type_name] from @t_2 b where a.type2 = b.[id]), a.type3,type3_name = (select [type_name] from @t_2 b where a.type3 = b.[id]) from @t_1 a SQL:StmtCompleted 0 0 60 0 发现在查询分析器里面的显示服务器跟踪里面,它读取了60 条记录。而select a.id,a.type1,b.type_name,a.type2, c.type_name,a.type3,d.type_name from @t_1 as a Left Join @t_2 as b on a.type1=b.id Left join @t_2 as c on a.type2=c.id Left join @t_2 as d on a.type3=d.id SQL:StmtCompleted 0 0 78 0 却读取了78条本想测试一下时间的的,可是客户统计里面每次测试的时间都不一样!无奈。但是应该说,读取的记录越少,那么组合时间应该短一点,效率更高些啊!? 搞不明白,请大家讨论讨论。
c.type_name,a.type3,d.type_name
from tabA as a Left Join tabB as b
on a.type1=b.id
Left join tabB as c on a.type2=c.id
Left join tabB as d on a.type3=d.id
type_name2=(select type_name from tab b where a.type2=b.id),a.type3,
type_name3=(select type_name from tab b where a.type3=b.id)
from taba a
a.id,
a.type1,
type_name = max(case b.id when a.type1 then b.type_name end),
a.type2,
type_name = max(case b.id when a.type2 then b.type_name end),
a.type3
type_name = max(case b.id when a.type3 then b.type_name end)
from
表A a,
表B b
group by
a.id,a.type1,a.type2,a.type3
一种是在select子句中进行嵌套子查询(请参考WANGZWANG)
一种是一张A表与三张B表进行左外连接。(请参考本人wgsasd311)
只有两种方法:
一种是在select子句中进行嵌套子查询(请参考本人wgsasd311)一种是一张A表与三张B表进行左外连接。(请参考WANGZWANG)
,a.type2,type2_name = (select [type_name] from @t_2 b where a.type2 = b.[id]),
a.type3,type3_name = (select [type_name] from @t_2 b where a.type3 = b.[id])
from @t_1 a SQL:StmtCompleted 0 0 60 0
发现在查询分析器里面的显示服务器跟踪里面,它读取了60 条记录。而select a.id,a.type1,b.type_name,a.type2,
c.type_name,a.type3,d.type_name
from @t_1 as a Left Join @t_2 as b
on a.type1=b.id
Left join @t_2 as c on a.type2=c.id
Left join @t_2 as d on a.type3=d.id SQL:StmtCompleted 0 0 78 0
却读取了78条本想测试一下时间的的,可是客户统计里面每次测试的时间都不一样!无奈。但是应该说,读取的记录越少,那么组合时间应该短一点,效率更高些啊!?
搞不明白,请大家讨论讨论。