这个SQL有什么问题吗？

Question

我确信这是我忽略的非常简单的事情，但 MS SQL 对我来说是新的——不过我（或者至少认为我是）对基本的 MySql 相当满意。

SELECT l.link_id, l.link_allcount, d.desc_id, d.desc_count, d.desc_text, h.hour_17,   dl.day_19 
FROM lnktrk_links AS l, lnktrk_hourly AS h, lnktrk_daily AS dl 
LEFT JOIN lnktrk_descriptions AS d ON l.link_id = d.link_id 
WHERE l.link_id = h.link_id AND l.link_id = dl.link_id AND l.link_is_click = 1

我得到的错误是：

'The multi-part identifier "l.link_id" could not be bound.

但是 l.link_id 肯定存在。以下没有连接的查询按预期工作：

SELECT l.link_id, l.link_allcount, d.desc_id, d.desc_count, d.desc_text, h.hour_17, dl.day_19 
FROM lnktrk_links AS l, lnktrk_hourly AS h, lnktrk_daily AS dl, lnktrk_descriptions AS d 
WHERE l.link_id = h.link_id AND l.link_id = dl.link_id AND d.link_id = l.link_id AND l.link_is_click = 1

Answer 1

试试这个：

SELECT 
l.link_id, 
l.link_allcount, 
d.desc_id, 
d.desc_count, 
d.desc_text, 
h.hour_17,   
dl.day_19 

FROM lnktrk_links AS l
inner join lnktrk_hourly AS h
on l.link_id = h.link_id
inner join  lnktrk_daily AS dl
on l.link_id = dl.link_id 
LEFT JOIN lnktrk_descriptions AS d ON l.link_id = d.link_id 
WHERE   l.link_is_click = 1

Answer 2

我认为语法

lnktrk_daily AS dl 
LEFT JOIN lnktrk_descriptions AS d 
    ON l.link_id = d.link_id 

比逗号分隔的子句绑定得更紧密，因此该部分中没有 l

您所拥有的内容分组为：

SELECT l.link_id, l.link_allcount, d.desc_id, d.desc_count, d.desc_text, h.hour_17,   dl.day_19 
FROM lnktrk_links AS l, lnktrk_hourly AS h, 
( lnktrk_daily AS dl 
  LEFT JOIN lnktrk_descriptions AS d 
  ON l.link_id = d.link_id )
WHERE l.link_id = h.link_id AND l.link_id = dl.link_id AND l.link_is_click = 1

Answer 3

该LEFT JOIN正在将lnktrk_daily AS dl与lnktrk_descriptions AS d连接起来 - 这些都不被称为l，所以< em>在JOIN的ON子句的上下文中，l没有任何意义。

看起来目前您正在使用“交叉连接加 WHERE 子句”样式，而不是“带有 ON 子句的内连接”样式。如果您切换到后者，那么 l 应该在整个 FROM 子句中有意义。

Answer 4

你应该加入它们 - 而不是使用 FROM

试试这个

SELECT l.link_id, l.link_allcount, d.desc_id, d.desc_count, d.desc_text, h.hour_17,   dl.day_19 
FROM lnktrk_links AS l
LEFT JOIN lnktrk_hourly AS h ON l.link_id = h.link_id
LEFT JOIN lnktrk_daily AS dl ON l.link_id = dl.link_id
LEFT JOIN lnktrk_descriptions AS d ON l.link_id = d.link_id 
WHERE l.link_is_click = 1