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

Question

这个tblName表有4列{日期}，{状态}，{膳食}，{类型}我想在侧子查询中使用条件以显示在不同的列中

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;

Answer 2

可能是这样的：

Select t1.Date, t1.Status, t1.Meal
    (t2.Type as Special),
    (t3.Type as Normal )
From tblName t1 LEFT JOIN
tblName t2
ON t1.ID=t2.ID
LEFT JOIN 
tblName t3
ON t1.ID=t3.ID
where t2.Type Not In ('SSS','MMM') 
OR t3.Type In ('SSS','MMM') 

或

条件聚合

Select Date, Status, Meal
    (case when Type IN ('SSS','MMM') then Type else null end case) 
     )Type as Special,
   (case when Type NOT IN ('SSS','MMM') then Type else null end case) 
     )Type as Normal
From tblName