---------------------------------------------------------------- -- Author :fredrickhu(小F，向高手学习) -- Date :2011-09-27 14:12:08 -- Verstion: -- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86) -- Apr 22 2011 11:57:00 -- Copyright (c) Microsoft Corporation -- Enterprise Evaluation Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64) -- ---------------------------------------------------------------- --> 测试数据：[tb] if object_id('[tb]') is not null drop table [tb] go create table [tb]([col] int) insert [tb] select 1001 union all select 100101 union all select 1201 union all select 120101 union all select 120102 union all select 12010201 union all select 1300 union all select 1400 union all select 140001 --------------开始查询-------------------------- select * from [tb] t where LEN(col)=(select MAX(len(col)) from tb where LEFT(col,4)=LEFT(t.col,4)) ----------------结果---------------------------- /* col ----------- 100101 12010201 1300 140001(4 行受影响) */
---------------------------------------------------------------- -- Author :fredrickhu(小F，向高手学习) -- Date :2011-09-27 14:12:08 -- Verstion: -- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86) -- Apr 22 2011 11:57:00 -- Copyright (c) Microsoft Corporation -- Enterprise Evaluation Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64) -- ---------------------------------------------------------------- --> 测试数据：[tb] if object_id('[tb]') is not null drop table [tb] go create table [tb]([col] int) insert [tb] select 1001 union all select 100101 union all select 1201 union all select 120101 union all select 120102 union all select 12010201 union all select 1300 union all select 1400 union all select 140001 --------------开始查询-------------------------- select * from [tb] t where LEN(col)=(select MAX(len(col)) from tb where CHARINDEX(ltrim(t.col),ltrim(col))>0) ----------------结果---------------------------- /*col ----------- 100101 120101 12010201 1300 140001(5 行受影响) */
如果是oracle数据库呢？ 还有编号规则是4-2-2,或者4-3-3呢
if object_id('[tb]') is not null drop table [tb] go create table [tb]([col] NVARCHAR(500)) insert [tb] select 1001 union all select 100101 union all select 1201 union all select 120101 union all select 120102 union all select 12010201 union all select 1300 union all select 1400 union all select 140001 SELECT * FROM TB AS a WHERE NOT EXISTS(SELECT 1 FROM TB WHERE Col LIKE a.Col||'_%')
---------------------------------------------------------------- -- Author :qiudf(day day up) -- Date :2011-09-27 15:10 -- Verstion: ---------------------------------------------------------------- --> 测试数据：[a]with a as ( select 1001 a01 from dual union all select 100101 from dual union all select 1201 from dual union all select 120101 from dual union all select 120102 from dual union all select 120103 from dual union all select 120104 from dual union all select 12010201 from dual union all select 1300 from dual union all select 130004 from dual union all select 130005 from dual union all select 13000401 from dual union all select 1400 from dual ) --根据树的层级来抓取 select a01 from ( select a01,level rr,row_number() over(partition by substr(a01,1,length(a01)-2) order by a01) rn from a where length(a01)>2 start with a01=substr(a01,1,4) connect by substr(a01,1,length(a01)-2)=prior a01 ) where rn<rr union all --考虑单行的情况 select to_number(a01) from (select substr(a01,1,4) a01,max(rn) rn from (select a01,row_number() over(partition by substr(a01,1,4) order by a01) rn from a) group by substr(a01,1,4) ) where rn=1--result: 1 100101 2 120101 3 12010201 4 130004 5 13000401 6 1400
如果是已知的表，直接根据id 不是都可以查到了吗？ 如果是未知的 select id from 表名 这样可以提高查询速度 然后，可以根据最后一个id不是可以查到最后一行数据了吗？
---------------------------------------------------------------- -- Author :qiudf(day day up) -- Date :2011-09-27 15:10 -- Verstion: ---------------------------------------------------------------- --> 测试数据：[a]--create table a (a01 int) --select * from a for update --根据树的层级来抓取 select a01 from ( select a01,level rr,row_number() over(partition by substr(a01,1,length(a01)-2) order by a01) rn from a where length(a01)>2 start with a01=substr(a01,1,length(a01)) --修改此处的取值,测试OK! connect by substr(a01,1,length(a01)-2)=prior a01 ) where rn<rr union all --考虑单行的情况 select to_number(a01) from (select substr(a01,1,4) a01,max(rn) rn from (select a01,row_number() over(partition by substr(a01,1,4) order by a01) rn from a) group by substr(a01,1,4) ) where rn=1
cosio你的sql太复杂了。 --对的。 SELECT t.acc_code, t.* FROM bz_acc t WHERE NOT EXISTS( SELECT * FROM bz_acc WHERE acc_code LIKE t.acc_code||'_%' ) order by t.acc_code;--原来是错的。修改后正确。 select acc_code, t.* from bz_acc t where length(acc_code)=( select MAX(length(acc_code)) from bz_acc where instr(acc_code, t.acc_code) =1 --where instr(acc_code, t.acc_code-) >0 --会包含不是首位开始而漏掉,故注释 ) order by t.acc_code;SELECT a.acc_code, a.* FROM bz_acc a order by a.acc_code;
-- Author :fredrickhu(小F，向高手学习)
-- Date :2011-09-27 14:12:08
-- Verstion:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86)
-- Apr 22 2011 11:57:00
-- Copyright (c) Microsoft Corporation
-- Enterprise Evaluation Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([col] int)
insert [tb]
select 1001 union all
select 100101 union all
select 1201 union all
select 120101 union all
select 120102 union all
select 12010201 union all
select 1300 union all
select 1400 union all
select 140001
--------------开始查询--------------------------
select * from [tb] t where LEN(col)=(select MAX(len(col)) from tb where LEFT(col,4)=LEFT(t.col,4))
----------------结果----------------------------
/* col
-----------
100101
12010201
1300
140001(4 行受影响)
*/
-- Author :fredrickhu(小F，向高手学习)
-- Date :2011-09-27 14:12:08
-- Verstion:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86)
-- Apr 22 2011 11:57:00
-- Copyright (c) Microsoft Corporation
-- Enterprise Evaluation Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([col] int)
insert [tb]
select 1001 union all
select 100101 union all
select 1201 union all
select 120101 union all
select 120102 union all
select 12010201 union all
select 1300 union all
select 1400 union all
select 140001
--------------开始查询--------------------------
select * from [tb] t where LEN(col)=(select MAX(len(col)) from tb where CHARINDEX(ltrim(t.col),ltrim(col))>0)
----------------结果----------------------------
/*col
-----------
100101
120101
12010201
1300
140001(5 行受影响)
*/
还有编号规则是4-2-2,或者4-3-3呢
go
create table [tb]([col] NVARCHAR(500))
insert [tb]
select 1001 union all
select 100101 union all
select 1201 union all
select 120101 union all
select 120102 union all
select 12010201 union all
select 1300 union all
select 1400 union all
select 140001
SELECT * FROM TB AS a WHERE NOT EXISTS(SELECT 1 FROM TB WHERE Col LIKE a.Col||'_%')
-- Author :qiudf(day day up)
-- Date :2011-09-27 15:10
-- Verstion:
----------------------------------------------------------------
--> 测试数据：[a]with a as
(
select 1001 a01 from dual union all
select 100101 from dual union all
select 1201 from dual union all
select 120101 from dual union all
select 120102 from dual union all
select 120103 from dual union all
select 120104 from dual union all
select 12010201 from dual union all
select 1300 from dual union all
select 130004 from dual union all
select 130005 from dual union all
select 13000401 from dual union all
select 1400 from dual
)
--根据树的层级来抓取
select a01 from
(
select a01,level rr,row_number() over(partition by substr(a01,1,length(a01)-2) order by a01) rn
from a
where length(a01)>2
start with a01=substr(a01,1,4) connect by substr(a01,1,length(a01)-2)=prior a01
)
where rn<rr
union all
--考虑单行的情况
select to_number(a01)
from
(select substr(a01,1,4) a01,max(rn) rn
from
(select a01,row_number() over(partition by substr(a01,1,4) order by a01) rn from a)
group by substr(a01,1,4)
)
where rn=1--result:
1 100101
2 120101
3 12010201
4 130004
5 13000401
6 1400
把
substr(a01,1,length(a01)-2)
改为
substr(a01,1,length(a01)-3)
即可!
一般对于树形结构，我们要有一下几个关键字段：
ParentID,FullID,Level,IsLeaf
这样的话，我们查询明细节点的话，直接判断是否为叶子节点即可。
如果设置parentid、fullid、level、IsLeaf冗余字段，
好处：查询科目信息快，不用动态计算这些值。
缺点：维护这些信息时，需要额外的判断、保存代码。会计科目基础表，数量不多，
一般只是200多条记录，动态法查询也不会太慢，所以省略了这些字段。但是如果换成是产品配套清单BOM，数据可能多达上万时，
就需要上述冗余字段了。所以不同情况，不同的设计方法。
数据如下：
5405
5501
550101
550102
550201
550202
55020221
550203
550205
550206
550207
550208
550209
550211
550215
550216
550217
550218
550220
5503
550301
550302
550303
550304
5601
560101
560102
560103
560104
560106
5701
5801
1701
5301
1102
113319
1911
217105
312104
410506
1101
113301
113315
1221
1500
1505
111102
1151
140201
210101
21710107
217109
560105
218157
218163
311105
314102
400003
1601
1603
1605
1001
1002
100201
1009
100901
110102
1111
111101
1121
1122
1131
1133
113302
1141
1161
1201
1211
1231
1232
1241
1243
1244
1250
1251
1261
1271
1281
1291
129101
129102
1401
140101
140102
1402
140202
1501
1502
1801
1805
1815
191101
191102
2101
210102
210104
2111
2121
2131
2141
2151
2153
2161
2171
217101
21710101
21710102
21710103
21710104
21710105
21710106
21710108
21710109
217102
217103
217104
217106
217107
217108
217110
217111
217112
2176
2181
218101
218120
5502
550204
550210
550219
550305
219103
2201
230101
230102
100101
3103
3111
311101
311102
311103
311104
311106
311107
3121
312101
312102
312103
312105
312106
3131
3141
314101
314103
314104
314105
314106
314107
314108
314109
314110
314111
314115
4000
400001
400002
4105
410501
410502
410503
410504
5101
5102
510201
510202
510203
5201
5203
530101
530102
530103
5401SELECT a.acc_code, a.*
FROM bz_acc a
WHERE NOT EXISTS(
SELECT 1 FROM bz_acc
WHERE acc_code
LIKE a.acc_code||'_%')
order by a.acc_code;select acc_code, t.* from bz_acc t
where length(acc_code)=
(select MAX(length(acc_code))
from bz_acc
where instr(acc_code, t.acc_code) >0)
order by t.acc_code;SELECT a.acc_code, a.*
FROM bz_acc a
order by a.acc_code;发现fredrickhu的算法，结果比roy_88少2条，
所以fredrickhu的算法，不对，而roy_88是对的。
可以拿上面的数据来测试。
如果是未知的
select id from 表名 这样可以提高查询速度
然后，可以根据最后一个id不是可以查到最后一行数据了吗？
-- Author :qiudf(day day up)
-- Date :2011-09-27 15:10
-- Verstion:
----------------------------------------------------------------
--> 测试数据：[a]--create table a (a01 int)
--select * from a for update
--根据树的层级来抓取
select a01 from
(
select a01,level rr,row_number() over(partition by substr(a01,1,length(a01)-2) order by a01) rn
from a
where length(a01)>2
start with a01=substr(a01,1,length(a01)) --修改此处的取值,测试OK!
connect by substr(a01,1,length(a01)-2)=prior a01
)
where rn<rr
union all
--考虑单行的情况
select to_number(a01)
from
(select substr(a01,1,4) a01,max(rn) rn
from
(select a01,row_number() over(partition by substr(a01,1,4) order by a01) rn from a)
group by substr(a01,1,4)
)
where rn=1
--对的。
SELECT t.acc_code, t.*
FROM bz_acc t
WHERE NOT EXISTS(
SELECT * FROM bz_acc
WHERE acc_code LIKE t.acc_code||'_%'
)
order by t.acc_code;--原来是错的。修改后正确。
select acc_code, t.* from bz_acc t
where length(acc_code)=(
select MAX(length(acc_code))
from bz_acc
where instr(acc_code, t.acc_code) =1
--where instr(acc_code, t.acc_code-) >0 --会包含不是首位开始而漏掉,故注释
)
order by t.acc_code;SELECT a.acc_code, a.*
FROM bz_acc a
order by a.acc_code;
1121 1121
1122 1122
1131 1131
1141 1141
1151 1151
1161 1161
1201 1201
1211 1211
1221 1221
1231 1231
1232 1232
1241 1241
1243 1243
1244 1244
1250 1250
1251 1251
1261 1261
1271 1271
1281 1281
1500 1500
1501 1501
1502 1502
1505 1505
1601 1601
1603 1603
1605 1605
1701 1701
1801 1801
1805 1805
1815 1815
2111 2111
2121 2121
2131 2131
2141 2141
2151 2151
2153 2153
2161 2161
2176 2176
2201 2201
3103 3103
3131 3131
5101 5101
5201 5201
5203 5203
5401 5401
5405 5405
5701 5701
5801 5801
100101 100101
100201 100201
100901 100901
110102 110102
111101 111101
111102 111102
113301 113301
113302 113302
113315 113315
113319 113319
129101 129101
129102 129102
140101 140101
140102 140102
140201 140201
140202 140202
191101 191101
191102 191102
210101 210101
210102 210102
210104 210104
217102 217102
217103 217103
217104 217104
217105 217105
217106 217106
217107 217107
217108 217108
217109 217109
217110 217110
217111 217111
217112 217112
218101 218101
218120 218120
218157 218157
218163 218163
219103 219103
230101 230101
230102 230102
311101 311101
311102 311102
311103 311103
311104 311104
311105 311105
311106 311106
311107 311107
312101 312101
312102 312102
312103 312103
312104 312104
312105 312105
312106 312106
314101 314101
314102 314102
314103 314103
314104 314104
314105 314105
314106 314106
314107 314107
314108 314108
314109 314109
314110 314110
314111 314111
314115 314115
400001 400001
400002 400002
400003 400003
410501 410501
410502 410502
410503 410503
410504 410504
410506 410506
510201 510201
510202 510202
510203 510203
530101 530101
530102 530102
530103 530103
550101 550101
550102 550102
550201 550201
550203 550203
550204 550204
550205 550205
550206 550206
550207 550207
550208 550208
550209 550209
550210 550210
550211 550211
550215 550215
550216 550216
550217 550217
550218 550218
550219 550219
550220 550220
550301 550301
550302 550302
550303 550303
550304 550304
550305 550305
560101 560101
560102 560102
560103 560103
560104 560104
560105 560105
560106 560106
21710101 21710101
21710102 21710102
21710103 21710103
21710104 21710104
21710105 21710105
21710106 21710106
21710107 21710107
21710108 21710108
21710109 21710109
55020221 55020221
上面的SQL运行出来的效果,取一部分,21710101 21710101
21710102 21710102
21710103 21710103
21710104 21710104
21710105 21710105
21710106 21710106
21710107 21710107
21710108 21710108
21710109 21710109
这些数据,末级只抓取一个嘛?
1001
1001011201
120101
120102
120102011300
1400
140001取得最末级：
100101
120101
12010201
1300
140001
看一下需求中: 120102没有抓取! 所以.....
cosio,佩服你的钻研，但你要学习大牛，自己多动动脑筋、多看看书。