不满足任何条件时添加虚拟数据

Question

我有一个存储过程，它返回一个带有不同子句的集合，这是我遇到一点问题的部分：

SELECT CASE             WHEN td.BSP <= 2    THEN '01-02'
WHEN td.BSP <= 3    THEN '02-03'
WHEN td.BSP <= 5    THEN '03-05'
WHEN td.BSP <= 8    THEN '05-08'
WHEN td.BSP <= 13   THEN '08-13'
WHEN td.BSP <= 21   THEN '13-21'
WHEN td.BSP <= 34   THEN '21-34'
WHEN td.BSP <= 55   THEN '34-55'
WHEN td.BSP <= 89   THEN '55-89'
WHEN td.BSP <= 1000 THEN '89-1000'
END AS label

这没关系，并且像我想要的那样工作，但是例如，当没有BSP <=3数据时，它不会打印该行，这在所有情况下都是可以的，除了我希望拥有它，无论是 0 还是虚拟值。这是之后，因为它被 excel 中的数据透视表选择，并且 excel 仅为可用数据创建标题。所以我也需要有括号的行，我也没有数据。

我在这里添加完整的语句：

DECLARE @RaceTrack CHAR(5)= 'Bala';
DECLARE @Category VARCHAR(4000)= 'All, Class, Days, Distance, Race, Runners, Total Bracket Count, Runners2';
DECLARE @TrotPace VARCHAR(10)= 'Excluded';
WITH CTE_TblData
AS (SELECT td.EVENT_ID, 
td.MENU_HINT, 
SUBSTRING(td.menu_hint, 7, 4) AS [RaceTrack],
td.EVENT_NAME, 
td.BSP, 
td.EVENT_DT AS Expr1003, 
td.WIN_LOSE, 
COUNT(*) OVER(PARTITION BY event_id) AS [RaceRunners], 
td.[Race No] AS RaceNo, 
replace(LEFT(SUBSTRING(td.EVENT_NAME, CHARINDEX(' ', td.EVENT_NAME) + 1, LEN(td.EVENT_NAME)), CHARINDEX(' ', SUBSTRING(td.EVENT_NAME, CHARINDEX(' ', td.EVENT_NAME) + 2, LEN(td.EVENT_NAME)))), 'm', '') AS Distance, 
REVERSE(LEFT(REVERSE(td.EVENT_NAME), CHARINDEX(' ', REVERSE(td.EVENT_NAME)) - 1)) AS Class, 
FORMAT(CONVERT(DATETIME, td.EVENT_DT, 103), 'dddd') AS [Day], 
DATEPART(WEEKDAY, CONVERT(DATETIME, td.EVENT_DT, 103)) AS [DayNumber]
FROM tblData td
WHERE(@TrotPace <> 'Excluded' OR td.event_name NOT LIKE '%trot%' AND td.event_name NOT LIKE '%pace%') AND SUBSTRING(td.menu_hint, 7, 4) = @RaceTrack),
NewCTE1_PriceLabel
AS (SELECT td.*, price.label AS [PriceBracketLabel]
FROM CTE_TblData AS td
CROSS APPLY
(
SELECT CASE    WHEN td.BSP <= 2    THEN '01-02'
WHEN td.BSP <= 3    THEN '02-03'
WHEN td.BSP <= 5    THEN '03-05'
WHEN td.BSP <= 8    THEN '05-08'
WHEN td.BSP <= 13   THEN '08-13'
WHEN td.BSP <= 21   THEN '13-21'
WHEN td.BSP <= 34   THEN '21-34'
WHEN td.BSP <= 55   THEN '34-55'
WHEN td.BSP <= 89   THEN '55-89'
WHEN td.BSP <= 1000 THEN '89-1000'
END AS label
) AS price),
NewCTE2_WindowedCount
AS (SELECT *, 
COUNT(*) OVER(PARTITION BY event_id, 
PriceBracketLabel) AS [PriceBracketRunners]
FROM NewCTE1_PriceLabel),
DataCube
AS (SELECT td.event_id, 
td.MENU_HINT, 
td.EVENT_NAME, 
td.Expr1003, 
td.RaceNo, 
td.Distance, 
td.Class, 
td.[Day], 
CASE WHEN brackets.category = brackets2.category then  brackets.category ELSE  brackets.category  + ' : ' + brackets2.category END  AS bracket_category, 
CASE WHEN brackets.label = brackets2.label THEN td.PriceBracketlabel + ' : ' + brackets.label ELSE td.PriceBracketlabel + ' : ' + brackets.label + ' : ' + brackets2.label END AS bracket_label, 
SUM(CASE WHEN td.WIN_LOSE = 1 THEN 1      ELSE 0 END) AS CountHorses_Winners, 
SUM(CASE WHEN td.WIN_LOSE = 1 THEN td.BSP ELSE 0 END) AS SumPrices_Winners, 
SUM(CASE WHEN td.WIN_LOSE = 0 THEN 1      ELSE 0 END) AS CountHorses_Losers, 
SUM(CASE WHEN td.WIN_LOSE = 0 THEN td.BSP ELSE 0 END) AS SumPrices_Losers
FROM NewCTE2_WindowedCount AS td
CROSS APPLY
(
SELECT 'All' AS category, 
'All' AS label
UNION ALL
SELECT 'Runners' AS category,
CASE
WHEN td.RaceRunners <= 7    THEN '01-07'
WHEN td.RaceRunners <= 12   THEN '07-12'
WHEN td.RaceRunners <= 1000 THEN '12-1000'
END AS label
UNION ALL
SELECT 'Total Bracket Count' AS category,
CASE
WHEN td.PriceBracketRunners = 1      THEN '01'
WHEN td.PriceBracketRunners IN(2, 3) THEN '02-03'
WHEN td.PriceBracketRunners > 3      THEN '04-1000'
END AS label
UNION ALL
SELECT 'Distance' AS category,
CASE
WHEN td.distance <= 1200  THEN '0-1200'
WHEN td.distance <= 1600  THEN '1200-1600'
WHEN td.distance <= 10000 THEN '1600-10000'
END AS label
UNION ALL
SELECT 'Race' AS category,
CASE
WHEN td.raceno < 5     THEN '0-4'
WHEN td.raceno < 1000  THEN '04-1000'
END AS label
UNION ALL
--the stuff after the "then" is the title for the column, the stuff inside the brackets of "in" is what it filters
SELECT 'Class' AS category,
CASE
WHEN td.Class IN('Mdn')                                            THEN 'Mdn'
WHEN td.Class IN('Cl1', 'Cl2', 'Cl3')                              THEN 'Cl1,Cl2,Cl3'
WHEN td.Class IN('Cl4', 'Cl5', 'Cl6')                              THEN 'Cl4,Cl5,Cl6'
WHEN td.Class IN('2yo')                                            THEN '2yo'
WHEN td.Class IN('3yo')                                            THEN '3yo'
WHEN td.Class IN('Hcp', 'Hcap')                                    THEN 'Hcp'
WHEN td.Class IN('Qlty', 'Listed', 'Grp3', 'Grp2', 'Grp1', 'WFA')  THEN 'Qlty,Listed,Grp3,Grp2,Grp1,WFA'
END AS label
UNION ALL
SELECT 'Days' AS category,
CASE
WHEN td.DayNumber IN(1, 2, 3) THEN 'Sun-Mon-Tues'
WHEN td.DayNumber IN(4, 5)    THEN 'Wed-Sat'
WHEN td.DayNumber IN(6, 7)    THEN 'Thurs-Fri'
END AS label
) AS brackets 
cross apply
(
SELECT 'All' AS category, 
'All' AS label
UNION ALL
SELECT 'Runners' AS category,
CASE
WHEN td.RaceRunners <= 7    THEN '01-07'
WHEN td.RaceRunners <= 12   THEN '07-12'
WHEN td.RaceRunners <= 1000 THEN '12-1000'
END AS label
UNION ALL
SELECT 'Total Bracket Count' AS category,
CASE
WHEN td.PriceBracketRunners = 1      THEN '01'
WHEN td.PriceBracketRunners IN(2, 3) THEN '02-03'
WHEN td.PriceBracketRunners > 3      THEN '04-1000'
END AS label
UNION ALL
SELECT 'Distance' AS category,
CASE
WHEN td.distance <= 1200  THEN '0-1200'
WHEN td.distance <= 1600  THEN '1200-1600'
WHEN td.distance <= 10000 THEN '1600-10000'
END AS label
UNION ALL
SELECT 'Race' AS category,
CASE
WHEN td.raceno < 5     THEN '0-4'
WHEN td.raceno < 1000  THEN '04-1000'
END AS label
UNION ALL
--the stuff after the "then" is the title for the column, the stuff inside the brackets of "in" is what it filters
SELECT 'Class' AS category,
CASE
WHEN td.Class IN('Mdn')                                            THEN 'Mdn'
WHEN td.Class IN('Cl1', 'Cl2', 'Cl3')                              THEN 'Cl1,Cl2,Cl3'
WHEN td.Class IN('Cl4', 'Cl5', 'Cl6')                              THEN 'Cl4,Cl5,Cl6'
WHEN td.Class IN('2yo')                                            THEN '2yo'
WHEN td.Class IN('3yo')                                            THEN '3yo'
WHEN td.Class IN('Hcp', 'Hcap')                                    THEN 'Hcp'
WHEN td.Class IN('Qlty', 'Listed', 'Grp3', 'Grp2', 'Grp1', 'WFA')  THEN 'Qlty,Listed,Grp3,Grp2,Grp1,WFA'
END AS label
UNION ALL
SELECT 'Days' AS category,
CASE
WHEN td.DayNumber IN(1, 2, 3) THEN 'Sun-Mon-Tues'
WHEN td.DayNumber IN(4, 5)    THEN 'Wed-Sat'
WHEN td.DayNumber IN(6, 7)    THEN 'Thurs-Fri'
END AS label
) AS brackets2
-- where brackets.category  <> brackets2.category
GROUP BY td.event_id, 
td.MENU_HINT, 
td.EVENT_NAME, 
td.Expr1003, 
td.RaceNo, 
td.Distance, 
td.Class, 
td.[Day], 
brackets.category, 
brackets2.category,
td.PriceBracketlabel, 
brackets.label,
brackets2.label),
WinnerGroups
AS (SELECT *, 
SUM(CountHorses_Winners) OVER(PARTITION BY bracket_category, 
bracket_label
ORDER BY Expr1003, 
RaceNo, 
event_id) AS winner_group_id, 
SumPrices_Winners - CountHorses_Winners - CountHorses_Losers AS line_profit
FROM DataCube),
GroupTotals
AS (SELECT bracket_category, 
bracket_label, 
winner_group_id, 
SUM(line_profit) AS Group_Total_Profit
FROM WinnerGroups
GROUP BY bracket_category, 
bracket_label, 
winner_group_id),
GroupSteppedTotals
AS (SELECT bracket_category, 
bracket_label, 
winner_group_id, 
Group_Total_Profit, 
SUM(Group_Total_Profit) OVER(PARTITION BY bracket_category, 
bracket_label
ORDER BY winner_group_id ROWS BETWEEN 0 PRECEDING AND CURRENT ROW) AS Group_Total1_Profit, 
SUM(Group_Total_Profit) OVER(PARTITION BY bracket_category, 
bracket_label
ORDER BY winner_group_id ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS Group_Total2_Profit, 
SUM(Group_Total_Profit) OVER(PARTITION BY bracket_category, 
bracket_label
ORDER BY winner_group_id ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS Group_Total3_Profit, 
SUM(Group_Total_Profit) OVER(PARTITION BY bracket_category, 
bracket_label
ORDER BY winner_group_id ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) AS Group_Total5_Profit, 
SUM(Group_Total_Profit) OVER(PARTITION BY bracket_category, 
bracket_label
ORDER BY winner_group_id ROWS BETWEEN 7 PRECEDING AND CURRENT ROW) AS Group_Total8_Profit
FROM GroupTotals),
RunningTotals
AS (SELECT *, 
SUM(line_profit) OVER(PARTITION BY bracket_category, 
bracket_label, 
winner_group_id
ORDER BY Expr1003 DESC, 
RaceNo DESC, 
event_id DESC) - line_profit AS Group_Partial_Profit
FROM WinnerGroups),
LaggedProfits
AS (SELECT RunningTotals.*, 
GroupSteppedTotals.Group_Total_Profit, 
LAG(RunningTotals.winner_group_id) OVER(PARTITION BY RunningTotals.bracket_category, 
RunningTotals.bracket_label
ORDER BY RunningTotals.Expr1003, 
RunningTotals.RaceNo, 
RunningTotals.event_id) AS previous_winner_group_id, 
(GroupSteppedTotals.Group_Total1_Profit - RunningTotals.Group_Partial_Profit) AS profit_back1winner, 
(GroupSteppedTotals.Group_Total2_Profit - RunningTotals.Group_Partial_Profit) AS profit_back2winner, 
(GroupSteppedTotals.Group_Total3_Profit - RunningTotals.Group_Partial_Profit) AS profit_back3winner, 
(GroupSteppedTotals.Group_Total5_Profit - RunningTotals.Group_Partial_Profit) AS profit_back5winner, 
(GroupSteppedTotals.Group_Total8_Profit - RunningTotals.Group_Partial_Profit) AS profit_back8winner
FROM RunningTotals
INNER JOIN GroupSteppedTotals ON GroupSteppedTotals.bracket_category = RunningTotals.bracket_category
AND GroupSteppedTotals.bracket_label = RunningTotals.bracket_label
AND GroupSteppedTotals.winner_group_id = RunningTotals.winner_group_id)
SELECT
event_id, 
MENU_HINT, 
EVENT_NAME, 
Expr1003, 
Class, 
RaceNo, 
bracket_category, 
bracket_label, 
--  winner_group_id, -- Debugging Column
CountHorses_Winners, 
SumPrices_Winners, 
CountHorses_Losers, 
SumPrices_Losers, 
--  line_profit, -- Debugging Column
--  Group_Partial_Profit, -- Debugging Column
--  Group_Total_Profit, -- Debugging Column
CASE WHEN CountHorses_Winners > 0 AND previous_winner_group_id >= 1 THEN profit_back1winner END AS Profit_Winner_1,
CASE WHEN CountHorses_Winners > 0 AND previous_winner_group_id >= 2 THEN profit_back2winner END AS Profit_Winner_2,
CASE WHEN CountHorses_Winners > 0 AND previous_winner_group_id >= 3 THEN profit_back3winner END AS Profit_Winner_3,
CASE WHEN (previous_winner_group_id >= 3 and CountHorses_Winners <> 0) THEN  cast(((profit_back1winner +  profit_back2winner +profit_back3winner)/3) as decimal(10,2)) END AS Average_Winner, 
-- CASE WHEN CountHorses_Winners > 0 AND previous_winner_group_id >= 5 THEN profit_back5winner END   AS Profit_Winner_5,
-- CASE WHEN CountHorses_Winners > 0 AND previous_winner_group_id >= 8 THEN profit_back8winner END   AS Profit_Winner_8,
CASE WHEN CountHorses_Winners = 0 AND previous_winner_group_id >= 1 AND CountHorses_Losers > 0 THEN profit_back1winner END AS Profit_NotWinner_1,
CASE WHEN CountHorses_Winners = 0 AND previous_winner_group_id >= 2 AND CountHorses_Losers > 0 THEN profit_back2winner END AS Profit_NotWinner_2,
CASE WHEN CountHorses_Winners = 0 AND previous_winner_group_id >= 3 AND CountHorses_Losers > 0 THEN profit_back3winner END AS Profit_NotWinner_3,
CASE WHEN CountHorses_Winners = 0 AND previous_winner_group_id >= 3 AND CountHorses_Losers > 0 THEN cast(((profit_back1winner +  profit_back2winner +profit_back3winner)/3) as decimal(10,2)) END AS Average_NotWinner
-- CASE WHEN CountHorses_Winners = 0 AND previous_winner_group_id >= 5 AND CountHorses_Losers > 0 THEN profit_back5winner END   AS Profit_NotWinner_5,
-- CASE WHEN CountHorses_Winners = 0 AND previous_winner_group_id >= 8 AND CountHorses_Losers > 0 THEN profit_back8winner END   AS Profit_NotWinner_8
FROM LaggedProfits

WHERE bracket_category in ('All', 'Class', 'Days', 'Distance', 'Race', 'Runners', 'Total Bracket Count', 'Class : Days','Class : Distance','Class : Race', 'Class : Runners', 'Class : Total Bracket Count', 'Days : Distance', 'Days : Race', 'Days : Runners', 
'Days : Total Bracket Count', 'Distance : Race', 'Distance : Runners', 'Distance : Total Bracket Count', 'Race : Runners', 'Race : Total Bracket Count', 'Runners : Total Bracket Count')
--and bracket_label = '03-05 : All'
-- WHERE --@Category IS NULL OR bracket_category IN (SELECT Value FROM dbo.FnSplit(@Category, ',')) and
--event_id = 145688261
--AND LaggedProfits.MENU_HINT  LIKE '%MVal%'
--bracket_category = 'Runners2'    AND 
--  and  bracket_label = '03-05 : Hcp : Wed-Sat'
--LaggedProfits.MENU_HINT LIKE '%Flem%'
ORDER BY Expr1003 DESC, 
RaceNo DESC, 
event_id DESC, 
bracket_category, 
bracket_label, 
Class;

基本上我需要的是检索所有组合的信息，即使没有，类似于"isnull"，我认为所以当导出到 excel 时，数据库中有没有组合的类别的标题

Answer 1

使用left join. 像这样：

select v.label, count(td.bsp)
from (values (0, 2, '01-02'),
(2, 2, '02-03'),
. . .  -- continue with the remaining groups
) v(lo, hi, label) left join
td
on td.BSP > v.lo and td.BSP = v.hi
group by v.label
order by v.lo;

Answer 2

这是否像在CASE语句中使用ELSE一样简单？

SELECT CASE 
WHEN td.BSP <= 2 THEN '01-02'
WHEN td.BSP <= 3 THEN '02-03'
WHEN td.BSP <= 5 THEN '03-05'
WHEN td.BSP <= 8 THEN '05-08'
WHEN td.BSP <= 13 THEN '08-13'
WHEN td.BSP <= 21 THEN '13-21'
WHEN td.BSP <= 34 THEN '21-34'
WHEN td.BSP <= 55 THEN '34-55'
WHEN td.BSP <= 89 THEN '55-89'
WHEN td.BSP <= 1000 THEN '89-1000'
ELSE '0'
END AS label