按存储过程参数排序 [英] Order by stored procedure parameter

问题描述

我在通过用户给定的参数对存储过程进行排序时遇到了问题，我尝试阅读，但是尝试过的解决方案不起作用.

I have a problem ordering my stored procedure by a parameter given by a user, I have tried reading but the solutions I have tried won't work.

那么，有人可以帮助我吗?我希望可以从Group_concat中按类型对它进行排序.如果我以Harcoded形式输入它(例如，ORDER BY Type1，ORDER BY Type2等)，它将起作用.但是我想要这样的东西(ORDER BY @specificStat).

So, is there somebody who can help me? I would like it to be sorted by Type from my Group_concat. It works if I type it in harcoded, like (ORDER BY Type1, ORDER BY Type2 and so on). But I would like something like this (ORDER BY @specificStat).

CREATE PROCEDURE getSpecificStatsBySeason(IN khID INT(11), IN seasonName varchar(40), IN specificStat INT(11))
BEGIN

SET @sql = NULL;
SET @khID = khID;
SET @seasonName = seasonName;
SET @specificStat = specificStat;

SELECT
  GROUP_CONCAT(DISTINCT CONCAT('SUM(IF(em.eventTypeID = ',
      et.eventTypeID, ', 1, 0)) AS Type',
      et.eventTypeID )
  ) INTO @sql
FROM  eventTypes       AS et, `eventType-R-kh` AS eRk, kh AS kh WHERE et.eventTypeID = eRk.eventTypeID AND eRk.kh = kh.kh AND kh.khID = @khID;

SET @sql = CONCAT('SELECT u.userID, ud.firstname, ud.lastname,', @sql,
                  ',
                  IFNULL((SELECT COUNT(s.activityID)
                          FROM activity AS a1, signup AS s
                          WHERE u.userid = s.userid
                          AND s.activityID = a1.activityID
                          AND a1.khID = @khID
                            AND s.answer = 1
                            AND se.seasonName = @seasonName
          AND a1.activityDate BETWEEN se.seasonStart AND se.seasonEnd
                  AND a1.activityDate <= NOW()), 0) AS matchCount
                  FROM userData AS ud, userInfo AS u
                  LEFT JOIN activity      AS  a ON a.khID = @khID
                  RIGHT JOIN season AS se ON se.seasonName = @seasonName
          AND a.activityDate BETWEEN se.seasonStart AND se.seasonEnd
                  LEFT JOIN `eventMatch`       AS em ON em.userid = u.userid
                  AND em.activityID = a.activityID
                  LEFT JOIN activityMatch AS am ON a.activityID = am.activityID 
                  WHERE u.khID  = @khID
                  AND u.userID = ud.userID
                  GROUP BY u.UserID  
                  ORDER BY  @specificStat  ;');


prepare stmt 
FROM @sql;

execute stmt;

推荐答案

请改用此:

...
...
  AND u.userID = ud.userID
              GROUP BY u.UserID  
              ORDER BY ',   @specificStat, '  ;');

这篇关于按存储过程参数排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！