A表
lb parent
01 -1
0100 1
0101 2
02 -1
0200 1
0201 3 B表
lb sl
0100 10
0100 5
0101 20
0101 10
0200 4
0200 7
0201 5
0201 8 我想查询得到 gg parent sl
01 -1 45(10+5+20+10)
0100 1 15(10+5)
0101 2 30(20+10)
02 -1 24(4+7+5+8)
0200 1 11(4+7)
0201 3 13(5+8) 说明
lb parent
01 -1
0100 1
0101 2
02 -1
0200 1
0201 3 B表
lb sl
0100 10
0100 5
0101 20
0101 10
0200 4
0200 7
0201 5
0201 8 我想查询得到 gg parent sl
01 -1 45(10+5+20+10)
0100 1 15(10+5)
0101 2 30(20+10)
02 -1 24(4+7+5+8)
0200 1 11(4+7)
0201 3 13(5+8) 说明
-- Author : liangCK 梁爱兰
-- Comment: 小梁 爱 兰儿
-- Date : 2009-11-07 10:46:42
-------------------------------------
--> 生成测试数据: @tb1
DECLARE @tb1 TABLE (lb varchar(4),parent int)
INSERT INTO @tb1
SELECT '01',-1 UNION ALL
SELECT '0100',1 UNION ALL
SELECT '0101',2 UNION ALL
SELECT '02',-1 UNION ALL
SELECT '0200',1 UNION ALL
SELECT '0201',3
--> 生成测试数据: @tb2
DECLARE @tb2 TABLE (lb varchar(4),sl int)
INSERT INTO @tb2
SELECT '0100',10 UNION ALL
SELECT '0100',5 UNION ALL
SELECT '0101',20 UNION ALL
SELECT '0101',10 UNION ALL
SELECT '0200',4 UNION ALL
SELECT '0200',7 UNION ALL
SELECT '0201',5 UNION ALL
SELECT '0201',8--SQL查询如下:SELECT *,sl=(SELECT SUM(sl) FROM @tb2
WHERE lb LIKE A.lb+'%')
FROM @tb1 AS A/*
lb parent sl
---- ----------- -----------
01 -1 45
0100 1 15
0101 2 30
02 -1 24
0200 1 11
0201 3 13(6 行受影响)
*/
--> 测试数据:[A表]
if object_id('[A表]') is not null drop table [A表]
create table [A表]([lb] varchar(4),[parent] int)
insert [A表]
select '01',-1 union all
select '0100',1 union all
select '0101',2 union all
select '02',-1 union all
select '0200',1 union all
select '0201',3
--> 测试数据:[B表]
if object_id('[B表]') is not null drop table [B表]
create table [B表]([lb] varchar(4),[sl] int)
insert [B表]
select '0100',10 union all
select '0100',5 union all
select '0101',20 union all
select '0101',10 union all
select '0200',4 union all
select '0200',7 union all
select '0201',5 union all
select '0201',8select *,s1=(select sum(sl) from [B表] where A.lb=left(lb,len(A.lb))) from [A表] A /*
lb parent s1
---- ----------- -----------
01 -1 45
0100 1 15
0101 2 30
02 -1 24
0200 1 11
0201 3 13(所影响的行数为 6 行)*/
drop table B表,A表
declare @A table (lb nvarchar(10),parent int)
insert into @A select '01',-1
union all select '0100',1
union all select '0101',2
union all select '02',-1
union all select '0200',1
union all select '0201',3
declare @B table (lb nvarchar(10),sl int)
insert into @B select '0100',10
union all select '0100',5
union all select '0101',20
union all select '0101',10
union all select '0200',4
union all select '0200',7
union all select '0201',5
union all select '0201',8
select a.*,s1= (select sum(sl) from @b where left(a.lb,2)=left(lb,2)
and charindex( a.lb,lb)>0) from @a a
---------- ----------- -----------
01 -1 45
0100 1 15
0101 2 30
02 -1 24
0200 1 11
0201 3 13(6 行受影响)
-- Author :fredrickhu(小F,向高手学习)
-- Date :2009-11-07 15:25:58
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[A]
if object_id('[A]') is not null drop table [A]
go
create table [A]([lb] varchar(4),[parent] int)
insert [A]
select '01',-1 union all
select '0100',1 union all
select '0101',2 union all
select '02',-1 union all
select '0200',1 union all
select '0201',3
--> 测试数据:[B]
if object_id('[B]') is not null drop table [B]
go
create table [B]([lb] varchar(4),[sl] int)
insert [B]
select '0100',10 union all
select '0100',5 union all
select '0101',20 union all
select '0101',10 union all
select '0200',4 union all
select '0200',7 union all
select '0201',5 union all
select '0201',8
--------------开始查询--------------------------
select
a.lb,a.parent,sum(b.s1) as sl
from
A
left join
(select lb,sum(sl) as s1 from b group by lb)b
on
left(a.lb,2)=left(b.lb,2)
group by
a.lb,a.parent
----------------结果----------------------------
/*
*/
-- Author :fredrickhu(小F,向高手学习)
-- Date :2009-11-07 15:25:58
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[A]
if object_id('[A]') is not null drop table [A]
go
create table [A]([lb] varchar(4),[parent] int)
insert [A]
select '01',-1 union all
select '0100',1 union all
select '0101',2 union all
select '02',-1 union all
select '0200',1 union all
select '0201',3
--> 测试数据:[B]
if object_id('[B]') is not null drop table [B]
go
create table [B]([lb] varchar(4),[sl] int)
insert [B]
select '0100',10 union all
select '0100',5 union all
select '0101',20 union all
select '0101',10 union all
select '0200',4 union all
select '0200',7 union all
select '0201',5 union all
select '0201',8
--------------开始查询--------------------------
select
a.lb,a.parent,sum(b.s1) as sl
from
A
left join
(select lb,sum(sl) as s1 from b group by lb)b
on
left(a.lb,2)=left(b.lb,2)
group by
a.lb,a.parent
----------------结果----------------------------
/* lb parent sl
---- ----------- -----------
01 -1 45
02 -1 24
0100 1 45
0200 1 24
0101 2 45
0201 3 24(6 行受影响)*/