Question

如何使用SQL语句，实现多条件分组统计

表 a
型号 是否报废 是否借出
a1 是
a1 否 是
a1 否
b1 是
b1 是
b1 否
c1 否 是
c1 否 是
c1 否
生成查询表
型号 未报废数量 已借出数量
a1 2 1
b1 1 0
c1 3 2
我使用的是Access 数据库，运行
“select 型号，count(case when 是否报废='否' then 型号 else 0 end) as 未报废数量，count(case when 是否借出='是' then 型号 else 0 end) as 已借出数量
from a
group by 型号”出错
使用where,只能弄出一列，
求高手解答

Answer 1

access和sql稍微有点区别，在一个楼主的统计函数用错了，应该是sum吧。

SELECT 型号, sum(IIf(是否报废='否',1,0)) AS 未报废数量, sum(IIf(是否借出='是',1,0)) AS 已借出数量
FROM a
GROUP BY 型号;

Answer 2

with t as
(
select 'a1' as "型号" ,'是' as "是否报废",'' as "是否借出" from dual
union all
select 'a1' as "型号" ,'否' as "是否报废",'是' as "是否借出" from dual
union all
select 'a1' as "型号" ,'否' as "是否报废",'' as "是否借出" from dual
union all
select 'b1' as "型号" ,'是' as "是否报废",'' as "是否借出" from dual
union all
select 'b1' as "型号" ,'是' as "是否报废",'' as "是否借出" from dual
union all
select 'b1' as "型号" ,'否' as "是否报废",'' as "是否借出" from dual
union all
select 'c1' as "型号" ,'否' as "是否报废",'是' as "是否借出" from dual
union all
select 'c1' as "型号" ,'否' as "是否报废",'是' as "是否借出" from dual
union all
select 'c1' as "型号" ,'否' as "是否报废",'' as "是否借出" from dual
)

select "型号",
count(case when "是否报废" = '否' then
"是否报废" end
) as "未报废数量",
count(case when "是否借出" = '是' then
"是否借出" end
) as "已借出数量"
from t
group by "型号"
order by "型号"

虽然在ORACLE中实现的

但是SQL语句简单修改就可以使用了

Answer 3

搂主的 SQL 语句，有2点问题

其一. ACCESS 不支持 CASE WHEN
要使用 IIF 函数来替换
语法
IIf(expr, truepart, falsepart)
IIf 函数的语法包含以下参数 （参数:为操作、事件、方法、属性、函数或过程提供信息的值。）：
参数 说明
expr 必选。要计算的表达式。
truepart 必选。expr 为 True 时返回的值或表达式。
falsepart 必选。expr 为 False 时返回的值或表达式。

其二. count 使用的不正确， 应该修改为 sum
select 型号，count( ... ) as 未报废数量

最后的SQL， 尝试修改为：

select
型号,
sum ( iif ( 是否报废='否' , 型号 , 0 ) ) as 未报废数量，
sum( iif ( 是否借出='是', 型号, 0 ) ) as 已借出数量
from a
group by 型号

看看能否获得正确结果么？