34,588
社区成员
发帖
与我相关
我的任务
分享探讨:这段SQL怎写才能达到最高效能? (各位高人,要练脑子的请进!)
example:
LOCN TOOL
----- -------
A 001
A 002
B 003
C 004
B 005
RESULT:
DOCN ROWN LOCN TOOL
---------------------
001 01 A 001
001 02 A 002
001 03 A NULL
001 04 A NULL
001 05 A NULL
002 01 A NULL
002 02 A NULL
002 03 A NULL
002 04 A NULL
002 05 A NULL
003 01 B 003
003 02 B 005
003 03 B NULL
003 04 B NULL
003 05 B NULL
004 01 C 004
004 02 C NULL
004 03 C NULL
004 04 C NULL
004 05 C NULL
005 01 C NULL
005 02 C NULL
005 03 C NULL
005 04 C NULL
005 05 C NULL
要求:按 LOCN 排序,每页 5行,不满5行的要加满一页,每LOCN后要再加完整一页,DOCN编号要前置零,依次递增,如上.
用SQL如何实现才能达到最高效能?
...全文
请发表友善的回复…
发表回复
tektite 2008-01-16
- 打赏
- 举报
嘿嘿,有办法了!
结贴!
肖祥清（呵呵，大人物！）的在第一时间写出来，有创意！10分
熊哥，全场积极跟进，思维活跃。35分
ORARichard，谢谢参与，5分
背着灵魂漫步，管它大妈小妈前妈后妈,能出货的就是好妈,那低级错误你已窥出来了,为啥不继续呢，不好意思，这回没你的分。
海爷及平友,没办法,那堆空记录一定得在表中生成,不是单纯的报表效果,为着那点微薄的人工,就得干这乱七八糟的活,苦笑!
这样分配没意见吧？
结贴!
肖祥清（呵呵，大人物！）的在第一时间写出来，有创意！10分
熊哥，全场积极跟进，思维活跃。35分
ORARichard，谢谢参与，5分
背着灵魂漫步，管它大妈小妈前妈后妈,能出货的就是好妈,那低级错误你已窥出来了,为啥不继续呢，不好意思，这回没你的分。
海爷及平友,没办法,那堆空记录一定得在表中生成,不是单纯的报表效果,为着那点微薄的人工,就得干这乱七八糟的活,苦笑!
这样分配没意见吧？
ORARichard 2008-01-16
- 打赏
- 举报
declare @t table(area varchar(3),locn varchar(2),tool varchar(4))
insert into @t(area,locn,tool)
select 'HK','C', '004' union all
select 'HK','C', '007' union all
select 'HK','G', '010' union all
select 'TW','B', '003' union all
select 'TW','B', '005' union all
select 'TW','D', '006' union all
select 'TW','F', '009' union all
select 'UK','A', '001' union all
select 'UK','A', '002' union all
select 'UK','E', '008';
with t(area,locn,tool,row,locn_row) as
(
select *,row_number() over( order by area,locn)-rank() over( order by area,locn)+1 locn_row
from
(
select * from
(
select * from @t union all
select area,locn,null tool
from
(
select distinct area,locn,null tool
,(row_number() over( order by area,locn)-rank() over(order by area,locn))/5 c
from @t
) t
) tt cross join (select 1 row union all select 2 union all select 3 union all select 4 union all select 5) s
) tt
)
select t.area+right(1000+dense_rank() over(order by t.area),3) docn,
right(100+row,2) rown,
t.locn,
s.tool
from t
left join (select tool,locn,row_number() over( order by area,locn)-rank() over( order by area,locn)+1 locn_row from @t) s
on t.locn=s.locn and t.locn_row=s.locn_row
dobear_0922 2008-01-15
- 打赏
- 举报
--结果表
create table bt(nId int identity(1,1), nType int, AREA varchar(8), LOCN varchar(8), TOOL varchar(8))
insert bt(nType, AREA, LOCN, TOOL) select nType=1, AREA, LOCN, TOOL from tb
union all select 2, AREA, LOCN, NULL from tb2 join Num on tb2.id>=Num.id
order by AREA, LOCN, nType, TOOL --加一个TOOL,确保最后一列也递增tektite 2008-01-15
- 打赏
- 举报
分地区逐个循环效率高，还是一次把全部地区完成效率高？
---------
29楼是一次全部完成的
---------
强！ 学习ing!
---------
29楼是一次全部完成的
---------
强！ 学习ing!
dobear_0922 2008-01-15
- 打赏
- 举报
也是插入时同步更新的，，，
tektite 2008-01-15
- 打赏
- 举报
增加一个有列排序分组,好像一定得回到SQL2005之前的方法来进行排号。
2005的 ntile 对不均等分组是不支持的，郁闷！
2005的 ntile 对不均等分组是不支持的，郁闷！
dobear_0922 2008-01-15
- 打赏
- 举报
分地区逐个循环效率高，还是一次把全部地区完成效率高？
---------
29楼是一次全部完成的
---------
29楼是一次全部完成的
dobear_0922 2008-01-15
- 打赏
- 举报
--创建测试表
create table tb(AREA varchar(8), LOCN varchar(8), TOOL varchar(8))
insert tb select 'HK', 'C', 004
union all select 'HK', 'C', 007
union all select 'HK', 'G', 010
union all select 'TW', 'B', 003
union all select 'TW', 'B', 005
union all select 'TW', 'D', 006
union all select 'TW', 'F', 009
union all select 'UK', 'A', 001
union all select 'UK', 'A', 002
union all select 'UK', 'E', 008
go
--创建辅助表
create table Num(Id int)
insert Num select 1 union all select 2 union all select 3 union all select 4
union all select 5 union all select 6 union all select 7 union all select 8
union all select 9
go
--辅助表
create table tb2(AREA varchar(8), LOCN varchar(8), id int)
insert tb2 select AREA, LOCN, 10+count(1)/5*5-count(1) from tb group by AREA, LOCN
go
--结果表
create table bt(nId int identity(1,1), nType int, AREA varchar(8), LOCN varchar(8), TOOL varchar(8))
insert bt(nType, AREA, LOCN, TOOL) select nType=1, AREA, LOCN, TOOL from tb
union all select 2, AREA, LOCN, NULL from tb2 join Num on tb2.id>=Num.id
order by AREA, LOCN, nType
--查询结果
select DOCN=AREA+right((select 1000+(count(1)+4)/5 from bt t where AREA=bt.AREA and nId<=bt.nId),3)
,ROWN='0'+rtrim((nId-1)%5+1)
,LOCN,TOOL
from bt
order by DOCN,ROWN
drop table tb,Num,tb2,bt
/*
DOCN ROWN LOCN TOOL
-------------- ------------- -------- --------
HK001 01 C 4
HK001 02 C 7
HK001 03 C NULL
HK001 04 C NULL
HK001 05 C NULL
HK002 01 C NULL
HK002 02 C NULL
HK002 03 C NULL
HK002 04 C NULL
HK002 05 C NULL
HK003 01 G 10
HK003 02 G NULL
HK003 03 G NULL
HK003 04 G NULL
HK003 05 G NULL
HK004 01 G NULL
HK004 02 G NULL
HK004 03 G NULL
HK004 04 G NULL
HK004 05 G NULL
TW001 01 B 3
TW001 02 B 5
TW001 03 B NULL
TW001 04 B NULL
TW001 05 B NULL
TW002 01 B NULL
TW002 02 B NULL
TW002 03 B NULL
TW002 04 B NULL
TW002 05 B NULL
TW003 01 D 6
TW003 02 D NULL
TW003 03 D NULL
TW003 04 D NULL
TW003 05 D NULL
TW004 01 D NULL
TW004 02 D NULL
TW004 03 D NULL
TW004 04 D NULL
TW004 05 D NULL
TW005 01 F 9
TW005 02 F NULL
TW005 03 F NULL
TW005 04 F NULL
TW005 05 F NULL
TW006 01 F NULL
TW006 02 F NULL
TW006 03 F NULL
TW006 04 F NULL
TW006 05 F NULL
UK001 01 A 1
UK001 02 A 2
UK001 03 A NULL
UK001 04 A NULL
UK001 05 A NULL
UK002 01 A NULL
UK002 02 A NULL
UK002 03 A NULL
UK002 04 A NULL
UK002 05 A NULL
UK003 01 E 8
UK003 02 E NULL
UK003 03 E NULL
UK003 04 E NULL
UK003 05 E NULL
UK004 01 E NULL
UK004 02 E NULL
UK004 03 E NULL
UK004 04 E NULL
UK004 05 E NULL
(70 row(s) affected)
*/tektite 2008-01-15
- 打赏
- 举报
27楼的例子，docn,rown 能做到加插时同步更新吗？
分地区逐个循环效率高，还是一次把全部地区完成效率高？
分地区逐个循环效率高，还是一次把全部地区完成效率高？
tektite 2008-01-15
- 打赏
- 举报
谢谢关注，请继续支持。
再来点难度,多增加一个排序列又如何？
例子：
area locn tool
---- ---- ----
HK C 004
HK C 007
HK G 010
TW B 003
TW B 005
TW D 006
TW F 009
UK A 001
UK A 002
UK E 008
返回结果:
docn rown locn tool
-------- ---- ---- ----
HK001 01 C 004
HK001 02 C 007
HK001 03 C NULL
HK001 04 C NULL
HK001 05 C NULL
HK002 01 C NULL
HK002 02 C NULL
HK002 03 C NULL
HK002 04 C NULL
HK002 05 C NULL
HK003 01 G 010
HK003 02 G NULL
HK003 03 G NULL
HK003 04 G NULL
HK003 05 G NULL
HK004 01 G NULL
HK004 02 G NULL
HK004 03 G NULL
HK004 04 G NULL
HK004 05 G NULL
TW001 01 B 003
TW001 02 B 005
TW001 03 B NULL
TW001 04 B NULL
TW001 05 B NULL
TW002 01 B NULL
TW002 02 B NULL
TW002 03 B NULL
TW002 04 B NULL
TW002 05 B NULL
TW003 01 D 006
TW003 02 D NULL
TW003 03 D NULL
TW003 04 D NULL
TW003 05 D NULL
TW004 01 D NULL
TW004 02 D NULL
TW004 03 D NULL
TW004 04 D NULL
TW004 05 D NULL
TW005 01 F 009
TW005 02 F NULL
TW005 03 F NULL
TW005 04 F NULL
TW005 05 F NULL
TW006 01 F NULL
TW006 02 F NULL
TW006 03 F NULL
TW006 04 F NULL
TW006 05 F NULL
UK001 01 A 001
UK001 02 A 002
UK001 03 A NULL
UK001 04 A NULL
UK001 05 A NULL
UK002 01 A NULL
UK002 02 A NULL
UK002 03 A NULL
UK002 04 A NULL
UK002 05 A NULL
UK003 01 E 008
UK003 02 E NULL
UK003 03 E NULL
UK003 04 E NULL
UK003 05 E NULL
UK004 01 E NULL
UK004 02 E NULL
UK004 03 E NULL
UK004 04 E NULL
UK004 05 E NULLdobear_0922 2008-01-14
- 打赏
- 举报
--动态的：
--创建测试表
create table tb(LOCN varchar(8), TOOL varchar(8))
insert tb select 'A' , '001'
union all select 'A' , '002'
union all select 'B' , '003'
union all select 'C' , '004'
union all select 'B', '005'
go
--创建辅助表
create table Num(Id int)
create table tb2(LOCN varchar(8), id int)
--结果表
create table bt(nId int identity(1,1), nType int, LOCN varchar(8), TOOL varchar(8))
declare @id int
, @nPageSize int --页大小
, @nBlankPage int --填充空白页数
select @nPageSize=7, @nBlankPage=2, @id=1
while @id<@nPageSize*(@nBlankPage+1)
begin
insert Num select @id
set @id=@id+1
end
insert tb2 select LOCN, @nPageSize*(@nBlankPage+1)-count(1)%@nPageSize
from tb group by LOCN
--插入结果
insert bt(nType, LOCN, TOOL) select nType=1, LOCN, TOOL from tb
union all select 2, LOCN, NULL from tb2 join Num on tb2.id>=Num.id
order by LOCN, nType
--查询结果
select DOCN=right('00'+rtrim((nId+@nPageSize-1)/@nPageSize),3)
,ROWN=right('0'+rtrim((nId-1)%@nPageSize+1),2)
,LOCN,TOOL
from bt
order by DOCN,ROWN
/*
DOCN ROWN LOCN TOOL
------ ---- -------- --------
001 01 A 001
001 02 A 002
001 03 A NULL
001 04 A NULL
001 05 A NULL
001 06 A NULL
001 07 A NULL
002 01 A NULL
002 02 A NULL
002 03 A NULL
002 04 A NULL
002 05 A NULL
002 06 A NULL
002 07 A NULL
003 01 A NULL
003 02 A NULL
003 03 A NULL
003 04 A NULL
003 05 A NULL
003 06 A NULL
003 07 A NULL
004 01 B 003
004 02 B 005
004 03 B NULL
004 04 B NULL
004 05 B NULL
004 06 B NULL
004 07 B NULL
005 01 B NULL
005 02 B NULL
005 03 B NULL
005 04 B NULL
005 05 B NULL
005 06 B NULL
005 07 B NULL
006 01 B NULL
006 02 B NULL
006 03 B NULL
006 04 B NULL
006 05 B NULL
006 06 B NULL
006 07 B NULL
007 01 C 004
007 02 C NULL
007 03 C NULL
007 04 C NULL
007 05 C NULL
007 06 C NULL
007 07 C NULL
008 01 C NULL
008 02 C NULL
008 03 C NULL
008 04 C NULL
008 05 C NULL
008 06 C NULL
008 07 C NULL
009 01 C NULL
009 02 C NULL
009 03 C NULL
009 04 C NULL
009 05 C NULL
009 06 C NULL
009 07 C NULL
(63 row(s) affected)
*/
drop table tb,Num,tb2,btdobear_0922 2008-01-14
- 打赏
- 举报
直接用identity列，更加暴力，呵呵
--创建测试表
create table tb(LOCN varchar(8), TOOL varchar(8))
insert tb select 'A' , '001'
union all select 'A' , '002'
union all select 'B' , '003'
union all select 'C' , '004'
union all select 'B', '005'
go
--创建辅助表
create table Num(Id int)
insert Num select 1 union all select 2 union all select 3 union all select 4
union all select 5 union all select 6 union all select 7 union all select 8
union all select 9
go
--辅助表
create table tb2(LOCN varchar(8), id int)
insert tb2 select LOCN, 10+count(1)/5*5-count(1) from tb group by LOCN
go
--结果表
create table bt(nId int identity(1,1), nType int, LOCN varchar(8), TOOL varchar(8))
insert bt(nType, LOCN, TOOL) select nType=1, LOCN, TOOL from tb
union all select 2, LOCN, NULL from tb2 join Num on tb2.id>=Num.id
order by LOCN, nType
--查询结果
select DOCN=right('00'+rtrim((nId+4)/5),3)
,ROWN='0'+rtrim((nId-1)%5+1)
,LOCN,TOOL
from bt
order by DOCN,ROWN
drop table tb,Num,tb2,bt
/*
DOCN ROWN LOCN TOOL
------ ------------- -------- --------
001 01 A 001
001 02 A 002
001 03 A NULL
001 04 A NULL
001 05 A NULL
002 01 A NULL
002 02 A NULL
002 03 A NULL
002 04 A NULL
002 05 A NULL
003 01 B 003
003 02 B 005
003 03 B NULL
003 04 B NULL
003 05 B NULL
004 01 B NULL
004 02 B NULL
004 03 B NULL
004 04 B NULL
004 05 B NULL
005 01 C 004
005 02 C NULL
005 03 C NULL
005 04 C NULL
005 05 C NULL
006 01 C NULL
006 02 C NULL
006 03 C NULL
006 04 C NULL
006 05 C NULL
(30 row(s) affected)
*/dobear_0922 2008-01-14
- 打赏
- 举报
select docn='0'+ltrim((ntile(@ttl_rows/5) over(order by locn,isnull(tool,2)))),
rown='00'+ltrim(((row_number() over(partition by locn order by isnull(tool,2))-1)%5+1)) ,
locn,tool
into #table2
from
(
select locn,tool from #table
union all
select locn=a.locn,tool=null
from #tmp a join (select top(@n) row_number() over(order by name) as id from syscolumns) b
on a.new_add>=b.id
)
c order by locn,isnull(tool,2)
很好，很强大，，，
rown='00'+ltrim(((row_number() over(partition by locn order by isnull(tool,2))-1)%5+1)) ,
locn,tool
into #table2
from
(
select locn,tool from #table
union all
select locn=a.locn,tool=null
from #tmp a join (select top(@n) row_number() over(order by name) as id from syscolumns) b
on a.new_add>=b.id
)
c order by locn,isnull(tool,2)
很好，很强大，，，
tektite 2008-01-14
- 打赏
- 举报
--楼上的，我要告你抄袭，呵呵，，，
——————————————————
熊哥，我是向你学习呀！请受徒儿一拜！师父在上，请多多指教。
docn,rown--如果能在加插时同步更新会更快的,能做到吗？
——————————————————
熊哥，我是向你学习呀！请受徒儿一拜！师父在上，请多多指教。
create table #table(locn varchar(1), tool varchar(3))
insert #table
select 'A','001' union all
select 'A','002' union all
select 'B','003' union all
select 'C','004' union all
select 'B','005'
GO
select locn,(5-count(locn)%5+5) as new_add into #tmp
from #table group by locn
GO
declare @N int
set @N=(select max(new_add) from #tmp)
select rown=identity(int,1,1),locn,tool
into #table2
from
(
select locn,tool from #table
union all
select locn=a.locn,tool=null
from #tmp a join (select top(@N) row_number() over(order by name) as id from syscolumns) b
on a.new_add>=b.id
)
c order by locn,isnull(sign(tool),2), tool
select docn='00'+rtrim((rown-1)/5+1)
,rown='0'+rtrim((rown-1)%5+1)
,locn,tool from #table2 order by locndocn,rown--如果能在加插时同步更新会更快的,能做到吗？
dobear_0922 2008-01-14
- 打赏
- 举报
不敢当，共同学习，，，
tektite 2008-01-14
- 打赏
- 举报
create table #table(locn varchar(1), tool varchar(3))
insert #table
select 'A','001' union all
select 'A','002' union all
select 'B','003' union all
select 'C','004' union all
select 'B','005'
GO
select locn,(5-count(locn)%5+5) as new_add into #tmp
from #table group by locn
GO
declare @n int
set @n=(select max(new_add) from #tmp)
DECLARE @ttl_rows int
SET @ttl_rows=((SELECT COUNT(1) FROM #TABLE)+(SELECT SUM(NEW_ADD) FROM #TMP))
select docn='0'+ltrim((ntile(@ttl_rows/5) over(order by locn,isnull(tool,2)))),
rown='00'+ltrim(((row_number() over(partition by locn order by isnull(tool,2))-1)%5+1)) ,
locn,tool
into #table2
from
(
select locn,tool from #table
union all
select locn=a.locn,tool=null
from #tmp a join (select top(@n) row_number() over(order by name) as id from syscolumns) b
on a.new_add>=b.id
)
c order by locn,isnull(tool,2)
select * from #table2
drop table #table
drop table #table2
drop table #tmp
凌雯 2008-01-14
- 打赏
- 举报
学习
ORARichard 2008-01-14
- 打赏
- 举报
感觉是能覆盖大部分情况了,代码没做过多优化. 如果用2005应该更简单,且高效.
ORARichard 2008-01-14
- 打赏
- 举报
declare @t table(locn varchar(2),tool varchar(4))
insert into @t(locn,tool)
select 'a','001' union all
select 'a','002' union all
select 'a','003' union all
select 'a','004' union all
select 'a','005' union all
select 'b','006' union all
select 'a','007'
SELECT row=IDENTITY(INT,1,1)
,num=count(0)
,locn
INTO #1
FROM @t
GROUP BY locn
declare @i varchar(10)
set @i=0
select @i=@i+(ceiling(num/5.0)+1)*5 from #1
create table #(row int)
insert into # exec('select top '+@i+' identity(int,1,1) row into # from syscolumns
select * from #')
SELECT row=(
SELECT COUNT(0) AS row
FROM @t
WHERE (locn = t.locn) AND (tool <= t.tool)
)
,locn
,tool
INTO #2
FROM @t AS t
SELECT right(1000+t.row,3) docn, right(100+(t.row-1)%5+1,2) rown, t.locn, #2.tool
FROM
(
SELECT #.row,s.locn,s.starts-1 starts
FROM # inner JOIN
(
select a.locn,
(select isnull(sum((ceiling(num/5.0)+1)*5),0)+1 from #1 where row<a.row) starts,
(select sum((ceiling(num/5.0)+1)*5) from #1 where row<=a.row) ends
from #1 a
) s on #.row between s.starts and s.ends
) AS t LEFT OUTER JOIN #2 ON #2.row+t.starts = t.row AND t.locn = #2.locn
drop table #1
drop table #2
drop table #
/*
docn rown locn tool
---- ---- ---- ----
001 01 a 001
002 02 a 002
003 03 a 003
004 04 a 004
005 05 a 005
006 01 a 007
007 02 a NULL
008 03 a NULL
009 04 a NULL
010 05 a NULL
011 01 a NULL
012 02 a NULL
013 03 a NULL
014 04 a NULL
015 05 a NULL
016 01 b 006
017 02 b NULL
018 03 b NULL
019 04 b NULL
020 05 b NULL
021 01 b NULL
022 02 b NULL
023 03 b NULL
024 04 b NULL
025 05 b NULL
*/
dobear_0922 2008-01-13
- 打赏
- 举报
--楼上的，我要告你抄袭，呵呵，，，
--14楼的代码可以优化一下
insert tb2 select LOCN, 10+count(1)/5*5-count(1) from tb group by LOCN
------------>
insert tb2 select LOCN, 10-count(1)%5 from tb group by LOCN
--15楼的也一样
insert tb2 select LOCN, @nPageSize*(@nBlankPage+1)+count(1)/@nPageSize*@nPageSize-count(1)
from tb group by LOCN
-------------->
insert tb2 select LOCN, @nPageSize*(@nBlankPage+1)+count(1)%@nPageSize
from tb group by LOCN加载更多回复（16）
