27,579
社区成员
发帖
与我相关
我的任务
分享
create table T(id varchar(50),tname varchar(50),tpath varchar(50),indextime datetime)
insert T
select '4b5d5056-58b7-4065-8ca9-21bd50c70c58','123456','087016','2011-12-16 09:48:01.810'
union all select '05527C13-3470-4CC0-AA75-A2512C407DC5','闪电买入价格以0.01微调','087016001','2011-12-19 09:06:08.855'
union all select '3BF1C178-627A-411C-B1B7-6E0D139868EC','鼠标悬停盘口价格以外的区域','087016002','2011-12-19 09:06:10.543'
union all select 'C77BB005-3E39-433D-B1D7-1D26E8C85186','锁定交易终端时,单击盘口弹出解锁窗体','087004','2011-12-16 09:47:56.309'
union all select 'D5CEAE3E-D0C1-4D9D-B55D-30482C0997D5','闪电交易窗体顶部Tab正确','087006','2011-12-16 09:47:57.809'
union all select 'B6357CF1-93E4-4053-AFBA-FEABF3FBF0F2','不登录交易终端单击盘口','087008','2011-12-16 09:48:33.466'
union all select '72809f19-72e0-4f0a-b562-c885199efbb2','8484','087009','2011-12-16 09:48:53.900'
union all select 'E0D1D497-A29E-4BE3-83FC-CA2D722D1195','闪电买入上市首日新股价格无限制','087011','2011-12-16 09:49:13.513'
go
select * from T order by STUFF(tpath,4,3,reverse(substring(tpath,4,3))),indextime
/*
id tname tpath indextime
-------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -----------------------
E0D1D497-A29E-4BE3-83FC-CA2D722D1195 闪电买入上市首日新股价格无限制 087011 2011-12-16 09:49:13.513
C77BB005-3E39-433D-B1D7-1D26E8C85186 锁定交易终端时,单击盘口弹出解锁窗体 087004 2011-12-16 09:47:56.310
D5CEAE3E-D0C1-4D9D-B55D-30482C0997D5 闪电交易窗体顶部Tab正确 087006 2011-12-16 09:47:57.810
4b5d5056-58b7-4065-8ca9-21bd50c70c58 123456 087016 2011-12-16 09:48:01.810
05527C13-3470-4CC0-AA75-A2512C407DC5 闪电买入价格以0.01微调 087016001 2011-12-19 09:06:08.857
3BF1C178-627A-411C-B1B7-6E0D139868EC 鼠标悬停盘口价格以外的区域 087016002 2011-12-19 09:06:10.543
B6357CF1-93E4-4053-AFBA-FEABF3FBF0F2 不登录交易终端单击盘口 087008 2011-12-16 09:48:33.467
72809f19-72e0-4f0a-b562-c885199efbb2 8484 087009 2011-12-16 09:48:53.900
(8 行受影响)
*/
go
drop table T
DECLARE @T table (id varchar(50),tname varchar(50),tpath varchar(50),indextime datetime)
insert @T
select '4b5d5056-58b7-4065-8ca9-21bd50c70c58','123456','087016','2011-12-16 09:48:01.810'
union all select '05527C13-3470-4CC0-AA75-A2512C407DC5','闪电买入价格以0.01微调','087016001','2011-12-19 09:06:08.855'
union all select '3BF1C178-627A-411C-B1B7-6E0D139868EC','鼠标悬停盘口价格以外的区域','087016002','2011-12-19 09:06:10.543'
union all select 'C77BB005-3E39-433D-B1D7-1D26E8C85186','锁定交易终端时,单击盘口弹出解锁窗体','087004','2011-12-16 09:47:56.309'
union all select 'D5CEAE3E-D0C1-4D9D-B55D-30482C0997D5','闪电交易窗体顶部Tab正确','087006','2011-12-16 09:47:57.809'
union all select 'B6357CF1-93E4-4053-AFBA-FEABF3FBF0F2','不登录交易终端单击盘口','087008','2011-12-16 09:48:33.466'
union all select '72809f19-72e0-4f0a-b562-c885199efbb2','8484','087009','2011-12-16 09:48:53.900'
union all select 'E0D1D497-A29E-4BE3-83FC-CA2D722D1195','闪电买入上市首日新股价格无限制','087011','2011-12-16 09:49:13.513'
SELECT * FROM @T A
ORDER BY (SELECT TOP 1 indextime FROM @T WHERE tpath = LEFT(A.tpath,3))
,(SELECT TOP 1 indextime FROM @T WHERE tpath = LEFT(A.tpath,6))
,indextime
-- 结果
id tname tpath indextime
C77BB005-3E39-433D-B1D7-1D26E8C85186 锁定交易终端时,单击盘口弹出解锁窗体 087004 2011-12-16 09:47:56.310
D5CEAE3E-D0C1-4D9D-B55D-30482C0997D5 闪电交易窗体顶部Tab正确 087006 2011-12-16 09:47:57.810
4b5d5056-58b7-4065-8ca9-21bd50c70c58 123456 087016 2011-12-16 09:48:01.810
05527C13-3470-4CC0-AA75-A2512C407DC5 闪电买入价格以0.01微调 087016001 2011-12-19 09:06:08.857
3BF1C178-627A-411C-B1B7-6E0D139868EC 鼠标悬停盘口价格以外的区域 087016002 2011-12-19 09:06:10.543
B6357CF1-93E4-4053-AFBA-FEABF3FBF0F2 不登录交易终端单击盘口 087008 2011-12-16 09:48:33.467
72809f19-72e0-4f0a-b562-c885199efbb2 8484 087009 2011-12-16 09:48:53.900
E0D1D497-A29E-4BE3-83FC-CA2D722D1195 闪电买入上市首日新股价格无限制 087011 2011-12-16 09:49:13.513
SEELCT * FROM T A
ORDER BY (SELECT TOP 1 indextime FROM T WHERE tpath = LEFT(A.tpath,3))
,(SELECT TOP 1 indextime FROM T WHERE tpath = LEFT(A.tpath,6))
,indextime
create table T(id varchar(50),tname varchar(50),tpath varchar(50),indextime datetime)
insert T
select '4b5d5056-58b7-4065-8ca9-21bd50c70c58','123456','087016','2011-12-16 09:48:01.810'
union all select '05527C13-3470-4CC0-AA75-A2512C407DC5','闪电买入价格以0.01微调','087016001','2011-12-19 09:06:08.855'
union all select '3BF1C178-627A-411C-B1B7-6E0D139868EC','鼠标悬停盘口价格以外的区域','087016002','2011-12-19 09:06:10.543'
union all select 'C77BB005-3E39-433D-B1D7-1D26E8C85186','锁定交易终端时,单击盘口弹出解锁窗体','087004','2011-12-16 09:47:56.309'
union all select 'D5CEAE3E-D0C1-4D9D-B55D-30482C0997D5','闪电交易窗体顶部Tab正确','087006','2011-12-16 09:47:57.809'
union all select 'B6357CF1-93E4-4053-AFBA-FEABF3FBF0F2','不登录交易终端单击盘口','087008','2011-12-16 09:48:33.466'
union all select '72809f19-72e0-4f0a-b562-c885199efbb2','8484','087009','2011-12-16 09:48:53.900'
union all select 'E0D1D497-A29E-4BE3-83FC-CA2D722D1195','闪电买入上市首日新股价格无限制','087011','2011-12-16 09:49:13.513'
select * from t a
order by (select min(indextime) from t where LEFT(tpath,6)=LEFT(a.tpath,6))
/*
id tname tpath indextime
-------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -----------------------
C77BB005-3E39-433D-B1D7-1D26E8C85186 锁定交易终端时,单击盘口弹出解锁窗体 087004 2011-12-16 09:47:56.310
D5CEAE3E-D0C1-4D9D-B55D-30482C0997D5 闪电交易窗体顶部Tab正确 087006 2011-12-16 09:47:57.810
4b5d5056-58b7-4065-8ca9-21bd50c70c58 123456 087016 2011-12-16 09:48:01.810
05527C13-3470-4CC0-AA75-A2512C407DC5 闪电买入价格以0.01微调 087016001 2011-12-19 09:06:08.857
3BF1C178-627A-411C-B1B7-6E0D139868EC 鼠标悬停盘口价格以外的区域 087016002 2011-12-19 09:06:10.543
B6357CF1-93E4-4053-AFBA-FEABF3FBF0F2 不登录交易终端单击盘口 087008 2011-12-16 09:48:33.467
72809f19-72e0-4f0a-b562-c885199efbb2 8484 087009 2011-12-16 09:48:53.900
E0D1D497-A29E-4BE3-83FC-CA2D722D1195 闪电买入上市首日新股价格无限制 087011 2011-12-16 09:49:13.513
(8 行受影响)
BOM按节点排序应用实例
--------------------------------------------------------------------------
-- Author : htl258(Tony)
-- Date : 2010-04-23 02:37:28
-- Version:Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
-- Subject: BOM按节点排序应用实例
--------------------------------------------------------------------------
--实例1:
--> 生成测试数据表:tb
IF NOT OBJECT_ID('[tb]') IS NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb]([id] INT,[code] NVARCHAR(10),[pid] INT,[name] NVARCHAR(10))
INSERT [tb]
SELECT 1,'01',0,N'服装' UNION ALL
SELECT 2,'01',1,N'男装' UNION ALL
SELECT 3,'01',2,N'西装' UNION ALL
SELECT 4,'01',3,N'全毛' UNION ALL
SELECT 5,'02',3,N'化纤' UNION ALL
SELECT 6,'02',2,N'休闲装' UNION ALL
SELECT 7,'02',1,N'女装' UNION ALL
SELECT 8,'01',7,N'套装' UNION ALL
SELECT 9,'02',7,N'职业装' UNION ALL
SELECT 10,'03',7,N'休闲装' UNION ALL
SELECT 11,'04',7,N'西装' UNION ALL
SELECT 12,'01',11,N'全毛' UNION ALL
SELECT 13,'02',11,N'化纤' UNION ALL
SELECT 14,'05',7,N'休闲装'
GO
--SELECT * FROM [tb]
-->SQL查询如下:
;WITH T AS
(
SELECT CAST(CODE AS VARCHAR(20)) AS CODE,*,
CAST(ID AS VARBINARY(MAX)) AS px
FROM tb AS A
WHERE NOT EXISTS(SELECT * FROM tb WHERE id=A.pid)
UNION ALL
SELECT CAST(B.CODE+A.CODE AS VARCHAR(20)),A.*,
CAST(B.px+CAST(A.ID AS VARBINARY) AS VARBINARY(MAX))
FROM tb AS A
JOIN T AS B
ON A.pid=B.id
)
SELECT Code,Name FROM T
ORDER BY px
/*
Code Name
-------------------- ----------
01 服装
0101 男装
010101 西装
01010101 全毛
01010102 化纤
010102 休闲装
0102 女装
010201 套装
010202 职业装
010203 休闲装
010204 西装
01020401 全毛
01020402 化纤
010205 休闲装
(14 行受影响)
*/
--实例2:
--> 生成测试数据表:tb
IF NOT OBJECT_ID('[tb]') IS NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb]([id] INT,[parentid] INT,[categoryname] NVARCHAR(10))
INSERT [tb]
SELECT 1,0,'test1' UNION ALL
SELECT 2,0,'test2' UNION ALL
SELECT 3,1,'test1.1' UNION ALL
SELECT 4,2,'test2.1' UNION ALL
SELECT 5,3,'test1.1.1' UNION ALL
SELECT 6,1,'test1.2'
GO
--SELECT * FROM [tb]
-->SQL查询如下:
;WITH T AS
(
SELECT *,CAST(ID AS VARBINARY(MAX)) AS px
FROM tb AS A
WHERE NOT EXISTS(SELECT * FROM tb WHERE id=A.[parentid])
UNION ALL
SELECT A.*,CAST(B.px+CAST(A.ID AS VARBINARY) AS VARBINARY(MAX))
FROM tb AS A
JOIN T AS B
ON A.[parentid]=B.id
)
SELECT [id],[parentid],[categoryname] FROM T
ORDER BY px
/*
id parentid categoryname
----------- ----------- ------------
1 0 test1
3 1 test1.1
5 3 test1.1.1
6 1 test1.2
2 0 test2
4 2 test2.1
(6 行受影响)
*/
本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/htl258/archive/2010/04/23/5518166.aspx