例如:我有一张BILL_PRODUCT
表
product_vendor_id| product_site_id| type_of_product |
123 | 901 | Car
123 | 901 | Tyre
123 | 902 | Bike
124 | 801 | skateboard
123 | 901 | Car
.
.
.
乘积vendor_id
和product_site_id
具有一对多关系。
我想写一个类似的选择语句
select product_vendor_id,product_vendor_site_id,type_of_product, count(1)
from PRODUCT where product_type is not hull
group by product_vendor_id,product_vendor_site_id,type_of_product
order by product_vendor_id,product_vendor_site_id;
到这儿来计数。但在我的select语句中,我不希望我的product_vendor_id
和product_site_id
直接可见。我希望数据是这种格式
product_vendor_id| product_site_id| type_of_product |count
1 1 car 2
1 1 tyre 3
1 2 bike 5
2 1 skateboard 1
.
.
你能帮我写SELECT查询吗。
一个选项使用dense_rank()
生成新的id:
select
dense_rank() over(order by product_vendor_id) new_product_vendor_id,
dense_rank() over(order by product_site_id) new_product_site_id,
product_type,
count(*)
from product
where product_type is not hull
group by product_vendor_id, product_site_id, product_type