当条件与没有链接的另一个表匹配时，如何从一个SQL表返回所有行

Question

有两张桌子。

表1:

bankid     region   country continent   area
-----------------------------------------------
1101       Puglia   Italy   Europe      South West
1222       Atos     France  Europe      South West
2222       Atos     Japan   Asia        East
0101       Momba    Gana    Africa      South East
...        ...       ...     ...        ...

表2:

user        level   geography   function
-------------------------------------------
anthony     Global  World        Buy
anothony    Global  World        Sell
smith       bankid  2222         Buy
smith       bankid  2222         Sell  

规则：

在区域下有国家，在国家之下有区域，在每个区域下有银行。因此，查询的预期结果是

当T2.level='Global‘时，从T1返回所有区域、国家、区域、BANKID和T2中的所有函数

当=‘’时，我要做的是返回表1中的所有行。然而，预期的格式如下：

user        level   geography   function
-------------------------------------------
anthony     bankid  1101         Buy
anthony     bankid  1222         Buy
anthony     bankid  2222         Buy
anthony     bankid  0101         Buy
anthony     region  Puglia       Buy
anthony     region  Atos         Buy
anthony     region  Momba        Buy
anthony     country Italy        Buy
anthony     country France       Buy
anthony     country Japan        Buy
anthony     country Gana         Buy
anthony     are     South West   Buy
anthony     are     East         Buy
anthony     are     South East   Buy
smith       bankid  ....         Sell
smnith      region  ....         Sell
smith       country ....         Sell
smith       area    ....         Sell

问题:当两个表之间没有链接时，如何查询结果？

试过：

    SELECT ua.username, ua.[function], ua.level, ua.geography 
FROM Table2 ua INNER JOIN 
(SELECT bankid, region, country, area, 'Global' as Global FROM Table1) as  c
                ON ua.geography=CASE WHEN ua.level='Area' THEN c.area 
                                              WHEN ua.level='Country' THEN c.country 
                                              WHEN ua.level='Region' THEN c.region
                                              WHEN ua.level='bankid' THEN c.bankid 

                                        END

没有运气。

Answer 1

您可以使用以下查询获得所需的结果：

--Create Table1
CREATE TABLE [dbo].[Table1](
    [bankid] [int] NULL,
    [region] [nvarchar](50) NULL,
    [country] [nvarchar](50) NULL,
    [continent] [nvarchar](50) NULL,
    [area] [nvarchar](50) NULL
)
GO
--Create Table2
CREATE TABLE [Table2](
    [username] [nvarchar](50) NULL,
    [level] [nvarchar](50) NULL,
    [geography] [nvarchar](50) NULL,
    [function] [nvarchar](50) NULL
)
GO
--Insert sample records in Table1
INSERT [Table1] ([bankid], [region], [country], [continent], [area]) 
VALUES  (1101, N'Puglia', N'Italy', N'Europe', N'South West'),
        (1222, N'Atos', N'France', N'Europe', N'South West'),
        (2222, N'Atos', N'Japan', N'Asia', N'East'),
        (101, N'Momba', N'Gana', N'Africa', N'South East')
GO
--Insert sample records in Table2
INSERT [Table2] ([username], [level], [geography], [function]) 
VALUES (N'anthony', N'Global', N'World', N'Buy'),
       (N'anothony', N'Global', N'World', N'Sell'),
       (N'smith', N'bankid', N'2222', N'Buy'),
       (N'smith', N'bankid', N'2222', N'Sell')


--Query
WITH    CTE
          AS ( SELECT DISTINCT 'bankid' level ,CAST(bankid AS NVARCHAR(50)) geography FROM Table1
               UNION ALL
               SELECT DISTINCT 'region' level ,region geography FROM Table1
               UNION ALL
               SELECT DISTINCT 'country' level ,country geography FROM Table1
               UNION ALL
               SELECT DISTINCT 'area' level , area geography FROM Table1
             )
    SELECT  t2.username ,
            IIF(t2.level = 'Global', CTE.level , CTE2.level) level,
            IIF(t2.level = 'Global',CTE.geography ,CTE2.geography) geography,
            t2.[function]
    FROM    Table2 t2
            LEFT JOIN CTE ON t2.level = 'Global'
            LEFT JOIN ( SELECT DISTINCT
                                level ,
                                '....' geography
                        FROM    CTE
                      ) CTE2 ON t2.level <> 'Global';