sql-server-2008 - SQL - 如果没有结果则显示 0

Question

我有一个结果集如下表:

C_Date    C_Time     Day        L_TID     Terminal_Name      Branch   Branch_Name           L_UID     C_Name            C_Unique    L_Result    C_Card
20140327  905        Thursday   4640601   046406 Virdi 4000  46406    Sr Blue Downs 046406  1220578   Tsutsu;Simamkele  1220578     0

发生的事情是，如果一个分支没有信息，它根本就不会出现。我想要的是尽可能显示 0。我已经尝试在 C_Date 列上做一个 CASE，但是一切都以未知的形式出现，并且仍然没有启动我的分支!

下面是我的脚本:

    SELECT 

 tEnter.C_Date, tEnter.C_Time, 
 datename (dw,tEnter.C_Date) AS [Day], 
CONVERT(CHAR(8), GETDATE () -7, 112) as [startdate],
CONVERT(CHAR(8), GETDATE () -1, 112) as [enddate], 
            tEnter.L_TID, 
            tTerminal.C_Name AS Terminal_Name, 
            tTerminal.C_Office AS Branch, 
            tTerminal.C_Place AS Branch_Name, 
            tEnter.L_UID, 
        tEnter.C_Name, 
        tEnter.C_Unique,
        tEnter.L_Result, 
        tEnter.C_Card,
   CASE tEnter.C_Date
        WHEN 1 THEN '1' WHEN 0 THEN 'Nothing' ELSE 'Unknown' END AS 'Test'

INTO #TEMPTABLE

FROM         tEnter INNER JOIN
                      tTerminal ON tEnter.L_TID = tTerminal.L_ID

WHERE 

         tEnter.C_Date between CONVERT(CHAR(8), GETDATE () -7, 112) and 
         CONVERT(CHAR(8), GETDATE () -1, 112)

select * from #TEMPTABLE

Answer 1

经过大量尝试，这成功了。

        SELECT DISTINCT C_Office
        INTO #Branch
        FROM tTerminal

        SELECT 

        ISNULL(tEnter.C_Date,0) AS C_Date,
        ISNULL( tEnter.C_Time,0) AS C_Time, 
        datename (dw,tEnter.C_Date) AS [Day], 
        CONVERT(CHAR(8), GETDATE () -7, 112) as [startdate],
        CONVERT(CHAR(8), GETDATE () -1, 112) as [enddate], 
                    tEnter.L_TID, 
                    tTerminal.C_Name AS Terminal_Name, 
                    BRN.C_Office AS Branch, 
                    tTerminal.C_Place AS Branch_Name, 
                    tEnter.L_UID, 
                tEnter.C_Name, 
                tEnter.C_Unique,
                tEnter.L_Result, 
                tEnter.C_Card

        INTO #TEMPTABLE2 
        FROM #Branch as BRN

                LEFT OUTER JOIN tEnter 
                on tEnter.C_Office = BRN.C_Office
                AND (tEnter.C_Date between CONVERT(CHAR(8), GETDATE () -7, 112) and CONVERT(CHAR(8), GETDATE () -1, 112))

                LEFT OUTER JOIN tTerminal 
                ON tEnter.L_TID = tTerminal.L_ID