我正在处理 Oracle 12c R1 db，并有一个包含示例数据的示例视图，如下所示：
视图名称：CUST_HOTEL_VIEW
+----------------+---------------+---------------+
| Customer | Hotel | Booked Status |
+----------------+---------------+---------------+
| John Smith | Beverly Hills | Booked |
| John Smith | Royal Palms | |
| Marilyn Lawson | Beverly Hills | |
| John Smith | Ritz-Carlton | |
| Marilyn Lawson | Royal Palms | |
| Sarah Elliot | Royal Palms | |
| Sarah Elliot | Ritz-Carlton | Booked |
| Sarah Elliot | Royal Palms | Booked |
+----------------+---------------+---------------+
从上面的数据中，我试图通过行总计、列总计和每个客户预订的酒店数量来获得低于枢轴输出的数据：
+----------------+-------------+---------------+--------------+-------------+----------+
| Customer | Royal Palms | Beverly Hills | Ritz-Carlton | Grand Total | # Booked |
+----------------+-------------+---------------+--------------+-------------+----------+
| John Smith | 1 | 1 | 1 | 3 | 1 |
| Marilyn Lawson | 1 | 1 | | 2 | - |
| Sarah Elliot | 2 | | 1 | 3 | 2 |
| Grand Total | 4 | 2 | 2 | 8 | 3 |
+----------------+-------------+---------------+--------------+-------------+----------+
我尝试下面的查询来生成枢轴数据
SELECT * FROM
(
SELECT CUSTOMER, HOTEL
FROM CUST_HOTEL_VIEW
)
PIVOT
(
COUNT(HOTEL)
FOR HOTEL IN ('Royal Palms' as "Royal Palms",'Beverly Hills' as "Beverly Hills",'Ritz-Carlton' as "Ritz-Carlton")
)
ORDER BY CUSTOMER
我想知道：
1. 如何包含行总计
2. 如何包含列总计
3. 如何包含已预订酒店的数量以及
3.是否可以在PIVOT FOR HOTEL IN子句中编写子查询。 （我尝试了子查询但出现错误）
我很感谢对此的任何帮助。
Thanks,
Richa
只需使用条件聚合：
SELECT COALESCE(customer, 'Grand Total') as customer,
SUM(CASE WHEN Hotel = 'Royal Palms' THEN 1 ELSE 0 END) as "Royal Palms",
SUM(CASE WHEN Hotel = 'Beverly Hills' THEN 1 ELSE 0 END) as "Beverly Hills",
SUM(CASE WHEN Hotel = 'Ritz-Carlton' THEN 1 ELSE 0 END) as "Ritz-Carlton" ,
COUNT(*) as "Grand Total",
COUNT(Booked_Status) as "Num Booked"
FROM CUST_HOTEL_VIEW
GROUP BY ROLLUP(CUSTOMER)
ORDER BY CUSTOMER;
条件聚合则灵活得多pivot。就我个人而言，我认为没有理由pivot语法：它很好地完成一件事，但不像传统 SQL 语句那样构成构建块。
ROLLUP()也很有帮助。您还可以使用：
GROUP BY GROUPING SETS ( (CUSTOMER), () )
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)
