sql-server - 在 SQL Server 中左连接 2 个表

Question

我有一个特定的查询，可以为每个用户提供一些关于他们在线活动的统计信息：

select * 
from 
    (select  
         a.PartyID, a.PartyID - 819538 as ACCOUNT_ID,
         a.USERID, a.vip_status,
         sum(a.Game_Bet) as GB,
         sum(a.Game_Win) as GW, 
         -sum(a.Game_Bet) - sum(a.Game_Win) as ggr,
         -sum(a.Game_Bet_RM) - sum(a.Game_Win_RM) as ggr_RM,
         sum(a.RawLoyalty) as raw_LP,
         sum(a.RawLoyalty) / 40000 as LP,
         sum(a.RawLoyalty) / 40000 / 100 as cash_LP,
         case
            when (-sum(a.Game_Bet) - sum(a.Game_Win)) = 0 
               then 0 
               else (sum(a.RawLoyalty) / 40000 / 100) / (-sum(a.Game_Bet) - sum (a.Game_Win)) * 100 
         end as LPper_full,
         case 
            when (-sum(a.Game_Bet_RM) - sum(a.Game_Win_RM)) = 0 
               then 0 
               else (sum(a.RawLoyalty) / 40000 / 100) / (-sum(a.Game_Bet_RM) -sum(a.Game_Win_RM)) * 100 
         end as LPper_ggr_RM 
     from
         (select 
              AccountTranHourlyAggregate.Datetime,
              AccountTranHourlyAggregate.PartyID,
              external_mpt.USER_CONF.USERID, VIP_STATUS.name AS vip_status,
              AccountTranHourlyAggregate.Currency,
              case
                 when AccountTranHourlyAggregate.TranType = 'GAME_BET' 
                    then AccountTranHourlyAggregate.AmountReal + AccountTranHourlyAggregate.AmountReleasedBonus + AccountTranHourlyAggregate.AmountPlayableBonus
                    else 0 
              end as Game_Bet,
              case 
                 when AccountTranHourlyAggregate.TranType = 'GAME_BET'
                    then AccountTranHourlyAggregate.AmountReal 
                    else 0 
              end as Game_Bet_RM,
              case 
                 when AccountTranHourlyAggregate.TranType = 'GAME_WIN' 
                    then AccountTranHourlyAggregate.AmountReal +
                         AccountTranHourlyAggregate.AmountReleasedBonus + 
                         AccountTranHourlyAggregate.AmountPlayableBonus 
                    else 0 
              end as Game_Win,
              case 
                 when AccountTranHourlyAggregate.TranType = 'GAME_WIN'
                    then AccountTranHourlyAggregate.AmountReal 
                    else 0 
              end as Game_Win_RM,
            CASE WHEN AccountTranHourlyAggregate.TranType='GAME_BET' THEN 
            RawLoyalty ELSE 0 END AS RawLoyalty
            FROM admin_all.AccountTranHourlyAggregate 
            LEFT JOIN external_mpt.USER_CONF 
            ON AccountTranHourlyAggregate.PartyID =external_mpt.USER_CONF.PARTYID
            LEFT JOIN admin_all.VIP_STATUS 
            ON external_mpt.USER_CONF.VIP_STATUS=VIP_STATUS.id 
            WHERE AccountTranHourlyAggregate.Datetime BETWEEN '2019-12-01' AND '2019-12-12'
            ) a 
            GROUP BY a.USERID,a.PartyID,a.vip_status) results

这给出了正确的输出：

+---------+------------+---------+------------+-----------+----------+---------+---------+----------+-----------+-----------+------------+--------------+
| PartyID | ACCOUNT_ID | USERID  | vip_status | GB        | GW       | ggr     | ggr_RM  | raw_LP   | LP        | cash_LP   | LPper_full | LPper_ggr_RM |
+---------+------------+---------+------------+-----------+----------+---------+---------+----------+-----------+-----------+------------+--------------+
| 1820020 | 1000482    | UserXX1 | Silver     | -4500     | 3600     | 900     | 0,01    | 18000000 | 450       | 4,5       | 0,5        | 45000        |
+---------+------------+---------+------------+-----------+----------+---------+---------+----------+-----------+-----------+------------+--------------+
| 1820086 | 1000548    | UserXX2 | Bronze     | 0         | 0        | 0       | 0       | 0        | 0         | 0         | 0          | 0            |
+---------+------------+---------+------------+-----------+----------+---------+---------+----------+-----------+-----------+------------+--------------+
| 1819748 | 1000210    | UserXX3 | Bronze     | -26212,17 | 24082,17 | 2130    | 1600    | 30157812 | 753,9453  | 7,539453  | 0,3539     | 0,4712       |
+---------+------------+---------+------------+-----------+----------+---------+---------+----------+-----------+-----------+------------+--------------+
| 1820074 | 1000536    | UserXX4 | Bronze     | -21077    | 20477    | 600     | 300     | 40009580 | 1000,2395 | 10,002395 | 1,667      | 3,3341       |
+---------+------------+---------+------------+-----------+----------+---------+---------+----------+-----------+-----------+------------+--------------+
| 1819761 | 1000223    | UserXX5 | Silver     | -28102,5  | 21369,5  | 6733    | 6553    | 83769000 | 2094,225  | 20,94225  | 0,311      | 0,3195       |
+---------+------------+---------+------------+-----------+----------+---------+---------+----------+-----------+-----------+------------+--------------+
| 1820080 | 1000542    | UserXX6 | Bronze     | -295,8    | 26,32    | 269,48  | 90      | 329984   | 8,2496    | 0,082496  | 0,0306     | 0,0916       |
+---------+------------+---------+------------+-----------+----------+---------+---------+----------+-----------+-----------+------------+--------------+
| 1820014 | 1000476    | UserXX7 | Bronze     | -15347,14 | 13692,27 | 1654,87 | 1250,07 | 7697088  | 192,4272  | 1,924272  | 0,1162     | 0,1539       |
+---------+------------+---------+------------+-----------+----------+---------+---------+----------+-----------+-----------+------------+--------------+

