MySQL 使用 Case 语句连接 [英] MySQL Joins With Case Statements

问题描述

我在查询时遇到了一些麻烦.如果遇到案例，我想加入表格.这是我正在使用的查询.我对这些案例陈述有点陌生，因此非常感谢您的帮助！

I am having a bit of trouble with a query. I want to join tables if a case is met. This the query I'm working with. I'm kind of new to these case statements so any help is greatly appreciated!

      SELECT 
        conversation.c_id,
        conversation.user_one,
        conversation.user_two,
        users.name,
        users.lastName
      FROM `conversation` 

      CASE
        WHEN conversation.user_one = 1
        THEN
          INNER JOIN `users`
          ON conversation.two = users.id

        WHEN conversation.user_two = 1
        THEN
          INNER JOIN `users`
          ON conversation.user_one = users.id
      END CASE

      WHERE `user_one` = 1 OR `user_two` = 1

推荐答案

不要对整个内连接进行 case，只对连接中的 'on' 子句进行 case.这应该有效(除非我有错别字):

Don't case the entire inner join, do the case on only the 'on' clause in the join. This should work (unless I have typos):

  SELECT 
    conversation.c_id,
    conversation.user_one,
    conversation.user_two,
    users.name,
    users.lastName
  FROM `conversation` 
  INNER JOIN `users`
  on
  users.id =
  CASE
    WHEN conversation.user_one = 1
    THEN conversation.two 
    WHEN conversation.user_two = 1
    THEN conversation.user_one   
 END
 WHERE `user_one` = 1 OR `user_two` = 1

您还可以通过对这些条件中的每一个进行左连接，然后在选择语句中使用 case 语句来确定从两个表中的哪一个显示记录来实现类似的效果.

You can also achieve a similiar affect by left joining on each of these conditions and then using the case statement in your select statement to determine which one of the two tables to display records from.

这篇关于MySQL 使用 Case 语句连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！