Mysql每个派生表都必须有自己的别名错误。

Question

我想从表sabha_cluster中选择不同的记录。我使用了以下查询，但是"Mysql每个派生表都必须有自己的别名错误“。

mysql> select mps.scode,mps.tlit,mps.amnt,mps.pr from milk_payment_summary mps
join society_master s on mps.scode=s.scode inner join (select distinct c.scode
rom sabha_cluster c) where mps.date1 between '2014-01-16' and '2014-02-15' && c.scode=44;

Answer 1

向派生表添加别名，如下所示：

select mps.scode,mps.tlit, mps.amnt,mps.pr 
from milk_payment_summary mps
join society_master s 
on mps.scode=s.scode 
join (
  select distinct c.scode from sabha_cluster c
) AS t
on t.scode = s.scode
where mps.date1 between '2014-01-16' and '2014-02-15' and t.scode=44

这里，t是派生表的别名。

Answer 2

您需要子查询的别名：

mysql> select mps.scode,mps.tlit,mps.amnt,mps.pr from milk_payment_summary mps
join society_master s on mps.scode=s.scode inner join (select distinct c.scode
from sabha_cluster c) x where mps.date1 between '2014-01-16' and '2014-02-15' && x.scode=44;

注意，您必须在WHERE子句中使用相同的别名，而不是c。

Answer 3

尝尝这个

select 
    mps.scode, mps.tlit, mps.amnt, mps.pr
from
    milk_payment_summary mps
        join
    society_master s ON mps.scode = s.scode
        inner join
    (select distinct
        c.scode
    from
        sabha_cluster c) d
where
    mps.date1 between '2014-01-16' and '2014-02-15'
        AND d.scode = 44;