从SQL中的每列中的每个列获取已排序的数据

问题描述

我有一个数据集，其中我希望SQL中每列的顶部最小值，但它根据排序顺序得到排序行。

例如：



我的尝试：

I have a dataset in which i want the minimum value on the top from each column in SQL, but it got sorted rows based on the sorted order.
FOR EXAMPLE:

What I have tried:

SELECT ExtractProfile
         , CountryCode
--       , TEST.STARTTS AS STARTTS
         , SUM(CASE WHEN TEST.STARTTS = CONVERT(DATE,GETDATE()) THEN TEST.StoreSuccess  ELSE 0 END) Sucesses0
         , SUM(CASE WHEN TEST.STARTTS = CONVERT(DATE,GETDATE()) THEN 1 ELSE 0 END) Stores0
         , SUM(CASE WHEN TEST.STARTTS = Dateadd(dd,-1,CONVERT(DATE,GETDATE())) THEN TEST.StoreSuccess  ELSE 0 END) Sucesses1
         , SUM(CASE WHEN TEST.STARTTS = Dateadd(dd,-1,CONVERT(DATE,GETDATE())) THEN 1                                ELSE 0 END) Stores1
         , SUM(CASE WHEN TEST.STARTTS = Dateadd(dd,-2,CONVERT(DATE,GETDATE())) THEN TEST.StoreSuccess  ELSE 0 END) Sucesses2
         , SUM(CASE WHEN TEST.STARTTS = Dateadd(dd,-2,CONVERT(DATE,GETDATE())) THEN 1                                ELSE 0 END) Stores2
        
--INTO #TEMP
FROM   (SELECT       d.ExtractProfile
                       , s.StoreNbr
                       , s.CountryCode
                       , CONVERT(DATE,d.StartTS) AS STARTTS
                       --, SUM(CASE WHEN s.Result  = 'Success' THEN 1 ELSE 0 END) Successes
                       --, SUM(CASE WHEN s.Result != 'Success' THEN 1 ELSE 0 END) Failures
                       --, COUNT(DISTINCT StoreNbr) Stores
                       , MAX(CASE WHEN s.Result  = 'Success' THEN 1 ELSE 0 END) AS StoreSuccess
              FROM  TEST s (NOLOCK)
              INNER JOIN TEST1 d (NOLOCK)
                           ON s.ExtractId = d.Id
              WHERE d.StartTS > CONVERT(VARCHAR(11),GETDATE() - 25)
       --and s.storenbr=1000
       --and d.rundate = dateadd(dd,-1,convert(varchar(11),getdate()))
              GROUP BY d.ExtractProfile
                       , s.StoreNbr
                       , s.CountryCode
                       , CONVERT(DATE,d.StartTS)
              ) AS TEST2
GROUP BY ExtractProfile
         , CountryCode
ORDER BY Sucesses0 ASC ,Sucesses1 ASC,Sucesses2 ASC,Sucesses3 ASC

推荐答案

SELECT a.ID, b.Dimension 
FROM (SELECT ROW_NUMBER() OVER(ORDER BY ID) AS No, Id FROM TableA) a
JOIN (SELECT ROW_NUMBER() OVER(ORDER BY Dimension) AS no, Dimension FROM TableA) b 
     ON a.No = b.No

这篇关于从SQL中的每列中的每个列获取已排序的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！