sql—在不满足任何条件时添加伪数据

ui7jx7zq  于 2021-08-09  发布在  Java
关注(0)|答案(2)|浏览(268)

我有一个存储过程，它返回一个包含不同子句的集合，这是我遇到的一个小问题：

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时，数据库中没有组合的类别有标题

sqlsql-server

2条答案

按热度按时间
sr4lhrrt

sr4lhrrt1#

使用 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;
赞(0）分享  回复(0）举报  2021-08-09
wfauudbj

wfauudbj2#

它是否像使用 ELSE 在你的 CASE 声明？

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
赞(0）分享  回复(0）举报  2021-08-09
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com