mysql 卸载“WHERE”条件中指定的一个特定值的附加列

at0kjp5o 于 2023-02-03 发布在 Mysql

关注(0)|答案(1)|浏览(143)

根据这样的请求：

WITH t as(
   SELECT a.Id,
      a.Name,
      a.Description,
      b.Title,
      c.Role
   FROM aTable AS a
      LEFT JOIN bTable AS b ON a.Id=b.uuid
      LEFT JOIN cTable AS c ON b.uuid=c.roleId
   WHERE c.role='major' OR c.role='captian'
   GROUP BY a.Id)
SELECT t.Id,
   t.Name,
   t.Description,
   t.Title,
   t.Role,
   d.Email
FROM t
LEFT JOIN dTable AS d ON t.Id=d.Guid

我收到两个角色的邮件（少校和上尉）。怎样只收船长的邮件？
我附上我的脚本的结果

和预期结果

mysql

来源：https://stackoverflow.com/questions/75313208/unloading-an-additional-column-for-one-specific-value-specified-in-the-where-c

1条答案

按热度按时间

iklwldmw1#

使用IF()表达式。

WITH t as(
   SELECT a.Id,
      a.Name,
      a.Description,
      b.Title,
      c.Role
   FROM aTable AS a
      INNER JOIN bTable AS b ON a.Id=b.uuid
      INNER JOIN cTable AS c ON b.uuid=c.roleId
   WHERE c.role='major' OR c.role='captain'
   GROUP BY a.Id)
SELECT t.Id,
   t.Name,
   t.Description,
   t.Title,
   t.Role,
   IF(c.role = 'captain', d.Email, '') AS Email
FROM t
LEFT JOIN dTable AS d ON t.Id=d.Guid

赞(0）回复(0）举报 2023-02-03

我来回答

mysql 卸载“WHERE”条件中指定的一个特定值的附加列

1条答案

相关问题

热门标签

最新问答