具有来自其他表的迭代次数的SQL排序表

Question

我有银行表和付款表，如下所示：

Bank Table:
BankID/Name
1/bank1Name
2/bank2Name
3/bank3Name
4/bank4Name

Payment Table:
PaymentID/ProductID/BankID
1/100/2
2/102/2
3/98/3
4/100/2
5/102/1

如它所示，银行id 2,3,1的迭代次数分别为3,1,1。我想对银行表进行排序，并在表银行上进行最多的迭代。其结果如下：

2/bank2Name
3/bank3Name
1/bank1Name
4/bank4Name 

Answer 1

不确定大多数迭代意味着什么，但如果它是最有效的，则如下所示：

select b.bankid, b.name 
from bank b
join payment p
   on b.bankid = p.bankid
group by b.bankid, b.name
order by count(1) desc

另一种选择是由一个子选择来订购。

select b.bankid, b.name  
from bank b 
order by (select count(1) from payment p where b.bankid = p.bankid) desc

Answer 2

这是一个完整的工作示例，在这个示例中，我猜测每个bank ids在Payment表中没有记录显示在按其id排序的末尾：

DECLARE @Bank TABLE
(
     [ID] TINYINT
    ,[Name] NVARCHAR(32)
)

INSERT INTO @Bank ([ID], [Name])
VALUES (1,'bank1Name')
      ,(2,'bank2Name')
      ,(3,'bank3Name')
      ,(4,'bank4Name')
      ,(5,'bank5Name')

DECLARE @Payment TABLE
(
     [PaymentID] TINYINT
    ,[ProductID] TINYINT
    ,[BankID] TINYINT
)

INSERT INTO @Payment ([PaymentID], [ProductID], [BankID])
VALUES (1,100,2)
      ,(2,102,2)
      ,(3,98,3)
      ,(4,100,2)
      ,(5,102,1)


;WITH HelpValue AS
(
    SELECT COUNT([PaymentID]) AS [RoCount]
    FROM @Payment
)
SELECT B.[ID]
      ,B.[Name]
FROM @Bank B
OUTER APPLY
(
    SELECT TOP 1 P.[PaymentID]
    FROM @Payment P
    WHERE B.[ID] = P.[BankID]
) DS
CROSS APPLY HelpValue
ORDER BY COALESCE(DS.[PaymentID], [RoCount] + B.[ID])