mysql - 在两个不同条件下查询同一个表中同一字段的多个列

Question

此 tblName 表有 4 列 {Date}、{Status}、{Meal}、{Type}我想在侧子查询中使用条件来显示在不同的列中

Select Date, Status, Meal
    (Select Type as Special
     from tblName
     where Type In ('SSS','MMM')),
    (Select Type as Normal
     from tblName
     where Type Not IN ('SSS','MMM'))
From tblName

我收到错误消息

Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Answer 1

您正在做的是:对于 tblName 中的每条记录，从 tblName 中选择所有类型，其中类型位于 ('SSS','MMM')。所有这些类型都应在 tblName 的结果行中显示为一列。这当然行不通。您可以做的是为tblName中的每条记录选择一个值。例如max(type)。

但是，您真正想要的似乎只是在类型为“SSS”或“MMM”时显示“特殊”，否则显示“正常”？

Select Date, Status, Meal,
  case when Type In ('SSS','MMM') then 'special' als 'normal' end as kind
From tblName;

或者在两个单独的列中显示类型？

Select Date, Status, Meal,
  case when Type In ('SSS','MMM') then Type end as Special,
  case when Type Not In ('SSS','MMM') then Type end as Normal
From tblName;