从SQL中的每列中的每个列获取已排序的数据 [英] Get the sorted data from each of the column in each column in 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

推荐答案

你不能这样做：SQL按标准对行进行排序，它不按列对数据进行排序，因为它不会将任何未排序的数据与其关联的数据保持在一起。



这是一个奇怪的请求，而不是SQL可以自动实现的一个 - 如果你真的必须这样做，那么用你的演示语言做这件事要好得多。虽然在SQL中有可能（通过SELECTign将每个列分成一个单独的结果，按顺序排列并使用ROWNUMBER;然后通过ROWNUMBER将各个表连接在一起），它很麻烦，可能会让用户感到困惑。



例如，如果你有一个包含两列ID和Deimension的表，你可以像这样单独排序：

You can't do that: SQL sorts rows by criteria, it doesn't sort data by columns, because that doesn't leave any unsorted data in place with it's associated data.

It's an odd request, and not one that SQL can automatically fulfil - you will be a lot better off doing this in your presentation language, if you really must do it. While it is possible in SQL (by SELECTign each column into a separate result, ordered and with a ROWNUMBER; then JOINING the various tables together by ROWNUMBER) it's messy, and probably rather confusing to users.

For example, if you have a table with two columns ID and Deimension, you can get both sorted individually like this:

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中的每列中的每个列获取已排序的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！