然后我必须left join对此结果进行以下查询：

SELECT
    ID,
    ACCOUNT_ID,
    BALANCE_RAW_LOYALTY AS bal_lps_raw,
    BALANCE_RAW_LOYALTY / 40000 AS bal_lps,
    ROUND(BALANCE_RAW_LOYALTY / 40000 / 100, 2) AS bal_cash_lps
FROM 
    admin_all.ACCOUNT_TRAN_ALL
WHERE 
    ID = (SELECT MAX(ID) FROM admin_all.ACCOUNT_TRAN_ALL 
          WHERE ACCOUNT_ID = 1000210);

知道WHERE ACCOUNT_ID = 1000210应该将此查询中的 ACCOUNT_ID 与“结果”中的 ACCOUNT_ID 匹配。

我尝试了几种选择，但始终无法使其正常工作。

提前感谢您的建议。

标签： sql-serverleft-join

Answer 1

;WITH results
     AS (
     SELECT *
         FROM
         (
             SELECT a.PartyID, 
                    a.PartyID - 819538 AS ACCOUNT_ID, 
                    a.USERID, 
                    a.vip_status, 
                    SUM(a.Game_Bet) AS GB, 
                    SUM(a.Game_Win) AS GW, 
                    -SUM(a.Game_Bet) - SUM(a.Game_Win) AS ggr, 
                    -SUM(a.Game_Bet_RM) - SUM(a.Game_Win_RM) AS ggr_RM, 
                    SUM(a.RawLoyalty) AS raw_LP, 
                    SUM(a.RawLoyalty) / 40000 AS LP, 
                    SUM(a.RawLoyalty) / 40000 / 100 AS cash_LP,
                    CASE
                        WHEN(-SUM(a.Game_Bet) - SUM(a.Game_Win)) = 0
                        THEN 0
                        ELSE(SUM(a.RawLoyalty) / 40000 / 100) / (-SUM(a.Game_Bet) - SUM(a.Game_Win)) * 100
                    END AS LPper_full,
                    CASE
                        WHEN(-SUM(a.Game_Bet_RM) - SUM(a.Game_Win_RM)) = 0
                        THEN 0
                        ELSE(SUM(a.RawLoyalty) / 40000 / 100) / (-SUM(a.Game_Bet_RM) - SUM(a.Game_Win_RM)) * 100
                    END AS LPper_ggr_RM
             FROM
             (
                 SELECT AccountTranHourlyAggregate.Datetime, 
                        AccountTranHourlyAggregate.PartyID, 
                        external_mpt.USER_CONF.USERID, 
                        VIP_STATUS.name AS vip_status, 
                        AccountTranHourlyAggregate.Currency,
                        CASE
                            WHEN AccountTranHourlyAggregate.TranType = 'GAME_BET'
                            THEN AccountTranHourlyAggregate.AmountReal + AccountTranHourlyAggregate.AmountReleasedBonus + AccountTranHourlyAggregate.AmountPlayableBonus
                            ELSE 0
                        END AS Game_Bet,
                        CASE
                            WHEN AccountTranHourlyAggregate.TranType = 'GAME_BET'
                            THEN AccountTranHourlyAggregate.AmountReal
                            ELSE 0
                        END AS Game_Bet_RM,
                        CASE
                            WHEN AccountTranHourlyAggregate.TranType = 'GAME_WIN'
                            THEN AccountTranHourlyAggregate.AmountReal + AccountTranHourlyAggregate.AmountReleasedBonus + AccountTranHourlyAggregate.AmountPlayableBonus
                            ELSE 0
                        END AS Game_Win,
                        CASE
                            WHEN AccountTranHourlyAggregate.TranType = 'GAME_WIN'
                            THEN AccountTranHourlyAggregate.AmountReal
                            ELSE 0
                        END AS Game_Win_RM,
                        CASE
                            WHEN AccountTranHourlyAggregate.TranType = 'GAME_BET'
                            THEN RawLoyalty
                            ELSE 0
                        END AS RawLoyalty
                 FROM admin_all.AccountTranHourlyAggregate
                      LEFT JOIN external_mpt.USER_CONF ON AccountTranHourlyAggregate.PartyID = external_mpt.USER_CONF.PARTYID
                      LEFT JOIN admin_all.VIP_STATUS ON external_mpt.USER_CONF.VIP_STATUS = VIP_STATUS.id
                 WHERE AccountTranHourlyAggregate.Datetime BETWEEN '2019-12-01' AND '2019-12-12'
             ) a
             GROUP BY a.USERID, 
                      a.PartyID, 
                      a.vip_status
         ) results
        )

     SELECT main.ID, 
            main.ACCOUNT_ID, 
            main.BALANCE_RAW_LOYALTY AS bal_lps_raw, 
            main.BALANCE_RAW_LOYALTY / 40000 AS bal_lps, 
            main.ROUND(BALANCE_RAW_LOYALTY / 40000 / 100, 2) AS bal_cash_lps
results.vip_status
-- add more columns from results here

     FROM admin_all.ACCOUNT_TRAN_ALL main
          LEFT JOIN results ON results.ACCOUNT_ID = main.ACCOUNT_ID