在sql Server 2005中,当我编写一个类似的查询时
SELECT m.*,a.price p1,b.price p2 FROM mytable m LEFT JOIN products_table_1 a ON my_hash_function(m.name) = a.hash LEFT JOIN products_table_2 b ON my_hash_function(m.name) = b.hash
是my_hash_function(m.name)计算两次还是只计算一次？如果两次,我怎样才能使用变量来避免这种情况？
解决方法
select mm.*,b.price p2 from
(SELECT m.*,my_hash_function(m.name) as name
FROM mytable m) mm
LEFT JOIN products_table_1 a
ON mm.name = a.hash
LEFT JOIN products_table_2 b
ON mm.name = b.hash
