SELECT node_id, node_addr,node_type, ISNULL
((SELECT SUM(POWER(2, meter_channel)) AS Expr1
FROM dbo.meter_table
WHERE (meter_node_id = dbo.node_table.node_id)
GROUP BY meter_node_id), 0) AS node_devs
FROM dbo.node_table用连接查询怎么优化,另外这里的Power对效率有影响吗?
((SELECT SUM(POWER(2, meter_channel)) AS Expr1
FROM dbo.meter_table
WHERE (meter_node_id = dbo.node_table.node_id)
GROUP BY meter_node_id), 0) AS node_devs
FROM dbo.node_table用连接查询怎么优化,另外这里的Power对效率有影响吗?
from dbo.node_table a inner join dbo.meter_table b
on a.node_id=b.meter_node_id
group by a.node_id,a.node_addr,a.node_type直接写,GROUP BY meter_node_id和a.node_id是一样不吗?
FROM dbo.node_table
left join dbo.meter_table on node_table.node_id=meter_table.meter_node_id
FROM dbo.node_table
left join dbo.meter_table on node_table.node_id=meter_table.meter_node_id
group by node_id,node_addr,node_type
dbo.node_table.node_id,
dbo.node_table.node_addr,
dbo.node_table.node_type,
ISNULL(dbo.meter_table.meter_channel, 0) AS node_devs
FROM
dbo.node_table
left join
dbo.meter_table
on
dbo.node_table.node_id = dbo.meter_table.meter_node_id
node_id
,node_addr
,node_type
,node_devs=isnull(Expr1,0)
from dbo.node_table a
left join (
SELECT
meter_node_id
,Expr1=SUM(POWER(2, meter_channel))
FROM dbo.meter_table
GROUP BY meter_node_id
) b on node_id=meter_node_id
子查询中的group by 无任何用处,可以去掉提升性能,2表连接的字段应该都有索引吧