mysql - 为什么在 JOIN 查询中出现 SQL 错误 "unknown column"？

Question

我正在使用 JOIN 查询 3 个表，我需要对同一个表使用它两次。我收到“未知列”错误消息。我尝试了各种使用别名的方法，但我无法解决问题。有什么想法吗？

SELECT DISTINCT jos_easypaypalbuttons_ipn.atendente
     , jos_easypaypalbuttons_ipn.btn_name
     , pagseguro.atendente
     , pagseguro.produto 
  FROM jos_easypaypalbuttons_ipn, pagseguro
 INNER JOIN produtos pp ON jos_easypaypalbuttons_ipn.btn_name = pp.cod_produto
 INNER JOIN produtos ps ON pagseguro.produto = ps.cod_produto
 WHERE jos_easypaypalbuttons_ipn.payer_email = '$email' 
    OR pagseguro.email = '$email' 
   AND pp.modo_consulta = 'imediata' 
    OR ps.modo_consulta = 'imediata'

留言:

Unknown column 'jos_easypaypalbuttons_ipn.btn_name' in 'on clause'

表的结构:

CREATE TABLE produtos 
(
    id int auto_increment primary key, 
    produto varchar(20), 
    modo_consulta varchar(30)
    );

CREATE TABLE jos_easypaypalbuttons_ipn 
(
    id int auto_increment primary key, 
    btn_name varchar(20), 
    atendente varchar(30),
    payer_email varchar(50)
    );

CREATE TABLE pagseguro 
(
    id int auto_increment primary key, 
    produto varchar(20), 
    atendente varchar(30),
    email varchar(50)
    );

示例数据:

    INSERT INTO produtos
    (modo_consulta, produto)
    VALUES
    ('imediata', 'BR0120'),
    ('imediata', 'BR0130');

    INSERT INTO jos_easypaypalbuttons_ipn
    (atendente, btn_name, payer_email)
    VALUES
    ('sibila', 'BR0120', 'me@lettura.com'),
    ('lua', 'BR0130', 'me@lettura.com');

    INSERT INTO pagseguro
    (atendente, produto, email)
    VALUES
    ('patricia', 'BR0120', 'me@lettura.com'),
    ('lua', 'BR0130', 'me@lettura.com');

---

更新。当匹配表 produtos.mod_consulta 为 时，所需的输出是来自 2 个表 pagseguro 和 jos_easypaypalbuttons 的唯一行 atendente 'imediata':

ATENDENTE   BTN_NAME PRODUTO  MOD_CONSULTA
-----------------------------------------
sibila      BR0120            imediata   
lua         BR0130            imediata
patricia             BR0120   imediata

Answer 1

根据您的预期输出，我得出结论，您需要两个表 {jos_easypaypalbuttons_ipn, pagseguro} 的 FULL JOIN(和/或 UNION)。 (并添加一些来自产品的装饰)

SELECT DISTINCT two.atendente
     , two.btn_name
     , two.produto
     , pp.modo_consulta
  FROM (
        SELECT  COALESCE(ipb.atendente, pgs.atendente) AS atendente
        , COALESCE(ipb.btn_name,pgs.produto) AS zzz
        , COALESCE(ipb.payer_email,pgs.email) AS payer_email
        , ipb.btn_name AS btn_name
        , pgs.produto AS produto
        FROM jos_easypaypalbuttons_ipn ipb
        FULL JOIN pagseguro pgs ON pgs.atendente = ipb.atendente
                                 AND pgs.produto = ipb.btn_name
        ) two
 JOIN produtos pp ON pp.produto = two.zzz
 WHERE two.payer_email = 'me@lettura.com'
   AND pp.modo_consulta = 'imediata'
        ;

同样可以通过将 FULL JOIN 子查询放入 CTE 来完成:

WITH two AS (
        SELECT  COALESCE(ipb.atendente, pgs.atendente) AS atendente
        , COALESCE(ipb.btn_name,pgs.produto) AS zzz
        , COALESCE(ipb.payer_email,pgs.email) AS payer_email
        , ipb.btn_name AS btn_name
        , pgs.produto AS produto
        FROM jos_easypaypalbuttons_ipn ipb
        FULL JOIN pagseguro pgs ON pgs.atendente = ipb.atendente
                        AND pgs.produto = ipb.btn_name
        )
SELECT DISTINCT two.atendente
     , two.btn_name
     , two.produto
        , pp.modo_consulta
 FROM two
 JOIN produtos pp ON pp.produto = two.zzz
 WHERE two.payer_email = 'me@lettura.com'
   AND pp.modo_consulta = 'imediata'
        ;

结果:

 atendente | btn_name | produto | modo_consulta 
-----------+----------+---------+---------------
 lua       | BR0130   | BR0130  | imediata
 sibila    | BR0120   |         | imediata
 patricia  |          | BR0120  | imediata
(3 rows)

更新 XXX:避免使用 UNION 进行 FULL JOIN。 (YMMV。叹息)

SELECT DISTINCT two.atendente
     , two.btn_name
     , two.produto
        , pp.modo_consulta
 FROM   (
         SELECT  COALESCE(ipb.atendente, pgs.atendente) AS atendente
        , COALESCE(ipb.btn_name,pgs.produto) AS zzz
        , COALESCE(ipb.payer_email,pgs.email) AS payer_email
        , ipb.btn_name AS btn_name
        , pgs.produto AS produto
        FROM (
                SELECT aa.atendente AS atendente
                        ,aa.produto AS zzz
                FROM pagseguro aa
                UNION
                SELECT bb.atendente AS atendente
                        ,bb.btn_name AS zzz
                FROM jos_easypaypalbuttons_ipn bb
                ) uu
        LEFT JOIN pagseguro pgs ON pgs.atendente = uu.atendente
                        AND pgs.produto = uu.zzz
        LEFT JOIN jos_easypaypalbuttons_ipn ipb ON ipb.atendente = uu.atendente
                        AND ipb.btn_name = uu.zzz
        ) two
 JOIN produtos pp ON pp.produto = two.zzz
 WHERE two.payer_email = 'me@lettura.com'
   AND pp.modo_consulta = 'imediata'
        ;

后果:请注意 {jos_easypaypalbuttons_ipn, pagseguro} 本质上是同构的。通过组合这两个表，可以避免很多复杂性，也许添加一个指示符/类型标签字段。 Adagium:需要 UNION(通常)表明存在数据建模问题