请帮忙纠正下，筛选出最大数量的数据，且时间最新

一速微光 2017-12-20 04:54:30

ctime value
2017-09-11 09:34:00 23
2017-09-11 09:34:00 220
2017-09-11 09:34:00 109
2017-09-11 09:36:00 28.9
2017-09-11 09:36:00 21.6
2017-09-11 09:36:00 53
2017-09-11 09:36:00 47
2017-09-11 09:36:00 123
2017-09-11 09:36:00 1870
2017-09-11 09:39:00 248
2017-09-11 09:39:00 23
2017-09-11 09:39:00 63
2017-09-11 09:39:00 61
2017-09-11 09:39:00 44
2017-09-11 09:39:00 27
以上是原始数据，需求是把记录数最多，如果记录数相同就要最新时间的记录选出来。

以下是我的实现SQL,总感觉有点奇怪。
SELECT MAX(CTIME) FROM
(SELECT CTIME,count(1) OVER(PARTITION BY CTIME ORDER BY CTIME) AS ROWNUMS FROM B
WHERE ROWNUMS =
(SELECT MAX(ROWNUMS)
FROM (SELECT
CTIME,
COUNT(*) AS ROWNUMS
FROM B
GROUP BY CTIME) A);
请给看下还有什么更好的办法吗？

...全文

193 4 打赏收藏转发到动态举报

写回复

用AI写文章

4 条回复

切换为时间正序

请发表友善的回复…

发表回复

snlixing 2017-12-21

打赏
举报

select top 1 * from (select COUNT(1) as cn,MAX([ctime]) as ct from #tab group by [ctime]) as t order by cn desc,ct desc

二月十六 2017-12-20

打赏
举报

是这个意思吗？借雨停数据

use Tempdb

go

--> --> 听雨停了-->测试数据

  

if not object_id(N'Tempdb..#tab') is null

    drop table #tab

Go

Create table #tab([ctime] Datetime,[value] decimal(18,7))

Insert #tab

select '2017-09-11 09:34:00',23 union all

select '2017-09-11 09:34:00',220 union all

select '2017-09-11 09:34:00',109 union all

select '2017-09-11 09:36:00',28.9 union all

select '2017-09-11 09:36:00',21.6 union all

select '2017-09-11 09:36:00',53 union all

select '2017-09-11 09:36:00',47 union all

select '2017-09-11 09:36:00',123 union all

select '2017-09-11 09:36:00',1870 union all

select '2017-09-11 09:39:00',248 union all

select '2017-09-11 09:39:00',23 union all

select '2017-09-11 09:39:00',63 union all

select '2017-09-11 09:39:00',61 union all

select '2017-09-11 09:39:00',44 union all

select '2017-09-11 09:39:00',27

--测试数据结束

SELECT  MAX(t.ctime) AS ctime ,

        t.grn

FROM    ( SELECT    ctime ,

                    COUNT(1) AS grn

          FROM      #tab

          GROUP BY  ctime

        ) t

GROUP BY t.grn;

听雨停了 2017-12-20

打赏
举报

你的sql改成下面这样也可以得，你那样写有问题，查不出来的


SELECT MAX(CTIME)
FROM   (
           SELECT CTIME,
                  COUNT(1) AS ROWNUMS
           FROM   #tab
           GROUP BY ctime
           having  COUNT(1) = (
                      SELECT MAX(ROWNUMS)
                      FROM   (
                                 SELECT CTIME,
                                        COUNT(*) AS ROWNUMS
                                 FROM   #tab
                                 GROUP BY
                                        CTIME
                             ) A
           )
) a

你要只查询时间的话，下面这样更简单


 SELECT TOP 1 ctime FROM #tab
    GROUP BY ctime
    ORDER BY COUNT(1) desc,ctime DESC

听雨停了 2017-12-20

打赏
举报



use Tempdb

go

--> --> 听雨停了-->测试数据

 

if not object_id(N'Tempdb..#tab') is null

	drop table #tab

Go

Create table #tab([ctime] Datetime,[value] decimal(18,7))

Insert #tab

select '2017-09-11 09:34:00',23 union all

select '2017-09-11 09:34:00',220 union all

select '2017-09-11 09:34:00',109 union all

select '2017-09-11 09:36:00',28.9 union all

select '2017-09-11 09:36:00',21.6 union all

select '2017-09-11 09:36:00',53 union all

select '2017-09-11 09:36:00',47 union all

select '2017-09-11 09:36:00',123 union all

select '2017-09-11 09:36:00',1870 union all

select '2017-09-11 09:39:00',248 union all

select '2017-09-11 09:39:00',23 union all

select '2017-09-11 09:39:00',63 union all

select '2017-09-11 09:39:00',61 union all

select '2017-09-11 09:39:00',44 union all

select '2017-09-11 09:39:00',27

--测试数据结束





SELECT * FROM #tab WHERE ctime=(

	SELECT TOP 1 ctime FROM #tab

	GROUP BY ctime

	ORDER BY COUNT(1) desc,ctime DESC

)