select
proCode,
proName,(CASE proType
WHEN'01'THEN'上衣'WHEN'02'THEN'帽子'WHEN'03'THEN'首饰'ELSE''END)AS proType
from
BAS_PROINFO
where proType in('01','02','03')//then 和 else 后面还可接 select 语句,看下面例子select
proCode,
proName,
proType
(CASE proType
WHEN'01'THEN(selectsum(num)from BAS_PROINFO_CLOTH where proType ='01')ELSE(sum(num))END)AS proNum
from
BAS_PROINFO
where proType in('01','02','03')groupby
proCode,
proName,
proType
4.2 union all 对两个结果集求并集,包含重复行
多个表数量关联求和(例子)
select
proCode,sum(num)from((select
proCodeA AS proCode,sum(numA)AS num
from
BAS_PROINFO_A
groupby
proCodeA
)UNIONALL(select
proCodeB AS proCode,sum(numB)AS num
from
BAS_PROINFO_B
groupby
proCodeB
))groupby
proCode
//union all 要求并集的表字段类型必须相同!!!