sql - TSL/SQL PERCENTILE_CONT 应返回 1 条记录

Question

我正在处理一个 T-SQL 查询(我正在运行一个 SQL Server 数据库)，该查询应该根据值列表计算中位数。查询如下所示:

SELECT 
   PERCENTILE_CONT(0.5) OVER (ORDER BY age) as Age 
FROM 
   peopleDB 
WHERE 
   <expressions>;

数据库有 100 行。查询返回中位数(相同的值)100 次，但我的目标是只返回一次。我一直在尝试以多种方式使用 DISTINCT 和 GROUP BY 但每次都返回语法错误。数据库目前不支持 DISTINCT。

我是否有可能忽略了一些明显的东西？

Answer 1

我从未听说过 TSL。但是，以下方法之一可能会起作用:

SELECT PERCENTILE_CONT(0.5) OVER (ORDER BY age) as Age
FROM peopleDB
WHERE <expressions>
limit 1;

SELECT top 1 PERCENTILE_CONT(0.5) OVER (ORDER BY age) as Age
FROM peopleDB
WHERE <expressions>;

SELECT max(PERCENTILE_CONT(0.5) OVER (ORDER BY age)) as Age
FROM peopleDB
WHERE <expressions>;

select max(age)
from (SELECT top 1 PERCENTILE_CONT(0.5) OVER (ORDER BY age) as Age
      FROM peopleDB
      WHERE <expressions>
     ) t