分享
三行代码  ›  专栏  ›  技术社区  ›  letsdothis

Python SQL-两个左连接

  •  0
  • letsdothis  · 技术社区  · 3 天前

    我有一些关于SQL for Python的问题,希望您能帮助我-我正在尝试从wordpress/woomerce检索一些数据。

    我的代码:

        cursor.execute("
        SELECT t1.ID, t1.post_date, t2.meta_value AS first_name, t3.meta_value AS last_name
        FROM test_posts t1 
        LEFT JOIN test_postmeta t2 
        ON t1.ID = t2.post_id 
        WHERE t2.meta_key = '_billing_first_name' and t2.post_id = t1.ID 
        LEFT JOIN test_postmeta t3 
        ON t1.ID = t3.post_id 
        WHERE t3.meta_key = '_billing_last_name' and t3.post_id = t1.ID 
        GROUP BY t1.ID 
        ORDER BY t1.post_date DESC LIMIT 20")
    

    我得到以下错误:

        mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LEFT JOIN test_postmeta t3 ON t1.ID = t3.post_id WHERE t3.meta_key = '_billing' at line 1
    

    我做错什么了?

    提前谢谢。

    2 回复  |  直到 3 天前
        1
  •  3
  •   forpas    3 天前

    在GROUPBY之前应该只有一个WHERE子句。
    但是由于您使用左连接,因此在 正确的 桌子状 t2.meta_key = '_billing_first_name' 因为拒绝不匹配的行,所以会得到一个内部联接。
    因此,请在ON子句中设置所有条件:

    cursor.execute("
    SELECT t1.ID, t1.post_date, t2.meta_value AS first_name, t3.meta_value AS last_name
    FROM test_posts t1 
    LEFT JOIN test_postmeta t2 
    ON t1.ID = t2.post_id AND t2.meta_key = '_billing_first_name'
    LEFT JOIN test_postmeta t3 
    ON t1.ID = t3.post_id AND t3.meta_key = '_billing_last_name'
    GROUP BY t1.ID 
    ORDER BY t1.post_date DESC LIMIT 20")
    

    尽管这个查询在语法上可能对MySql是正确的,但是使用GROUP BY是没有意义的,因为您不做任何聚合。

        2
  •  2
  •   Jerry M.    3 天前

    你的 SQL 语法不正确。试试这个:

      cursor.execute("
        SELECT t1.ID, t1.post_date, t2.meta_value AS first_name, t3.meta_value AS last_name
        FROM test_posts t1 
        LEFT JOIN test_postmeta t2 ON t1.ID = t2.post_id 
        LEFT JOIN test_postmeta t3  ON t1.ID = t3.post_id 
        WHERE t3.meta_key = '_billing_last_name' and t2.meta_key = '_billing_first_name'
        GROUP BY t1.ID 
        ORDER BY t1.post_date DESC LIMIT 20")
    

    也许值得一读 SQL语言 Joins WHERE 声明。