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语句分组获取记录的第一条数据的方法

使用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进行排序、分组、统计的10个新技巧分享

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

以数据库字段分组显示数据的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    *FROM    (        SELECT            userid,            classid,            remark,            ROW_NUMBER () OVER (                PARTITION BY userid,                classid            ORDER BY                addtime DESC        

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 分组查询问题

情景一: 表中数据 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

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语句

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 SERVER 分组求和sql语句

需求:如下图所示 实现sql语句 SELECT A1,SUM(A2*A3) FROM A GROUP BY A1 大家可以自行测试一下,主要需要了解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

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获取分组排序后数据的脚本

先上一段代码,如下所示: 复制代码 代码如下: 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把纵向表转为横向表,并分别分组求平均和总平均值

效果如图所示:测试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更新两个表并可更新对应的字段值具体实现

ACCESS 例子: 复制代码 代码如下: insert into products (ProNumber,CASNumber,Cnname,Price,Enname,Baozhuang,Pinpai) select ProNumber,CASNumber,Cnname,Price,Enname,Baozhuang,Pinpai from product22 1.用一句sql从一个表里面取出数据导入另外一个表里面.这个对不同程序之间的数据转换很有用处,而且速度快,数据完整: insert int

SQL优化之针对count、表的连接顺序、条件顺序、in及exist的优化

本文详述了SQL优化中针对count.表的连接顺序.条件顺序.in及exist的优化,非常具有实用价值!详述如下: 一.关于count 看过一些网上关于count(*)和count(列)的文章,count(列)的效率一定比count(*)高吗? 其实个人觉得count(*)和count(列)根本就没有可比性,count(*)统计的是表里面的总条数,而count(列)统计的是当列的非空记录条数. 不过我们可以通过实验来比较一下: 首先创建测试表: drop table test purge; cr

SQL Server--怎样用ADO在SQL SERVER中建库,建表

通过ADO可以访问SQL SERVER,并执行相应的SQL语句建库.建表,下面是SQL SERVER BOOKS ONLINE中的相关定义. 建表: CREATE TABLE   [       database_name.[owner].       | owner.   ] table_name   (       {               | column_name AS computed_column_expression } [,...n]   )   [ON {filegrou

Mysql跨表更新 多表update sql语句总结

假定我们有两张表,一张表为Product表存放产品信息,其中有产品价格列Price:另外一张表是ProductPrice表,我们要将ProductPrice表中的价格字段Price更新为Price表中价格字段的80%. 在Mysql中我们有几种手段可以做到这一点,一种是update table1 t1, table2 ts ...的方式: 复制代码 代码如下: UPDATE product p, productPrice pp SET pp.price = pp.price * 0.8 WHER

SQL判断语句用法和多表查询

1.格式化时间sql语句 本例中本人随便做了两张表,和实际不是很相符,只是想说明sql语句的写法. 例1表格式如下: 需求:查询出本表,但需要使time字段的时间格式为yyyy-MM-dd,比如:2013-08-13 sql写法: SELECT u.id,u.userId,u.timeType,DATE_FORMAT(time,'%Y-%m-%d') AS time,secondId FROM `user` u 运行结果: 2.多表查询(三表查询) 例二三表结构如下: 需求:查询出主表,要求在主

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脚本来删除表,以及删除表中的数据. 删除表和删除表数据这是

sql语句查询数据库中的表名/列名/主键/自动增长值实例

sql语句查询数据库中的表名/列名/主键/自动增长值 ----查询数据库中用户创建的表 ----jsj01 为数据库名 select name tablename from jsj01..sysobjects where type='U' and name not in ('dtproperties') --查询表里的字段信息 ---docs为表名 ---- select * from syscolumns where id = object_id('docs') ----查询数据库中所有类型

必须会的SQL语句(二) 创建表、修改表结构、删除表

1.创建数据库表 --使用哪个数据库,如果不写这一句是默认的数据库,也可以用鼠标选当前数据库 use testDB --创建表 Create Table tablename ( --id表示字段名 --int 数据类型 --primary key 主键 --not null 非空 --identity(1,1)初始值是1 每次自增长1 id int primary key not null identity(1,1), --unique 唯一 name varchar(20) not null

sql server建库、建表、建约束技巧

下面给大家分享下sql server建库.建表.建约束技巧,下文介绍有文字有代码. --创建School数据库之前:首先判断数据库是否存在,若存在则删除后再创建,若不存在则创建-- --exists关键字:括号里边能查询到数据则返回'true' 否则返回'false' if exists(select * from sysdatabases where name = 'School') --exists返回'true'则执行删除数据库操作-- drop database School --exi