我一直在努力在GROUP BY
中使用CASE
表达式。我不确定这是否是查询我想要什么的最佳方式,所以请告诉我是否有更好的方式,但这是我能找到的最接近的方式。我现在遇到的问题是给GROUP BY
语句加标签。
ID Crossing_Order Barhab_Weighted
1 0 120
2 0 0
3 1 200
4 1 500
5 2 200
6 3 10
7 4 500
我很想买一张这样的桌子:
Crossing_Order Over_100 Under_100
zero 1 1
one 0 2
more_than_1 1 2
这个代码让我接近:
SELECT
count(distinct(case when a.barhab_weighted > 100 then site_id end)) as Over_100
,count(distinct(case when a.barhab_weighted <= 100 then site_id end)) as Under_100
FROM site_details as a
GROUP BY case
when a.crossing_order = 0 then 'zero'
when a.crossing_order = 1 then 'one'
when a.crossing_order > 1 then 'more_than_1' end
我得到的结果很接近,但我无法正确标记。如何显示"零"one_answers"一"值?
Over_100 Under_100
1 1 1
2 0 2
3 1 2
我不知道为什么要在唯一列上使用count(distinct)
。
但是您需要SELECT
中的CASE
逻辑。如果使用横向连接定义一次,这是最简单的:
SELECT v.Crossing_Order,
count(distinct case when sd.barhab_weighted > 100 then sd.site_id end)) as Over_100,
count(distinct case when sd.barhab_weighted <= 100 then sd.site_id end)) as Under_100
FROM site_details sd CROSS APPLY
(VALUES (case when sd.crossing_order = 0 then 'zero'
when sd.crossing_order = 1 then 'one'
when sd.crossing_order > 1 then 'more_than_1'
end)
) v(Crossing_Order)
GROUP BY v.Crossing_Order;
注意,我还将表别名a
更改为有意义的内容——sd
——表的缩写。
您可以使用以下条件聚合:
select
case
when a.crossing_order = 0 then 'zero'
when a.crossing_order = 1 then 'one'
when a.crossing_order > 1 then 'more_than_1'
end cat,
sum(case when barhab_weighted >= 100 then 1 else 0 end) over_100,
sum(case when barhab_weighted < 100 then 1 else 0 end) under_100
from site_details
group by
case
when a.crossing_order = 0 then 'zero'
when a.crossing_order = 1 then 'one'
when a.crossing_order > 1 then 'more_than_1'
end
执行case
以在派生表中对crossing_order进行分组。GROUP BY
的结果:
select crossing_order_grouped,
count(distinct case when barhab_weighted > 100 then site_id end) as Over_100,
count(distinct case when barhab_weighted <= 100 then site_id end) as Under_100
from
(
SELECT case when crossing_order = 0 then 'zero'
when crossing_order = 1 then 'one'
when crossing_order > 1 then 'more_than_1'
end crossing_order_grouped,
Barhab_Weighted
from site_details
) dt
group by crossing_order_grouped
GROUP BY
不向结果添加字段,您需要SELECT
该大小写表达式,并在GROUP BY
中重复它。
SELECT CASE
WHEN a.crossing_order = 0 THEN 'zero'
WHEN a.crossing_order = 1 THEN 'one'
WHEN a.crossing_order > 1 THEN 'more_than_1'
END AS crossing_order
, COUNT(DISTINCT CASE WHEN a.barhab_weighted > 100 THEN site_id END) AS Over_100
, COUNT(DISTINCT CASE WHEN a.barhab_weighted <= 100 THEN site_id END) AS Under_100
FROM site_details AS a
GROUP BY CASE WHEN a.crossing_order = 0 THEN 'zero'
WHEN a.crossing_order = 1 THEN 'one'
WHEN a.crossing_order > 1 THEN 'more_than_1'
END
注意:如果你真的希望数据按照你提供的顺序,你可能需要另一个CASE表达式来将cross_order"when"转换为合适的值;否则"零"将排在最后。