select
UnsubmittedCount = (
case
when
ApproveStatus
in
(
'未提交'
,
'已提交'
)
then
count
(FormID)
end
),
ApprovedCount = (
case
when
ApproveStatus
in
(
'已审核'
)
then
count
(FormID)
end
)
from
formLogistic
group
by
ApproveStatus
返回结果集为GroupBy后的多行数据,如何避开GroupBy?
可以使用max()或者sum()的方式,将casewhen一行化,如:
select
UnsubmittedCount =
max
(
case
when
ApproveStatus
in
(
'未提交'
,
'已提交'
)
then
count
(FormID)
end
)
from
formLogistic
group
by
ApproveStatus
但:聚合(CaseWhen(聚合)) 这种模式的查询方式,
会报一个错误:不能对包含聚合或子查询的表达式执行聚合函数。
可以优化为:
select
UnsubmittedCount =
COUNT
(
case
when
ApproveStatus
in
(
'未提交'
,
'已提交'
)
then
FormID
end
),
ApprovedCount =
COUNT
(
case
when
ApproveStatus
in
(
'已审核'
)
then
FormID
end
)
from
formLogistic