我有兩個選擇查詢：
選擇#1：
select
Date_Booking as 'Date', isnull(sum(Ticket_Price), 0) as 'Total sales'
from
(select
Date_Booking, Ticket_Price
from
Booking
where
month(Date_Booking) = 2 and year(Date_Booking) = 2022
union all
select Date_Shipping, Cost_Shipping
from Shipping
where month(Date_Shipping) = 2 and year(Date_Shipping) = 2022) t
group by
Date_Booking
選擇#2：
select
Date as 'Date', isnull(sum(Gas_Cost), 0) as 'Total Expenses'
from
(select Date, Gas_Cost
from BusGas
where month(Date) = 2 and year(Date) = 2022
union all
select Date, Wash_Cost
from BusWash
where month(Date) = 2 and year(Date) = 2022
union all
select Date, Drive_Money
from Driver_Expenses
where month(Date) = 2 and year(Date) = 2022
union all
select Date, Emp_money
from Emp_Expenses
where month(Date) = 2 and year(Date) = 2022
union all
select Date, Cost
from OtherExpenses
where month(Date) = 2 and year(Date) = 2022
union all
select Date, ServiceRoad_Cost
from RoadServices
where month(Date) = 2 and year(Date) = 2022) t
group by
Date
現在，我想組合這些結果，以便我有三列日期、總銷售額和總費用，以及沒有銷售額或費用的日期為 0。
如何解決這個問題？
uj5u.com熱心網友回復：
您需要按照以下步驟操作：
- 為日期范圍創建 CalenderTable。請參閱 Aaron Bertnard 在 MSSQLTips 上的精彩腳本
SET DATEFIRST 7, LANGUAGE us_english;
DECLARE @StartDate date = '20200101',
@Years int = 30;
;WITH seq(n) AS
(
SELECT 1 UNION ALL SELECT n 1 FROM seq
WHERE n < DATEDIFF(DAY, @StartDate, DATEADD(YEAR, @Years, @StartDate))
),
d(d) AS
(
SELECT DATEADD(DAY, n - 1, @StartDate) FROM seq
),
src AS
(
SELECT
TheDate = CONVERT(date, d),
TheDay = DATEPART(DAY, d),
TheDayName = DATENAME(WEEKDAY, d),
TheWeek = DATEPART(WEEK, d),
TheISOWeek = DATEPART(ISO_WEEK, d),
TheDayOfWeek = DATEPART(WEEKDAY, d),
TheMonth = DATEPART(MONTH, d),
TheMonthName = DATENAME(MONTH, d),
TheQuarter = DATEPART(QUARTER, d),
TheYear = DATEPART(YEAR, d),
TheFirstOfMonth = DATEFROMPARTS(YEAR(d), MONTH(d), 1),
TheLastOfYear = DATEFROMPARTS(YEAR(d), 12, 31),
TheDayOfYear = DATEPART(DAYOFYEAR, d)
FROM d
),
dim AS
(
SELECT
TheDate,
TheDay,
TheDaySuffix = CONVERT(char(2), CASE WHEN TheDay / 10 = 1 THEN 'th' ELSE
CASE RIGHT(TheDay, 1) WHEN '1' THEN 'st' WHEN '2' THEN 'nd'
WHEN '3' THEN 'rd' ELSE 'th' END END),
TheDayName,
TheDayOfWeek,
TheDayOfWeekInMonth = CONVERT(tinyint, ROW_NUMBER() OVER
(PARTITION BY TheFirstOfMonth, TheDayOfWeek ORDER BY TheDate)),
TheDayOfYear,
IsWeekend = CASE WHEN TheDayOfWeek IN (CASE @@DATEFIRST
WHEN 1 THEN 6 WHEN 7 THEN 1 END,7)
THEN 1 ELSE 0 END,
TheWeek,
TheISOweek,
TheFirstOfWeek = DATEADD(DAY, 1 - TheDayOfWeek, TheDate),
TheLastOfWeek = DATEADD(DAY, 6, DATEADD(DAY, 1 - TheDayOfWeek, TheDate)),
TheWeekOfMonth = CONVERT(tinyint, DENSE_RANK() OVER
(PARTITION BY TheYear, TheMonth ORDER BY TheWeek)),
TheMonth,
TheMonthName,
TheFirstOfMonth,
TheLastOfMonth = MAX(TheDate) OVER (PARTITION BY TheYear, TheMonth),
TheFirstOfNextMonth = DATEADD(MONTH, 1, TheFirstOfMonth),
TheLastOfNextMonth = DATEADD(DAY, -1, DATEADD(MONTH, 2, TheFirstOfMonth)),
TheQuarter,
TheFirstOfQuarter = MIN(TheDate) OVER (PARTITION BY TheYear, TheQuarter),
TheLastOfQuarter = MAX(TheDate) OVER (PARTITION BY TheYear, TheQuarter),
TheYear,
TheISOYear = TheYear - CASE WHEN TheMonth = 1 AND TheISOWeek > 51 THEN 1
WHEN TheMonth = 12 AND TheISOWeek = 1 THEN -1 ELSE 0 END,
TheFirstOfYear = DATEFROMPARTS(TheYear, 1, 1),
TheLastOfYear,
IsLeapYear = CONVERT(bit, CASE WHEN (TheYear % 400 = 0)
OR (TheYear % 4 = 0 AND TheYear % 100 <> 0)
THEN 1 ELSE 0 END),
Has53Weeks = CASE WHEN DATEPART(WEEK, TheLastOfYear) = 53 THEN 1 ELSE 0 END,
Has53ISOWeeks = CASE WHEN DATEPART(ISO_WEEK, TheLastOfYear) = 53 THEN 1 ELSE 0 END,
MMYYYY = CONVERT(char(2), CONVERT(char(8), TheDate, 101))
CONVERT(char(4), TheYear),
Style101 = CONVERT(char(10), TheDate, 101),
Style103 = CONVERT(char(10), TheDate, 103),
Style112 = CONVERT(char(8), TheDate, 112),
Style120 = CONVERT(char(10), TheDate, 120)
FROM src
)
SELECT *
INTO dbo.Calendar
FROM dim
ORDER BY TheDate
OPTION (MAXRECURSION 0);
GO
ALTER TABLE dbo.Calendar ALTER COLUMN TheDate date NOT NULL;
GO
ALTER TABLE dbo.Calendar ADD CONSTRAINT PK_Calendar PRIMARY KEY CLUSTERED(TheDate);
GO
- 現在，創建兩個公用表運算式，并使用日歷表將這兩個 CTE 連接起來。
;WITH cte_DateSales AS
(
select Date_Booking as 'Date',ISNULL(sum(Ticket_Price),0)as 'Total sales' from (
select Date_Booking,Ticket_Price
from Booking WHERE MONTH(Date_Booking) = 2 and YEAR(Date_Booking) = 2022
union all
select Date_Shipping,Cost_Shipping
from Shipping WHERE MONTH(Date_Shipping) = 2 and YEAR(Date_Shipping) = 2022 ) t group by Date_Booking
), cte_TotalExpenses AS
(
select Date as 'Date',ISNULL(sum(Gas_Cost),0) as 'Total Expenses'from (
select Date,Gas_Cost
from BusGas WHERE MONTH(Date) = 2 and YEAR(Date) = 2022
union all
select Date,Wash_Cost
from BusWash WHERE MONTH(Date) = 2 and YEAR(Date) = 2022
union all
select Date,Drive_Money
from Driver_Expenses WHERE MONTH(Date) = 2 and YEAR(Date) = 2022
union all
select Date,Emp_money
from Emp_Expenses WHERE MONTH(Date) = 2 and YEAR(Date) = 2022
union all
select Date,Cost
from OtherExpenses WHERE MONTH(Date) = 2 and YEAR(Date) = 2022
union all
select Date,ServiceRoad_Cost
from RoadServices WHERE MONTH(Date) = 2 and YEAR(Date) = 2022 ) t group by Date
)
SELECT c.Date, s.[Total Sales], e.[Total Expenses]
FROM dbo.Calendar AS c
LEFT OUTER JOIN cte_DateSales AS s
ON s.Date = c.Date
LEFT OUTER JOIN cte_TotalExpenses AS e
ON e.Date = c.Date
uj5u.com熱心網友回復：
您只需擴展您已經使用的技術。使用 UNION ALL 包括所有費用和銷售查詢，并添加一個“標志”以使用條件聚合。
select [Date],
sum(case IsSale when 1 then Value else 0 end) as [Total sales],
sum(case IsSale when 1 then 0 else Value end) as [Total Expenses]
from (
select Date_Booking as [Date], Ticket_Price as Value, cast(1 as bit) as IsSale
from Booking WHERE MONTH(Date_Booking) = 2 and YEAR(Date_Booking) = 2022
union all
select Date, Gas_Cost, 0
from BusGas WHERE MONTH(Date) = 2 and YEAR(Date) = 2022
union all
...
) as combined
group by [Date]
order by ...;
可以將形成派生表的查詢放入視圖中，以便您可以重用邏輯 - 這可能在我的經驗中。如果您這樣做，請不要包含日期過濾器，以便您可以在任何時期使用它。有更好的按日期過濾的方法，但這是一個不同的問題。
如前所述，您將需要添加日歷表，以強制包含各種事務表中不存在的日期。這
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/436641.html
下一篇：在SQL中連接表時如何避免重復