表AF_BM F_Name
001
001001
001002
001003 表B
F_BM F_SL
001001 100
001002 50
001003 20
怎么整出
A.F_BM B.F_SL
001 170
001001 100
001002 50
001003 20
求一SQL
001
001001
001002
001003 表B
F_BM F_SL
001001 100
001002 50
001003 20
怎么整出
A.F_BM B.F_SL
001 170
001001 100
001002 50
001003 20
求一SQL
-- Author : htl258(Tony)
-- Date : 2010-04-22 14:33:40
-- 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)
--------------------------------------------------------------------------
--> 生成测试数据表:aIF NOT OBJECT_ID('[a]') IS NULL
DROP TABLE [a]
GO
CREATE TABLE [a]([F_BM] NVARCHAR(10),[F_Name] NVARCHAR(10))
INSERT [a]
SELECT '001',NULL UNION ALL
SELECT '001001',NULL UNION ALL
SELECT '001002',NULL UNION ALL
SELECT '001003',NULL
GO
--SELECT * FROM [a]--> 生成测试数据表:BIF NOT OBJECT_ID('[B]') IS NULL
DROP TABLE [B]
GO
CREATE TABLE [B]([F_BM] NVARCHAR(10),[F_SL] INT)
INSERT [B]
SELECT '001001',100 UNION ALL
SELECT '001002',50 UNION ALL
SELECT '001003',20
GO
--SELECT * FROM [B]-->SQL查询如下:
select *,FSUM=(select SUM(F_SL) from B where F_BM like a.F_BM+'%') from A
/*
F_BM F_Name FSUM
---------- ---------- -----------
001 NULL 170
001001 NULL 100
001002 NULL 50
001003 NULL 20(4 行受影响)
*/
select F_BM,F_SL=(select SUM(F_SL) from B where F_BM like a.F_BM+'%') from A
/*
F_BM F_SL
---------- -----------
001 170
001001 100
001002 50
001003 20(4 行受影响)
*/
--> 测试数据:[TA]
if object_id('[TA]') is not null drop table [TA]
create table [TA]([F_BM] varchar(6),[F_Name] varchar(10))
insert [TA]
select '001',null union all
select '001001',null union all
select '001002',null union all
select '001003',null
--> 测试数据:[TB]
if object_id('[TB]') is not null drop table [TB]
create table [TB]([F_BM] varchar(6),[F_SL] int)
insert [TB]
select '001001',100 union all
select '001002',50 union all
select '001003',20select a.F_BM,
F_SL=(select sum(F_SL) from TB where charindex(a.F_BM,F_BM)>0)
from [TA] a left join TB b on a.F_BM=b.F_BM/*
F_BM F_SL
------ -----------
001 170
001001 100
001002 50
001003 20(所影响的行数为 4 行)*/
drop table TA,TB
FROM TA, TB
WHERE TB.F_BM LIKE ISNULL(TA.F_BM, '') + '%'
GROUP BY
TA.F_BM