根据独立表中的列名对表进行排序

Question

我有两个独立的表，一个是事务表，另一个是要生成报告列表的表。包含报表的表具有要满足的条件和排序顺序。

Transaction
Emp_ID  Amount  Date
1       100     2020-01-01
2       90      2020-01-04
3       85      2020-01-02
4       40      2020-01-07
5       60      2020-01-08

ReportMaster
ReportID    Sorting     Asc_Desc
1           Amount      Desc
2           Date        Asc
3           Amount      Asc

最终报告将显示工会中每个报告的前2名结果。

ReportID    Emp_ID  Amount  Date        Row_Number
1           1       100     2020-01-01  1
1           2       90      2020-01-04  2
2           1       100     2020-01-01  1
2           3       85      2020-01-02  2
3           4       40      2020-01-07  1
3           5       60      2020-01-08  2

我的逻辑已经到了死胡同。使用了一个带有"排序"值的order子句，它只返回Amount/Document，而不是相应列中的值。请记住，实际的数据集包含20ish列和50-60个报告，因此按顺序编写案例语句有点乏味。

是否需要递归语句？或者有其他方法可以实现这一点吗？

Answer 1

尝试下面的sql：

DECLARE @sql NVARCHAR(MAX) = ''
IF OBJECT_ID('tempdb..#ReportTypes') IS NOT NULL DROP TABLE #ReportTypes
IF OBJECT_ID('tempdb..#Results') IS NOT NULL DROP TABLE #Results
CREATE TABLE #Results (
ReportID INT,
Emp_ID INT,
Amount DECIMAL(18, 2),
Date DATETIME,
RowNumber INT
)
SELECT ReportId, Sorting, Asc_Desc
INTO #ReportTypes
FROM ReportMaster
WHILE (SELECT COUNT(*) FROM #ReportTypes) > 0
BEGIN
DECLARE @ReportId INT, @Sorting VARCHAR(50), @Asc_Desc VARCHAR(50)
SELECT TOP 1 @ReportID = ReportId, @Sorting = Sorting, @Asc_Desc = Asc_Desc FROM #ReportTypes
SET @sql = @sql + ' INSERT INTO #Results ' 
+ ' SELECT ' + CAST(@ReportId AS VARCHAR) + ' ,Emp_ID, Amount, Date, ROW_NUMBER() OVER(ORDER BY ' + @Sorting + ' ' + @Asc_Desc + ') AS RowNumber FROM [Transactions];'
DELETE FROM #ReportTypes WHERE ReportId = @ReportId 
END
EXECUTE sp_executesql @sql
SELECT ReportID, Emp_ID, Amount, Date, RowNumber AS [Row_Number]
FROM #Results
WHERE RowNumber <= 2
ORDER BY ReportID, RowNumber