SQLserver中取众位数的写法

最新推荐文章于 2022-01-21 11:37:29 发布

weixin_34128237

最新推荐文章于 2022-01-21 11:37:29 发布

阅读量137

点赞数

文章标签：数据库

原文链接：http://www.cnblogs.com/xianhan/p/9090297.html

版权

取众位数：先按个数倒排序，再取第一条
SELECT * FROM
(
select a.billid,a.DemandVoltage,count(1) as RecordCount,
Row_NUMBER() OVER(PARTITION BY billid ORDER BY count(1) DESC) AS SN
from ETL_SingleCharging201805 a with(nolock)
WHERE RowKey LIKE '00000058-bca0-4559-8f8f-2fc3b61eb01c%'
OR Rowkey LIKE '000001b7-3e26-47fd-9a67-b177750d5147%'
group by a.billid,a.DemandVoltage
) b
WHERE b.billid IN ('00000058-bca0-4559-8f8f-2fc3b61eb01c',
'000001b7-3e26-47fd-9a67-b177750d5147') AND b.SN=1

按个数排序：
select a.billid,a.DemandVoltage,count(1) as RecordCount, Row_NUMBER() OVER(PARTITION BY billid ORDER BY count(1) DESC) AS SN
from ETL_SingleCharging201805 a with(nolock)
WHERE RowKey LIKE '00000058-bca0-4559-8f8f-2fc3b61eb01c%' OR Rowkey LIKE '000001b7-3e26-47fd-9a67-b177750d5147%'
group by a.billid,a.DemandVoltage