mysql - 查询可以选择，但不能作为 View

Question

我有一个查询，它可以作为选择查询提供正确的结果，但是当我将它用作 View 时，它会创建 View ，但浏览 View 会出现错误“子查询返回超过 1 行”。该查询采用“my_postmeta_table”表上的“meta_key”字段，并将其设为当前行的“meta_value”字段的列名称。它会产生这样的东西:

这是表格上的格式:

post_id  meta_key  meta_value
1        key1      val1
1        key2      val2   
2        key1      val3
2        key2      val4  

选择查询返回的结果如下所示:

post_id  key1  key2
1        val1  val2
2        val3  val4

这是查询:

create or REPLACE view contacts2 as select
    post_id pid, 
    post_date Date, 
    (SELECT  meta_value from my_postmeta_table where meta_key="contact_country" and post_id=pid)  AS "Country", 
    (SELECT  meta_value from my_postmeta_table where meta_key="contact_usa_states" and post_id=pid)  AS "USA State",  
    (SELECT  meta_value from my_postmeta_table where meta_key="contact_subject" and post_id=pid)  AS "Subject",  
    (SELECT meta_value from my_postmeta_table where meta_key="contact_first_name"  and post_id=pid)  AS "First Name",  
    (SELECT meta_value from my_postmeta_table where meta_key="contact_last_name"  and post_id=pid)  AS "Last Name",  
    (SELECT meta_value from my_postmeta_table where meta_key="contact_company"  and post_id=pid)  AS "Company",  
    (SELECT meta_value from my_postmeta_table where meta_key="contact_position"  and post_id=pid)  AS "Position",  
    (SELECT meta_value from my_postmeta_table where meta_key="contact_email"  and post_id=pid)  AS "Email",  
    (SELECT meta_value from my_postmeta_table where meta_key="contact_message"  and post_id=pid)  AS "Message" 
    from my_postmeta_table, my_posts_table where my_posts_table.ID=my_postmeta_table.post_id and my_posts_table.post_type="contact"  GROUP by post_id;

所以......如果我从上面的代码中省略这个“创建或替换 View contacts2为”，它会给出很好的结果。

谢谢

Answer 1

如果您希望所有子查询仅返回 1 条记录，则只需在每个子查询的末尾添加 limit 1 即可。这样mysql就会知道子查询肯定只会返回1条记录。