mysql按问题排序

ymdaylpp 于 2021-06-25 发布在 Mysql

关注(0)|答案(2)|浏览(321)

我试图在一个sql语句中做太多的事情。我想在输出表的适当位置得到小计和总计。这是我的sql；

SELECT Date, DOW, Week, Year, logdate, Month, monum, netID, Logins, 
       creds, newb, netCnt, TOD, netCnt, activity
  FROM (SELECT logdate
              ,activity
              ,DATE( logdate )                      AS Date
              ,DAYOFWEEK( logdate )         AS DOW
              ,WEEK( logdate,0 )              AS Week
              ,YEAR( logdate )              AS Year
              ,DATE_FORMAT( logdate, '%M' )         AS Month
              ,DATE_FORMAT( logdate, '%m' )     AS monum
              ,CONVERT( netID,UNSIGNED INTEGER )    AS netID
              ,COUNT( callsign )                    AS Logins
              ,COUNT( IF(creds <> '',1,NULL) )  AS creds
              ,COUNT( IF(comments LIKE '%first log in%',1,NULL) ) AS newb
              ,count( DISTINCT netID )      AS netCnt
              ,SUM(  DISTINCT netID)            AS allCnt
              ,SEC_TO_TIME( SUM(timeonduty) )       AS TOD
         FROM NetLog
        WHERE netID <> 0 
          AND activity NOT LIKE '%TEST%'
          AND netcall LIKE '%W0KCN%'
          AND substr(logdate,1,4) = 2017
    GROUP BY Month, netID WITH ROLLUP ) AS t 
    ORDER BY t.logdate , logins

（缩写）输出如下所示；

你会注意到每个月的订单都是正确的，直到10月份。总数在10月前而不是12月底（12月后）排序。这是我可以在sql中控制的还是应该在php中修复？如何在sql中修复它？

mysql php sql-order-by

来源：https://stackoverflow.com/questions/48233444/mysql-order-by-issues

2条答案

按热度按时间

scyqe7ek1#

结果我需要一份案情陈述才能让这一切顺利进行。

SELECT Date, DOW, Week, Year,  Month, monum, netID, Logins, 
       creds, newb, netCnt, TOD, activity, logdate,
       COALESCE (Month, 'GT') as MonthNM
  FROM (SELECT logdate
              ,activity
              ,DATE( logdate )                      AS Date
              ,DAYOFWEEK( logdate )                 AS DOW
              ,WEEK( logdate,0 )                    AS Week
              ,YEAR( logdate )                      AS Year
              ,MONTHNAME ( logdate )                AS Month
              ,MONTH( logdate )                     AS monum
              ,CONVERT( netID,UNSIGNED INTEGER )    AS netID
              ,COUNT( callsign )                    AS Logins
              ,COUNT( IF(creds <> '',1,NULL) )      AS creds
              ,COUNT( IF(comments LIKE '%first log in%',1,NULL) ) AS newb
              ,count( DISTINCT netID )              AS netCnt
              ,SUM(  DISTINCT netID)                AS allCnt
              ,SEC_TO_TIME( SUM(timeonduty) )       AS TOD
         FROM NetLog
        WHERE netID <> 0 
          AND activity NOT LIKE '%TEST%'
          AND netcall LIKE '%W0KCN%'
          AND substr(logdate,1,4) = 2017
    GROUP BY Month, netID WITH ROLLUP ) AS t 
    ORDER BY 
     CASE
        WHEN MonthNM = 'GT' THEN  1
        ELSE 0
     END
        ,monum
        ,logins

赞(0）回复(0）举报 2021-06-25

z9smfwbn2#

你的第二个领域 logins 将仅在第一个排序列的相等值内进行相应排序。否则，排序将优先于第一列。尝试交换 logdate 以及 logins 看看发生了什么。

赞(0）回复(0）举报 2021-06-25