------------------------------------------------------------------------
-- Author: happyflystone
-- Date : 2009-03-30 11:03:14
-- Ver: Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86)
-- Apr 14 2006 01:12:25
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
--
-------------------------------------------------------------------------- Test Data: ta
IF OBJECT_ID('ta') IS NOT NULL
DROP TABLE ta
Go
CREATE TABLE ta(CID NVARCHAR(2),PID NVARCHAR(15),NAME NVARCHAR(1),S1 INT)
Go
INSERT INTO ta
SELECT 'a1','001','一',1 UNION ALL
SELECT 'a2','001002','二',2 UNION ALL
SELECT 'a3','001003','三',3 UNION ALL
SELECT 'a4','001002001','四',4 UNION ALL
SELECT 'a5','001002004','五',5 UNION ALL
SELECT 'a6','001002001001','六',6 UNION ALL
SELECT 'a7','001002004001001','七',7
GO
--Start
SELECT
left(b.pid,len(a.pid)+3),sum(b.s1)
FROM
TA a
cross apply (select * from ta where pid like a.pid+'%') b
where a.pid = '001002' and len(b.pid) > len(a.pid)
group by left(b.pid,len(a.pid)+3)--Result:
/*
--------------- -----------
001002001 10
001002004 12*/
--End
-- Author: happyflystone
-- Date : 2009-03-30 11:03:14
-- Ver: Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86)
-- Apr 14 2006 01:12:25
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
--
-------------------------------------------------------------------------- Test Data: ta
IF OBJECT_ID('ta') IS NOT NULL
DROP TABLE ta
Go
CREATE TABLE ta(CID NVARCHAR(2),PID NVARCHAR(15),NAME NVARCHAR(1),S1 INT)
Go
INSERT INTO ta
SELECT 'a1','001','一',1 UNION ALL
SELECT 'a2','001002','二',2 UNION ALL
SELECT 'a3','001003','三',3 UNION ALL
SELECT 'a4','001002001','四',4 UNION ALL
SELECT 'a5','001002004','五',5 UNION ALL
SELECT 'a6','001002001001','六',6 UNION ALL
SELECT 'a7','001002004001001','七',7
GO
--Start
SELECT
left(b.pid,len(a.pid)+3),sum(b.s1)
FROM
TA a
cross apply (select * from ta where pid like a.pid+'%') b
where a.pid = '001002' and len(b.pid) > len(a.pid)
group by left(b.pid,len(a.pid)+3)--Result:
/*
--------------- -----------
001002001 10
001002004 12*/
--End
-- Author: liangCK 小梁
---------------------------------
--> 生成测试数据: @T
DECLARE @T TABLE (ID VARCHAR(2),PID VARCHAR(15),NAME VARCHAR(2),s1 INT)
INSERT INTO @T
SELECT 'a1','001','一',1 UNION ALL
SELECT 'a2','001002','二',2 UNION ALL
SELECT 'a3','001003','三',3 UNION ALL
SELECT 'a4','001002001','四',4 UNION ALL
SELECT 'a5','001002004','五',5 UNION ALL
SELECT 'a6','001002001001','六',6 UNION ALL
SELECT 'a7','001002004001001','七',7--SQL查询如下:SELECT
LEFT(B.PID,LEN(A.PID)+3) AS PID,
SUM(B.s1) AS s1
FROM @T AS A
JOIN @T AS B
ON A.PID='001002'
AND B.PID LIKE A.PID+'%'
AND LEN(B.PID)>LEN(A.PID)
GROUP BY LEFT(B.PID,LEN(A.PID)+3)/*
PID s1
--------------- -----------
001002001 10
001002004 12(2 行受影响)
*/