我正在运行以下查询来输出添加订单和取消,这样我们就可以更有效地管理我们公司使用的许可证,使用带有查询返回数据的excel工作表。到目前为止,一切都正常,但我想知道或看看是否有一种方法可以合并“重复”的附加名称。例如,在屏幕截图中,我们可以看到购买了85台Unity接待机和4台购买了35美元的Unity接待机。这些是相同的许可证,我想要一个列只有Unity接待员,总共89个许可证计数。与取消版本的Unity接待员和Unity接待员相同,$35取消
下面是我的查询,我运行该查询来提取所有这些数据
select sod.add_on_name, sum(sod.add_on_qty) AS ADD_ON_QTY
from subscriber_order so
join subscriber_order_detail sod on
so.subscriber_order_id=sod.SUBSCRIBER_ORDER_ID
join corp_acct c on c.subscriber_id=so.subscriber_id
join PROV_CORP_ACCT_ZONE_XREF z on z.corp_acct_nbr=c.corp_acct_nbr
where add_on_name like 'Unity%'
and add_on_name not like '%Discount%'
and order_date >= Sysdate-90
and add_on_name
group by sod.add_on_name
order by case
when sod.add_on_name like '%Cancel%' then 2
else 1
end;
发布于 2019-03-20 03:11:47
可以在group by和case中使用like
Select
CASE
WHEN sod.add_on_name LIKE 'Unity Receptionist%' THEN 'Unity Receptionist'
END AS add_on_name,
SUM(sod.add_on_qty) AS ADD_ON_QTY
FROM subscriber_order so
JOIN subscriber_order_detail sod on so.subscriber_order_id=sod.SUBSCRIBER_ORDER_ID
join corp_acct c on c.subscriber_id=so.subscriber_id
join PROV_CORP_ACCT_ZONE_XREF z on z.corp_acct_nbr=c.corp_acct_nbr
where add_on_name like 'Unity%'
and add_on_name not like '%Discount%'
and order_date >= Sysdate-90
and add_on_name
GROUP BY
CASE
WHEN sod.add_on_name LIKE 'Unity Receptionist%' THEN 'Unity Receptionist'
END
order by case
when sod.add_on_name like '%Cancel%' then 2
else 1
end;
发布于 2019-03-20 04:15:05
使用
GROUP BY CASE WHEN add_on_name LIKE‘统一接待员%’然后‘统一接待员’结束
解决了我的问题。
https://stackoverflow.com/questions/55248251
复制相似问题