如下,表t_1:
code Snum
-------------------------------
a01 Null
a02 Null
a03 Null
a04 Null表t_2:
code num
------------------------------
a01 1
a01 2
a02 2
a02 12
a03 100
a03 10要求结果为:
code Snum
-------------------------------
a01 3
a02 14
a03 110
a04 Null
PS:表t_1的code和表t_2的code是主外键关系. 高手们帮忙看一下要如何写。。
code Snum
-------------------------------
a01 Null
a02 Null
a03 Null
a04 Null表t_2:
code num
------------------------------
a01 1
a01 2
a02 2
a02 12
a03 100
a03 10要求结果为:
code Snum
-------------------------------
a01 3
a02 14
a03 110
a04 Null
PS:表t_1的code和表t_2的code是主外键关系. 高手们帮忙看一下要如何写。。
from t_1
left join t_2 on t_1.code=t_2.code
-- Author :SQL77(只为思齐老)
-- Date :2010-01-10 17:16:30
-- Version:
-- Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
-- Aug 6 2000 00:57:48
-- Copyright (c) 1988-2000 Microsoft Corporation
-- Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[TB1]
if object_id('[TB1]') is not null drop table [TB1]
go
create table [TB1]([code] varchar(3),[Snum] sql_variant)
insert [TB1]
select 'a01',null union all
select 'a02',null union all
select 'a03',null union all
select 'a04',null
--> 测试数据:[TB2]
if object_id('[TB2]') is not null drop table [TB2]
go
create table [TB2]([code] varchar(3),[num] int)
insert [TB2]
select 'a01',1 union all
select 'a01',2 union all
select 'a02',2 union all
select 'a02',12 union all
select 'a03',100 union all
select 'a03',10
--------------开始查询--------------------------
SELECT
T1.CODE,ISNULL(LTRIM(SUM(T2.NUM)),'')AS NUM
FROM
TB1 T1 LEFT JOIN
TB2 T2
ON T1.CODE=T2.CODEGROUP BY T1.CODE
----------------结果----------------------------
/* (所影响的行数为 4 行)
(所影响的行数为 6 行)CODE NUM
---- ------------
a01 3
a02 14
a03 110
a04 (所影响的行数为 4 行)警告: 聚合或其它 SET 操作消除了空值。*/
-- Author :SQL77(只为思齐老)
-- Date :2010-01-10 17:16:30
-- Version:
-- Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
-- Aug 6 2000 00:57:48
-- Copyright (c) 1988-2000 Microsoft Corporation
-- Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[TB1]
if object_id('[TB1]') is not null drop table [TB1]
go
create table [TB1]([code] varchar(3),[Snum] sql_variant)
insert [TB1]
select 'a01',null union all
select 'a02',null union all
select 'a03',null union all
select 'a04',null
--> 测试数据:[TB2]
if object_id('[TB2]') is not null drop table [TB2]
go
create table [TB2]([code] varchar(3),[num] int)
insert [TB2]
select 'a01',1 union all
select 'a01',2 union all
select 'a02',2 union all
select 'a02',12 union all
select 'a03',100 union all
select 'a03',10
--------------开始查询--------------------------
SELECT
T1.CODE,SUM(T2.NUM)AS NUM
FROM
TB1 T1 LEFT JOIN
TB2 T2
ON T1.CODE=T2.CODEGROUP BY T1.CODE
----------------结果----------------------------
/*
(所影响的行数为 4 行)
(所影响的行数为 6 行)CODE NUM
---- -----------
a01 3
a02 14
a03 110
a04 NULL(所影响的行数为 4 行)警告: 聚合或其它 SET 操作消除了空值。
*/
select t_1.code, sum(t_2.snum) snum
from t_1 left join t_2 on t_1.code=t_2.code
group by t_1.code
-- Author :SQL77(只为思齐老)
-- Date :2010-01-10 17:16:30
-- Version:
-- Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
-- Aug 6 2000 00:57:48
-- Copyright (c) 1988-2000 Microsoft Corporation
-- Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[TB1]
if object_id('[TB1]') is not null drop table [TB1]
go
create table [TB1]([code] varchar(3),[Snum] sql_variant)
insert [TB1]
select 'a01',null union all
select 'a02',null union all
select 'a03',null union all
select 'a04',null
--> 测试数据:[TB2]
if object_id('[TB2]') is not null drop table [TB2]
go
create table [TB2]([code] varchar(3),[num] int)
insert [TB2]
select 'a01',1 union all
select 'a01',2 union all
select 'a02',2 union all
select 'a02',12 union all
select 'a03',100 union all
select 'a03',10
--------------开始查询--------------------------
UPDATE A SET Snum=B.NUM FROM
(
SELECT
T1.CODE,SUM(T2.NUM)AS NUM
FROM
TB1 T1 LEFT JOIN
TB2 T2
ON T1.CODE=T2.CODEGROUP BY T1.CODE)AS B ,TB1 A WHERE B.CODE=A.CODESELECT * FROM TB1
----------------结果----------------------------
/* (所影响的行数为 4 行)
(所影响的行数为 6 行)
(所影响的行数为 4 行)警告: 聚合或其它 SET 操作消除了空值。
code Snum
---- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
a01 3
a02 14
a03 110
a04 NULL(所影响的行数为 4 行)
*/