SQL中Group分组获取Top N方法实现可首选row_number

有产品表,包含id,name,city,addtime四个字段,因报表需要按城市分组,统计每个城市的最新10个产品,便向该表中插入了100万数据,做了如下系列测试:


代码如下:

CREATE TABLE [dbo].[products](
[id] [int] IDENTITY(1,1) NOT NULL,
[name] [nvarchar](50) NULL,
[addtime] [datetime] NULL,
[city] [nvarchar](10) NULL,
CONSTRAINT [PK_products] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

1、采用row_number方法,执行5次,平均下来8秒左右,速度最快。


代码如下:

select no, id,name,city
from (select no =row_number() over (partition by city order by addtime desc), * from products)t
where no< 11 order by city asc,addtime desc

2、采用cross apply方法,执行了3次,基本都在3分5秒以上,已经很慢了。


代码如下:

select distinct b.id,b.name,b.city from products a
cross apply (select top 10 * from products where city = a.city order by addtime desc) b

3、采用Count查询,只执行了两次,第一次执行到5分钟时,取消任务执行了;第二次执行到13分钟时,没有hold住又直接停止了,实在无法忍受。


代码如下:

select id,name,city from products a
where ( select count(city) from products where a.city = city and addtime>a.addtime) < 10
order by city asc,addtime desc

4、采用游标方法,这个最后测试的,执行了5次,每次都是10秒完成,感觉还不错。


代码如下:

declare @city nvarchar(10)
create table #Top(id int,name nvarchar(50),city nvarchar(10),addtime datetime)
declare mycursor cursor for
select distinct city from products order by city asc
open mycursor
fetch next from mycursor into @city
while @@fetch_status =0
begin
insert into #Top
select top 10 id,name,city,addtime from products where city = @city
fetch next from mycursor into @city
end
close mycursor
deallocate mycursor
Select * from #Top order by city asc,addtime desc
drop table #Top

通过上述对比不难发现,在面临Group获取Top N场景时,可以首选row_number,游标cursor其次,另外两个就基本不考虑了,数据量大的时候根本没法使用。

时间: 2013-03-09

Sql Server 分组统计并合计总数及WITH ROLLUP应用

WITH ROLLUP 在生成包含小计和合计的报表时,ROLLUP 运算符很有用.ROLLUP 运算符生成的结果集类似于 CUBE 运算符所生成的结果集. 复制代码 代码如下: SELECT [Source], COUNT(*) AS OrderTotal FROM [ExternalOrder] Where OrderStatus=1 AND (CheckPayment=1 ) and TicketDate >= '2012-11-1' AND TicketDate < '2012-12-1

sql 分组查询问题

情景一: 表中数据 name score aaa 11 aaa 19 bbb 12 bbb 18 ccc 19 ddd 21 期望查询结果如下 name score aaa 30 bbb 30 ccc 19 ddd 21 复制代码 代码如下: ---检查表是否存在 if exists(select * from sysobjects where name='testSum') drop table testSum go ---创建表 create table testSum ( tid int

sqlserver巧用row_number和partition by分组取top数据

分组取TOP数据是T-SQL中的常用查询, 如学生信息管理系统中取出每个学科前3名的学生.这种查询在SQL Server 2005之前,写起来很繁琐,需要用到临时表关联查询才能取到.SQL Server 2005后之后,引入了row_number()函数,row_number()函数的分组排序功能使这种操作变得非常简单.下面是一个简单示例: 复制代码 代码如下: --1.创建测试表 create table #score ( name varchar(20), subject varchar(2

SQL进行排序、分组、统计的10个新技巧分享

1.使用排序使数据有序通常,你的所有数据真正需要的仅仅是按某种顺序排列.SQL的ORDER BY语句可以以字母或数字顺序组织数据.因此,相似的值按组排序在一起.然而,这个分组时排序的结果,并不是真的分组.ORDER BY显示每条记录而分组可能代表很多记录.2.进行分组除去重复值 排序和分组之间的最大区别是:排序的数据显示所有记录(在限定标准范围之内),而分组数据不是显示所有记录.GROUP BY语句对于同样的值只显示一条记录.例如,下面的语句中的GROUP BY语句对数据源中重复出现的数据只返回

分组后分组合计以及总计SQL语句(稍微整理了一下)

今天看到了这个文章感觉内容挺多的,就是比较乱,实在不好整理,我们小编就简单整理了一下,希望大家能凑合看吧 分组后分组合计以及总计SQL语句   1)想一次性得到分组合计以及总计,sql: SELECT 分组字段 FROM 表 GROUP BY 分组字段 compute sum(COUNT(*)) 2)分组合计1: SELECT COUNT(*) FROM (SELECT 分组字段 FROM 表 GROUP BY 分组字段 )别名 3)分组合计2: SELECT COUNT(*) FROM (SE

SQL SERVER 分组求和sql语句

需求:如下图所示 实现sql语句 SELECT A1,SUM(A2*A3) FROM A GROUP BY A1 大家可以自行测试一下,主要需要了解group by语句的用法

SQL分组排序去重复的小实例

复制代码 代码如下: SELECT    *FROM    (        SELECT            userid,            classid,            remark,            ROW_NUMBER () OVER (                PARTITION BY userid,                classid            ORDER BY                addtime DESC        

以数据库字段分组显示数据的sql语句(详细介绍)

复制代码 代码如下: <?php$dsn = "mysql:host=localhost;dbname=group";$db = new PDO($dsn, 'root', '');$db->exec('set names utf8'); $sql = "select conf_id from  `group` group by conf_id";//$pre = $db->prepare($sql);//$result_arr = array()

sql获取分组排序后数据的脚本

先上一段代码,如下所示: 复制代码 代码如下: select distinct  b.OrgID,b.CompanyID,b.AreaID,b.CustChannelID,b.CustID,b.SaleTotalQty,GETDATE() from KDS400TelSurveyCustAll_Temp  as tmp1 cross apply (select top 10 percent * from KDS400TelSurveyCustAll_Temp tmp2 where tmp1.Or

SQLserver 实现分组统计查询(按月、小时分组)

设置AccessCount字段可以根据需求在特定的时间范围内如果是相同IP访问就在AccessCount上累加. 复制代码 代码如下: Create table Counter ( CounterID int identity(1,1) not null, IP varchar(20), AccessDateTime datetime, AccessCount int ) 该表在这儿只是演示使用,所以只提供了最基本的字段 现在往表中插入几条记录 insert into Counter selec

Sql Server:多行合并成一行,并做分组统计的两个方法

复制代码 代码如下: --创建 test 表 ,插入数据 CREATE TABLE test(code varchar(50), [values] varchar(10),[count] int)INSERT test SELECT '001', 'aa',1UNION ALL SELECT '001', 'bb',2UNION ALL SELECT '002', 'aaa',4UNION ALL SELECT '002', 'bbb',5UNION ALL SELECT '002', 'ccc

sql分组后二次汇总(处理表重复记录查询和删除)的实现方法

--处理表重复记录(查询和删除) /****************************************************************************************************************************************************** 1.Num.Name相同的重复值记录,没有大小关系只保留一条 2.Name相同,ID有大小关系时,保留大或小其中一个记录 整理人:中国风(Roy) 日期:2008

一句Sql把纵向表转为横向表,并分别分组求平均和总平均值

效果如图所示:测试sql语句如下: 复制代码 代码如下: declare @tab table(Class varchar(20),Student varchar(20),Course varchar(50),Quantity decimal(7,2)); insert into @tab(Class,Student,Course,Quantity) values('A班','张三','语文',60); insert into @tab(Class,Student,Course,Quantity

显示同一分组中的其他元素的sql语句

1.原始查询表结果  2.理想查询表结果 一很牛的朋友写的sql语句大笑,学习啦偷笑: select userpwd,username=stuff((select ','+username from tuser t1 where t1.userpwd=t.userpwd for xml path('')),1,1,'') from tuser t group by userpwd 说明: a.stuff方法(举例说明:select stuff('abcdef', 2, 3, 'klmn'); 在

SQL语句分组获取记录的第一条数据的方法

使用Northwind 数据库 首先查询Employees表 查询结果: city列里面只有5个城市 使用ROW_NUMBER() OVER(PARTITION BY COL1 ORDER BY COL2) 先进行分组 注:根据COL1分组,在分组内部根据 COL2排序,而此函数计算的值就表示每组内部排序后的顺序编号(组内连续的唯一的). sql语句为: select EmployeeID,LastName,FirstName,Title,TitleOfCourtesy,City,ROW_NUM

SQL语句去掉重复记录,获取重复记录

SQL语句去掉重复记录,获取重复记录  --查询一个表中有效去掉重复的记录,UserID为自增长主键,RoleID为重复字段 SELECT MIN(UserID) AS UserID, RoleID FROM tmpTable GROUP BY RoleID SELECT RoleID FROM tmpTable GROUP BY RoleID HAVING (COUNT(*) > 1) SELECT DISTINCT * FROM tmpTable sql语句查询 sql server acc

一个查询的SQL语句请教,希望能够用一条SQL语句得到结果

各位老大:  我有一个查询的SQL语句请教,希望能够用一条SQL语句得到结果,问题如下: 表X:      A           B           C       D      ------------------      北京   广州   1000     10      北京   深圳   1200     20      北京   上海   500       30      北京   天津   300       40      北京   河南   800       20

四种数据库随机获取10条数据的方法

四种数据库随机获取10条数据的方法 SQL Server: 复制代码 代码如下: SELECT TOP 10 * FROM T_USER ORDER BY NEWID() ORACLE: 复制代码 代码如下: SELECT * FROM (SELECT * FROM T_USER ORDER BY DBMS_RANDOM.RANDOM()) WHERE RONUM <= 10 MySQL: 复制代码 代码如下: SELECT * FROM T_USER  ORDER BY  RAND() LIM

jQuery实现获取table表格第一列值的方法

本文实例讲述了jQuery实现获取table表格第一列值的方法.分享给大家供大家参考,具体如下: 先来看看运行效果截图: 完整代码如下: <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd" > <html xmlns="http://www.w3.or

php+mysqli预处理技术实现添加、修改及删除多条数据的方法

本文实例讲述了php+mysqli预处理技术实现添加.修改及删除多条数据的方法.分享给大家供大家参考.具体分析如下: 首先来说说为什么要有预处理(预编译)技术?举个例子:假设要向数据库添加100个用户,按常规思路,就是向数据库发送100个执行请求,此时,按照 mysql 数据库的工作原理,它需要对每一条执行语句进行编译(这里就有100次).所以,这里的效率是非常低的. 预处理(预编译)技术的作用,就是减少编译的次数和时间,以提高效果.通过一个案例来说明,预处理(预编译)技术是如何做到的(好吧,先

SQL Server实现显示每个类别最新更新数据的方法

本文实例讲述了SQL Server实现显示每个类别最新更新数据的方法.分享给大家供大家参考,具体如下: 在项目中经常遇到求每个类别最新显示的数据,比如显示某某某类别最新更新的5条数据.特写下这个sql记录于此: -- ============================================= -- 作者: <Rising_Sun> -- 创建日期: <2012-9-28> -- 描述: <显示每个类别下最新更新的n条数据> -- ============

mysql实现合并同一ID对应多条数据的方法

本文实例讲述了mysql实现合并同一ID对应多条数据的方法.分享给大家供大家参考,具体如下: 如 : CREATE TABLE `c_classuser_tab` ( `Id` int(11) NOT NULL AUTO_INCREMENT, `Classid` int(11) DEFAULT NULL, `Username` varchar(100) DEFAULT NULL, `studentid` varchar(100) DEFAULT NULL, `College` varchar(1

sql 随机抽取几条数据的方法 推荐

传说用这个语句管用:select top 5 * from tablename order by newid() 我放到sql的查询分析器里去执行果然管用,随机抽取5条信息,不停的换,结果我应用到程序里的时候就不管用了,总是那几条,于是对这个东西进行了一个研究得出另一种方法: newid() 可以产生如:"49869293-8891-4B31-B88E-A584D9621490" 这样的数据结果,而且每一行产生的都不同,这样的话,我们就可以在查询的时候给结果集里加上这么一个列,比如:

SQL Server删除表及删除表中数据的方法

本文介绍SQL Server中如何删除表,如何删除表中的数据.在删除表数据时有delete和truncate两种方法,delete和truncate有什么区别呢? SQL Server,我们现在基本上使用的最古老的版本应该是SQL Server 2000吧,应该没有更早的版本了吧?!从SQL Server 2000开始,到SQL Server 2005,2008,2012等,T-SQL的处理能力越来越强.今天我们就来说说如何使用T-SQL脚本来删除表,以及删除表中的数据. 删除表和删除表数据这是