doutang3760
2013-09-11 14:53
浏览 211
已采纳

MySQL选择复杂的子查询作为字段

I currently have:

SELECT tbl_review.*, users.first_name, users.last_name, (
    SELECT order_ns.tran_date 
    FROM order_ns 
    LEFT JOIN product_2_order_ns.external_order_id = order_ns.order_id 
    WHERE product_2_order_ns.bkfno IN :id
    ORDER BY order_ns.trandate ASC 
    LIMIT 1
) as purchase_date
FROM tbl_review
LEFT JOIN users ON users.sequal_user_id = tbl_review.user_id
WHERE tbl_review.product_id IN :id AND tbl_review.approved = 1

Which, in its sub query, selects an order the user has which has a product in question (defined in :id) get the the oldest transaction date on file for one of the found orders.

I would really like to keep this to one call of the database (don't really want to call again for each returned user for just one field, or even do a range query of all users) but obviously this particular query isn't working.

What can I do, if anything, to get this working?

I cannot make the sub query into a join since they are two distinct pieces of data, the sub query needs to return detail for each row in the main query.

图片转代码服务由CSDN问答提供 功能建议

我目前有:

  SELECT tbl_review。*,users  .first_name,users.last_name,(
 SELECT order_ns.tran_date 
 FROM order_ns 
 LEFT JOIN product_2_order_ns.external_order_id = order_ns.order_id 
 WHERE product_2_order_ns.bkfno IN:id 
 ORDER BY order_ns.trandate ASC 
 LIMIT 1  
)as purchase_date 
FROM tbl_review 
LEFT JOIN users ON users.sequal_user_id = tbl_review.user_id 
WHERE tbl_review.product_id IN:id AND tbl_review.approved = 1 
   
 
 

在其子查询中,选择一个用户拥有相关产品的订单(在:id 中定义)获取其中一个找到订单的最早交易日期。

我真的想把它保留在数据库的一次调用中(不是真的想再为每个返回的用户调用一个字段,甚至不要对所有用户进行范围查询) 但显然这个特定的查询不起作用。

如果,我该怎么办? 什么,让这个工作?

我不能将子查询变成连接,因为它们是两个不同的数据,子查询需要返回主查询中每一行的详细信息 。

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

2条回答 默认 最新

  • doucuyu2259 2013-09-12 09:12
    已采纳

    @Gordons answer is really close but I wanted it to return even if no data was found for tran_date so I changed my query to:

    SELECT tbl_review.*, users.first_name, users.last_name, order_ns.tran_date
    FROM tbl_review
    LEFT JOIN users ON users.sequal_user_id = tbl_review.user_id 
    LEFT JOIN order_ns ON order_ns.order_id = (
        SELECT order_ns.order_id 
        FROM order_ns
        LEFT JOIN product_2_order_ns on product_2_order_ns.external_order_id = order_ns.order_id
                WHERE product_2_order_ns.bkfno IN :id
        ORDER BY order_ns.tran_date ASC
        LIMIT 1
     ) 
     WHERE tbl_review.product_id IN :id AND tbl_review.approved = 1;    
    

    This will return the distinct data of tran_date irrespective of whether it is found or not.

    点赞 评论
  • doujuncuo9339 2013-09-11 15:08

    I think you just want a correlated subquery. It is unclear exactly what the relationship is between the inner query and the outer one. My guess is that it is on users and orders:

    SELECT tbl_review.*, users.first_name, users.last_name,
           (SELECT order_ns.tran_date 
            FROM order_ns LEFT JOIN
                 product_2_order_ns
                 on product_2_order_ns.external_order_id = order_ns.order_id and
                    product_2_order_ns.bkfno = tbl_review.product_id and
            WHERE order_ns.user_id = tbl_review.user_id
            ORDER BY order_ns.trandate ASC 
            LIMIT 1
           ) as purchase_date
    FROM tbl_review LEFT JOIN
         users
         ON users.sequal_user_id = tbl_review.user_id
    WHERE tbl_review.product_id IN :id AND tbl_review.approved = 1;
    

    EDIT:

    Oh, the inner query has no relationship to the outer query. Then it is easier. Move it to the from clause using cross join:

    SELECT tbl_review.*, users.first_name, users.last_name,
           innerquery.tran_date as purchase_date
    FROM tbl_review LEFT JOIN
         users
         ON users.sequal_user_id = tbl_review.user_id cross join
         (SELECT order_ns.tran_date 
          FROM order_ns LEFT JOIN
               product_2_order_ns
               on product_2_order_ns.external_order_id = order_ns.order_id 
          WHERE product_2_order_ns.bkfno IN :id
          ORDER BY order_ns.trandate ASC 
          LIMIT 1
         ) innerquery
    WHERE tbl_review.product_id IN :id AND tbl_review.approved = 1;
    
    点赞 评论

相关推荐 更多相似问题