Order Table.Reprpesents Order Date and Created 是插入記錄的時間
Price OrderDate Created
240.00 2021-12-26 2021-12-26 05:17:38.710
13.00 2021-12-26 2021-12-26 04:17:28.810
4.00 2021-12-26 2021-12-26 03:07:50.507
7.00 2021-12-26 2021-12-26 02:07:50.507
119.00 2021-12-26 2021-12-26 01:07:50.507
16 2021-12-25 2021-12-26 23:56:06.810
108.00 2021-12-25 2021-12-26 12:56:02.140
162.00 2021-12-25 2021-12-26 09:50:54.667
40.00 2021-12-25 2021-12-26 05:17:38.710
23.00 2021-12-25 2021-12-26 04:17:28.810
40.00 2021-12-25 2021-12-26 03:07:50.507
7.00 2021-12-25 2021-12-26 02:07:50.507
100.00 2021-12-25 2021-12-26 01:07:50.507
此表中的最新記錄在（創建值）- 05:17:38.710
Select Max(Created) From dbo.TenantOrder -
輸出 - 2021-12-26 05:17:38.710
26 日的這個時間，即今天的銷售日期
Select SUM(Price) From dbo.TenantOrder Where OrderDate = (Select Max(OrderDate) From dbo.TenantOrder)
同時我想知道25號的銷售額
Select SUM(Price) From dbo.TenantOrder Where OrderDate = (Select (Max(OrderDate) -1) From dbo.TenantOrder) and Created <= (Select Max(Created) -1 From dbo.TenantOrder)
我希望每天都有這種記錄，即按訂單日期和最大創建時間分組。所以就像在下午 5 點，每天的銷售額是多少
uj5u.com熱心網友回復：
似乎您想要對每一天進行分組總計，但僅限于與表中最新行標記相同時間的時間。
在 SQL Server 中，您可以使用以下
DECLARE @time time = (
SELECT TOP (1)
CAST(o.OrderDate AS time)
FROM dbo.TenantOrder o
ORDER BY o.OrderDate DESC);
SELECT
date = CAST(o.OrderDate AS date),
total = SUM(o.price)
FROM TenantOrder o
WHERE CAST(o.OrderDate AS time) <= @time
GROUP BY
CAST(o.OrderDate AS date);
資料庫<>小提琴
uj5u.com熱心網友回復：
嘗試以下代碼：
selec sum(Price) as SuumPrice from dbo.TenantOrder
group by OrderDate
并且在特定時間：
select sum(price) as SuumPrice from
(
select * from dbo.TenantOrder where CONVERT(VARCHAR(5),Created ,108) = N'12:10'
) as Dt
group by Dt.OrderDate
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/393888.html
標籤：sql sql-server