SQL查询 - 显示主账户和所有二级账户

问题描述：

我知道我可以通过将“从账户”和“账户”存储到变量或其他东西来完成某种循环，但我在寻找更简单的方法。SQL查询 - 显示主账户和所有二级账户

MainAccount表显然包含1个帐号，它也存储在AccountsInterval表中。在该表中，有一个范围（从帐户到帐户）。如果没有循环，我很难找到一种方法来获取每个主要帐户的所有辅助帐户。有更容易的方法吗？

CREATE TABLE #MainAccounts(id INT IDENTITY(1,1) PRIMARY KEY, MainAccount NVARCHAR(20)) 
    INSERT INTO #MainAccounts(MainAccount) VALUES('41000') 
    INSERT INTO #MainAccounts(MainAccount) VALUES('41010') 
    INSERT INTO #MainAccounts(MainAccount) VALUES('41011') 
    INSERT INTO #MainAccounts(MainAccount) VALUES('41999') 
    INSERT INTO #MainAccounts(MainAccount) VALUES('42000') 
    INSERT INTO #MainAccounts(MainAccount) VALUES('42010') 
    INSERT INTO #MainAccounts(MainAccount) VALUES('42015') 
    INSERT INTO #MainAccounts(MainAccount) VALUES('42020') 
    INSERT INTO #MainAccounts(MainAccount) VALUES('42030') 
    INSERT INTO #MainAccounts(MainAccount) VALUES('42080') 
    INSERT INTO #MainAccounts(MainAccount) VALUES('42310') 
    INSERT INTO #MainAccounts(MainAccount) VALUES('42999') 
    INSERT INTO #MainAccounts(MainAccount) VALUES('43999') 
    INSERT INTO #MainAccounts(MainAccount) VALUES('48000') 
    INSERT INTO #MainAccounts(MainAccount) VALUES('48100') 
    INSERT INTO #MainAccounts(MainAccount) VALUES('48199') 
    INSERT INTO #MainAccounts(MainAccount) VALUES('48200') 
    INSERT INTO #MainAccounts(MainAccount) VALUES('48210') 
    INSERT INTO #MainAccounts(MainAccount) VALUES('48220') 
    INSERT INTO #MainAccounts(MainAccount) VALUES('48299') 
    INSERT INTO #MainAccounts(MainAccount) VALUES('48999') 
    INSERT INTO #MainAccounts(MainAccount) VALUES('49999') 

CREATE TABLE #AccountsInterval(id INT IDENTITY(1,1) PRIMARY KEY, MainAccount NVARCHAR(20), FromAccount NVARCHAR(20), ToAccount NVARCHAR(20)) 
INSERT INTO #AccountsInterval(MainAccount, FromAccount, ToAccount) VALUES('41999', '41000', '41999') 
INSERT INTO #AccountsInterval(MainAccount, FromAccount, ToAccount) VALUES('42999', '42000', '42999') 
INSERT INTO #AccountsInterval(MainAccount, FromAccount, ToAccount) VALUES('43999', '41000', '43999') 
INSERT INTO #AccountsInterval(MainAccount, FromAccount, ToAccount) VALUES('48199', '48000', '48199') 
INSERT INTO #AccountsInterval(MainAccount, FromAccount, ToAccount) VALUES('48299', '48200', '48299') 
INSERT INTO #AccountsInterval(MainAccount, FromAccount, ToAccount) VALUES('48999', '48000', '48999') 
INSERT INTO #AccountsInterval(MainAccount, FromAccount, ToAccount) VALUES('49999', '41000', '49999')

如果我们使用示例帐户; 41999，42999，43999 ...我们应该得到下面的结果。

Main Secondary 
41999 41000 
41999 41010 
41999 41011 
41999 41999 
42999 42000 
42999 42010 
42999 42015 
42999 42020 
42999 42030 
42999 42080 
42999 42310 
42999 42999 
43999 41000 
43999 41010 
43999 41011 
43999 41999 
43999 42000 
43999 42010 
43999 42015 
43999 42020 
43999 42030 
43999 42080 
43999 42310 
43999 42999 
43999 43999

我试过多个查询，子查询，我没有得到任何地方。

答

和

select ai.MainAccount as "Main", mi.MainAccount as "Secondary" 
from #AccountsInterval ai 
join #MainAccounts mi on mi.MainAccount >= ai.FromAccount and mi.MainAccount <= ai.ToAccount

...或...或者

select ai.MainAccount as "Main", mi.MainAccount as "Secondary" 
from #AccountsInterval ai 
cross join #MainAccounts mi 
where mi.MainAccount >= ai.FromAccount and mi.MainAccount <= ai.ToAccount

感谢。长周五。交叉加入。不错。 – manderson

答

这应该做你在一个查询想要的东西：

Select M.MainAccount As Main, S.MainAccount As Secondary 
From #MainAccounts  M 
Join #AccountsInterval I On M.MainAccount = I.MainAccount 
Join #MainAccounts  S On Convert(Int, S.MainAccount) Between Convert(Int, I.FromAccount) 
                   And  Convert(Int, I.ToAccount) 
Order By Main, Secondary

继的问题你的例子中，我们可以将结果限制为仅41999，42999，并43999：

Select M.MainAccount As Main, S.MainAccount As Secondary 
From #MainAccounts  M 
Join #AccountsInterval I On M.MainAccount = I.MainAccount 
Join #MainAccounts  S On Convert(Int, S.MainAccount) Between Convert(Int, I.FromAccount) 
                   And  Convert(Int, I.ToAccount) 
Where M.MainAccount In ('41999', '42999', '43999') 
Order By Main, Secondary

Main Secondary 
41999 41000 
41999 41010 
41999 41011 
41999 41999 
42999 42000 
42999 42010 
42999 42015 
42999 42020 
42999 42030 
42999 42080 
42999 42310 
42999 42999 
43999 41000 
43999 41010 
43999 41011 
43999 41999 
43999 42000 
43999 42010 
43999 42015 
43999 42020 
43999 42030 
43999 42080 
43999 42310 
43999 42999 
43999 43999

SQL查询 - 显示主账户和所有二级账户

相关推荐