SQL 查询排序

2015-11-19 02:47:35 156 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

SQL基础查询与排序本笔记为阿里云天池龙珠计划SQL训练营的学习内容，链接为：https://tianchi.aliyun.com/specials/promotion/aicampsql 一、查询 1.准备数据 1.简单查询 select <列名> from <表名 > SELECT查询不会对数据库中的数据进行修改.只是一种显示数据的方式 1)查询product中所有的数据 select * from product; 这儿的 * 表示查询表中所有的... 阅读详情

declare @sql varchar(max)

declare @w varchar(max) 

 set @w = '

		with tt as (select ta.*, tb.name bname, tc.name cname from [dbo].[inf_customer] as ta

		join [dbo].[inf_custstatus] as tb on ta.statusid=tb.id

		join [dbo].[inf_company] as tc on ta.companyid=tc.id

		)'

set @sql = @w + 'select cname ,count(*) as count,convert(VARCHAR(10),updatetime,120) as time' 

		;with tt as (select ta.*, tb.name bname, tc.name cname from [dbo].[inf_customer] as ta

		join [dbo].[inf_custstatus] as tb on ta.statusid=tb.id

		join [dbo].[inf_company] as tc on ta.companyid=tc.id

		)

select @sql = @sql + ',SUM(case bname when '''+Convert(varchar(100),bname)+''' then 1 else 0 end) ['+Convert(varchar(100),bname)+']'

		from (select distinct top 1000 bname from tt order by 1) as a

select @sql = @sql+' from tt  group by cname,convert(VARCHAR(10),updatetime,120) order by convert(VARCHAR(10),updatetime,120) DESC'		

exec(@sql)

请问怎么排序查询出来的结果

...全文

SQL查询so easy！轻松学会排序查询和分页查询可以每页只查询10条，20条或者某个具体数量的数据，当用户点击下一页的时候再查询后面的10条，20条。排序就是让查询结果根据某个字段进行按照升序或者降序排列，后面ASC是升序排序，在后面跟DESC关键字是降序排序。分页查询有两个参数第一个是起始查询条数，MySQL默认是从第0条开始计数的，第二个参数则是每页查询条数，例如：每页想要显示10条数据那么第二个参数就写10以此类推。上面不加分页一共是16条数据，如果在企业真实情况下数据量可能是几十万，几百万甚至更多的数据。查询teacher教师表中姓王的讲师。阅读详情

百里清尘

疑难问题 22,298 成员 121,727 内容

+ 关注 + 加入社区

切换为时间正序

Yole 2015-11-19

打赏
举报


/*
    功能：生成测试数据.
*/
 
create table Test_paging(
    id int identity(1,1) not null primary key,
    TestNumber int not null,
    TestName varchar(20) not null,
    TestDept varchar(10) not null,
    TestDate datetime not null
)
go
 
with tep(Number,Name,Dept,Date) as
(
    select 1,cast('0_testname' as varchar(20)),cast('0_DBA' as varchar(10)),getdate()
    union all
    select Number+1,cast(cast(Number as varchar(20))+'_testname' as varchar(20)),cast(cast(Number/500 as varchar(10))+'_DBA' as varchar(10)) ,getdate()
    from tep
    where Number<=20000000
)
insert into Test_paging(TestNumber,TestName,TestDept,TestDate)
select Number,Name,Dept,Date from tep option(maxrecursion 0)
 
--添加索引（我有测试没有索引的情况，2012的优势更加明显，但是我们的数据库不可能不建索引的，故可忽略没有索引的情况）
create nonclustered index IX_TestDept on Test_paging(
    TestDept
) include
(
    TestName,TestDate
) 
go




/*
    功能：测试2012版本中offset/fetch分页.
*/
 
dbcc dropcleanbuffers
dbcc freeproccache
 
set statistics io on
set statistics time on
set statistics profile on
 
declare   
    @page int,    --第@page页
    @size int,    --每页@size行
    @total int    --总行数
 
select @page=20,@size=10,@total=count(1) from Test_paging where TestDept = '1000_DBA' 
 
select 
    TestName,TestDept,TestDate,@total
from 
    Test_paging
where 
    TestDept = '1000_DBA' 
order by id offset (@page-1)*@size rows fetch next @size rows only
 
set statistics io off
set statistics time off
set statistics profile off




/*
    功能：测试2005/2008版本中row_number分页.
*/
 
dbcc dropcleanbuffers
dbcc freeproccache
 
set statistics io on
set statistics time on
set statistics profile on
 
declare   
    @page int,    --第@page页
    @size int,    --每页@size行
    @total int
 
select @page=20,@size=10,@total=count(1) from Test_paging where TestDept = '1000_DBA'
 
select TestName,TestDept,TestDate,@total from
(
    select 
        TestName,TestDept,TestDate,row_number() over(order by ID) as num 
    from 
        Test_paging
    where 
        TestDept = '1000_DBA'
) test where num between (@page-1)*@size+1 and @page*@size order by num 
 
set statistics io off
set statistics time off
set statistics profile off




/*
    功能：测试2000版本中top分页.
*/
 
dbcc dropcleanbuffers
dbcc freeproccache
 
set statistics io on
set statistics time on
set statistics profile on
 
declare   
    @page int,    --第@page页
    @size int,    --每页@size行
    @total int    --总行数
 
select @page=20,@size=10,@total=count(1) from Test_paging where TestDept = '1000_DBA' 
 
 
select TestName,TestDept,TestDate,@total from
(
    select top(@size) id,TestName,TestDept,TestDate from 
    (
        select top(@page*@size) id,TestName,TestDept,TestDate
        from Test_paging 
        where TestDept = '1000_DBA'
        order by id
    )temp1 order by id desc
)temp2 order by id 
 
set statistics io off
set statistics time off
set statistics profile off

这是几种分页形式，根据sql版本来使用。

百里清尘 2015-11-19

打赏
举报

引用 1 楼 u010192842 的回复:

order by convert(VARCHAR(10),updatetime,120) DESC
这里不已然进行排序了么？您还要怎么排？

我发错了我是问怎么分页

Yole 2015-11-19

打赏
举报


order by convert(VARCHAR(10),updatetime,120) DESC

这里不已然进行排序了么？您还要怎么排？

SQL 查询 排序

相关推荐

SQL 查询排序