如何显示所有日期?现在我的约会在销售结束的时候才有空.我想显示所有的日期

Question

select date(products.date_of_sale) as Date,
count(case when products.product_type_id = 6 then product_types.id end) as "POYOYO"
and products.product_type_id = 55
and products.product_type_id = 55
and products.product_type_id = 55
from products
and products.product_type_id = 6
and products.product_type_id = 55
and products.product_type_id = 55
order by date(products.date_of_sale) desc

现在我的日期显示只有销售。和产品。Product_type_id = 55和products。Product_type_id = 55

Answer 1

您可以使用generate_series函数来构建您想要构建系列的一系列日期。假设您想要一个从第一次销售到最后一次销售的系列，它看起来像这样。

with cte_sales as (
select date(products.date_of_sale) as Date,
count(case when products.product_type_id = 6 then product_types.id end) as "Car_Rentals"
from products
join product_types on product_types.id = products.product_type_id
where date(products.date_of_sale) >= '2021-06-01'
and date(products.date_of_sale) <= current_date
and products.product_type_id = 6
and products.sales_category_id = 1
and products.sales_category_id <> 2
and products.status_id = 7
group by date(products.date_of_sale)
order by date(products.date_of_sale) desc
)
, cte_series as (
select generate_series(min(Date), max(Date), '1 day'::interval)::date as generated_series
from cte_sales)
select
*
from
cte_series series
left join
cte_sales sales
on sales.Date = series.generated_series

你可以用不同的方法生成级数，这是一个强大的功能。