时间戳

首页 > 解决方案 > SQL Server 查询指示在 3 个不同时间段内销售的金额

sql - SQL Server 查询指示在 3 个不同时间段内销售的金额

问题描述

我正在尝试编写一个从表中选择一些数据的 SQL 查询。我想要的数据包括一件商品已售出多少次，目标是让列显示该商品在 30,60 和 90 天内售出多少次。我可以编写 3 个单独的查询来完成这项工作，但我想编写一个查询，以便数据都在一个结果表上。这是我到目前为止所拥有的：

DECLARE @30daysago DATETIME = DATEADD(DAY,-30,CAST(GETDATE() AS DATE));
DECLARE @90daysago DATETIME = DATEADD(DAY,-90,CAST(GETDATE() AS DATE));

Set NOCOUNT ON
Select company_info_1 from setup

select ii.itemnum, i.itemname,i.price, d.description, count(ii.itemnum)
from invoice_itemized ii
join invoice_totals IT on it.invoice_number=ii.invoice_number
join inventory i on i.itemnum=ii.itemnum
join departments d on d.dept_id=i.dept_id
where it.datetime > @30daysago and len(ii.itemnum) >4
group by ii.itemnum, i.itemname, d.description, i.price
order by count(ii.itemnum) desc

标签： sqlsql-server

解决方案

使用带有条件聚合的单个查询：

SELECT
    ii.itemnum,
    i.itemname,
    i.price,
    d.description,
    COUNT(ii.itemnum) AS total,
    COUNT(CASE WHEN it.datetime > DATEADD(DAY, -30, CAST(GETDATE() AS DATE))
               THEN ii.itemnum END) AS 30daysago,
    COUNT(CASE WHEN it.datetime > DATEADD(DAY, -90, CAST(GETDATE() AS DATE))
               THEN ii.itemnum END) AS 90daysago
FROM invoice_itemized ii
INNER JOIN invoice_totals IT
    ON it.invoice_number = ii.invoice_number
INNER JOIN inventory I
    ON i.itemnum = ii.itemnum
INNER JOIN departments d
    ON d.dept_id = i.dept_id
WHERE
    LEN(ii.itemnum) > 4
GROUP BY
    ii.itemnum,
    i.itemname,
    d.description,
    i.price
ORDER BY
    ii.itemnum DESC;

推荐阅读