Sqlite 不明确的列名，包括表名

Question

我有以下sqlite的SELECT语句，它将返回Error: ambiguous column name: msgs.msgcontent1，但列名并不含糊！有人能帮我弄清楚我在这里缺了什么吗？

SELECT 
  phone_lookup.normalized_number AS number , 
  contacts.display_name AS name , 
  msgs.msgcontent1 
FROM 
  contacts2.contacts , 
  temp.msgs 
  INNER JOIN contacts2.phone_lookup ON ( contacts.name_raw_contact_id = phone_lookup.raw_contact_id ) 
  INNER JOIN temp.msgs ON ( contacts.phone_lookup.normalized_number = msgs.sender ) ;

下面是我用来创建包含麻烦列的表的is语句：

CREATE TEMP TABLE msgs 
AS SELECT 
  PART."_ID" , 
  ADDR."_ID" , 
  DATETIME( SUBSTR( COALESCE( PDU."DATE" , SMS."DATE" ) , 1 , 10 ) , 'localtime' , 'unixepoch' ) AS dt , 
  COALESCE( PDU.READ , SMS.READ ) AS read , 
  COALESCE( SMS.ADDRESS , ADDR.ADDRESS ) AS sender , 
  COALESCE( SMS.BODY , COALESCE( PART.TEXT , PART."_DATA" ) ) AS msgcontent1 
FROM 
  mmssms.PDU 
  LEFT OUTER JOIN mmssms.SMS ON ( PDU."DATE" = SMS."DATE" ) 
  LEFT OUTER JOIN mmssms.PART ON ( PART.MID = PDU."_ID" ) 
  LEFT OUTER JOIN mmssms.ADDR ON ( ADDR.MSG_ID = PDU."_ID" ) 
WHERE 
  COALESCE( SMS.BODY , COALESCE( PART.TEXT , PART."_DATA" ) ) IS NOT NULL AND ADDR."_ID" IN 
    ( 
      SELECT 
        MIN( ADDR."_ID" ) 
      FROM 
        ADDR 
      GROUP BY 
        ADDR.MSG_ID 
      ORDER BY 
        ADDR."_ID" 
    ) AND PART."_ID" IN 
    ( 
      SELECT 
        MAX( PART."_ID" ) 
      FROM 
        mmssms.PART 
      GROUP BY 
        PART.MID 
      ORDER BY 
        PART."_ID" 
    ) 
UNION ALL 
SELECT 
  PART."_ID" , 
  ADDR."_ID" , 
  DATETIME( SUBSTR( COALESCE( PDU."DATE" , SMS."DATE" ) , 1 , 10 ) , 'localtime' , 'unixepoch' ) AS dt , 
  COALESCE( PDU."READ" , SMS."READ" ) AS read , 
  COALESCE( SMS."ADDRESS" , ADDR."ADDRESS" ) AS sender , 
  COALESCE( SMS."BODY" , COALESCE( PART."TEXT" , PART."_DATA" ) ) AS msgcontent2 
FROM 
  mmssms.SMS 
  LEFT OUTER JOIN mmssms.PDU ON ( PDU."DATE" = SMS."DATE" ) 
  LEFT OUTER JOIN mmssms.PART ON ( PART."MID" = PDU."_ID" ) 
  LEFT OUTER JOIN mmssms.ADDR ON ( ADDR."MSG_ID" = PDU."_ID" ) 
WHERE 
  COALESCE( SMS."BODY" , COALESCE( PART."TEXT" , PART."_DATA" ) ) IS NOT NULL 
ORDER BY 
  DATETIME( SUBSTR( COALESCE( PDU."DATE" , SMS."DATE" ) , 1 , 10 ) , 'localtime' , 'unixepoch' ) DESC , 
  ADDR."_ID" ASC ;

最后，尽管我认为这与问题无关，但我以以下pragma开始sqlite命令，并附加语句以确保我可以访问所有内容：

PRAGMA temp_store = MEMORY ;
ATTACH '/data/data/com.android.providers.contacts/databases/contacts2.db' AS contacts2 ;
ATTACH '/data/data/com.android.providers.telephony/databases/mmssms.db' AS mmssms ;

Answer 1

看起来FROM之后的temp.msgs不是必需的，因为它已经在INNER JOIN子句中了。所以这个查询必须有效：

SELECT 
  phone_lookup.normalized_number AS number , 
  contacts.display_name AS name , 
  msgs.msgcontent1 
FROM 
  contacts2.contacts
  INNER JOIN contacts2.phone_lookup ON ( contacts.name_raw_contact_id = phone_lookup.raw_contact_id ) 
  INNER JOIN temp.msgs ON ( contacts.phone_lookup.normalized_number = msgs.sender ) ;

如果需要在两个位置指定同一个表（FROM和JOIN子句），则必须为每个表使用不同的别名，并使用这些别名引用列。

更多信息：SQLite查询语言：SELECT