sql

SET @rownum := -1;
SELECT
AVG(t.mark)
FROM
(
SELECT
@rownum := @rownum + 1 AS rownum,
results.mark AS mark
FROM
results
ORDER BY results.mark
) AS t
WHERE
t.rownum IN (
CEIL(@rownum/2),
FLOOR(@rownum/2)
)
;

 第二种方式实验已经通过，mysql求中位数：

/*属性：parent_category_id表：help_category数据库：mysql版本：5.5.28SQLyog版本：v10.3*/
SELECT AVG(t1.parent_category_id) AS median_val FROM (
SELECT @rownum:=@rownum+1 AS `row_number`, d.parent_category_id
  FROM help_category d,  (SELECT @rownum:=0) r
  WHERE 1
  -- put some where clause here
  ORDER BY d.parent_category_id
) AS t1, 
(
  SELECT COUNT(*) AS total_rows
  FROM help_category d
  WHERE 1
  -- put same where clause here
) AS t2
WHERE 1
AND t1.row_number IN ( FLOOR((total_rows+1)/2), FLOOR((total_rows+2)/2) );

 第三种方式也通过，mysql求中位数：

SELECT AVG(middle_values) AS 'median' FROM (
  SELECT t1.parent_category_id AS 'middle_values' FROM
    (
      SELECT @row:=@row+1 AS `row`, x.parent_category_id
      FROM help_category AS X, (SELECT @row:=0) AS r
      WHERE 1
      -- put some where clause here
      ORDER BY x.parent_category_id
    ) AS t1,
    (
      SELECT COUNT(*) AS 'count'
      FROM help_category X
      WHERE 1
      -- put same where clause here
    ) AS t2
    -- the following condition will return 1 record for odd number sets, or 2 records for even number sets.
    WHERE t1.row >= t2.count/2 AND t1.row <= ((t2.count/2) +1)) AS t3;

 第四种方式也通过，mysql求中位数：

SELECT CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(
 GROUP_CONCAT(parent_category_id ORDER BY parent_category_id SEPARATOR ','),
  ',', 50/100 * COUNT(*) + 1), ',', -1) AS DECIMAL) AS `Median`
FROM help_category;

 第五种方式也通过，mysql求中位数：

SELECT
((SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(parent_category_id ORDER BY parent_category_id), ',', FLOOR(1+((COUNT(parent_category_id)-1) / 2))), ',', -1))
+
(SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(parent_category_id ORDER BY parent_category_id), ',', CEILING(1+((COUNT(parent_category_id)-1) / 2))), ',', -1)))/2
AS median
FROM help_category;

 第六种方式未验证，mysql求中位数存储过程：

DROP PROCEDURE IF EXISTS median;
DELIMITER //
CREATE PROCEDURE median (table_name VARCHAR(255), column_name VARCHAR(255), where_clause VARCHAR(255))
BEGIN
  -- Set default parameters
  IF where_clause IS NULL OR where_clause = '' THEN
    SET where_clause = 1;
  END IF;

  -- Prepare statement
  SET @sql = CONCAT(
    "SELECT AVG(middle_values) AS 'median' FROM (
      SELECT t1.", column_name, " AS 'middle_values' FROM
        (
          SELECT @row:=@row+1 as `row`, x.", column_name, "
          FROM ", table_name," AS x, (SELECT @row:=0) AS r
          WHERE ", where_clause, " ORDER BY x.", column_name, "
        ) AS t1,
        (
          SELECT COUNT(*) as 'count'
          FROM ", table_name, " x
          WHERE ", where_clause, "
        ) AS t2
        -- the following condition will return 1 record for odd number sets, or 2 records for even number sets.
        WHERE t1.row >= t2.count/2
          AND t1.row <= ((t2.count/2)+1)) AS t3
    ");

  -- Execute statement
  PREPARE stmt FROM @sql;
  EXECUTE stmt;
END//
DELIMITER ;


-- Sample usage:
-- median(table_name, column_name, where_condition);
CALL median('products', 'price', NULL);

 第七种方式已经验证通过，mysql求中位数：

SET @median_counter = (SELECT FLOOR(COUNT(*)/2) - 1 AS `median_counter` FROM `help_category`);

SET @median = CONCAT('SELECT `parent_category_id` FROM `help_category` ORDER BY `parent_category_id` LIMIT ', @median_counter, ', 1');

PREPARE median FROM @median;

EXECUTE median;

 第七种的第二个方式已经验证通过，mysql求中位数：

SET @median = CONCAT( 'SELECT `parent_category_id` FROM `help_category` ORDER BY `parent_category_id` LIMIT ',
                      (SELECT FLOOR(COUNT(*)/2) - 1 AS `median_counter` FROM `help_category`),
                      ', 1'
                    );

PREPARE median FROM @median;

EXECUTE median;