SQL语句问题..高手进

举例例子数据
cpcode] [sb_ym] [op_date] [ts_amt]
4407960103 200812 2009-01-15 09:37:05.483 1962.69
4407960209 200901 2009-04-08 15:39:58.043 13190.88
4407960103 200403 2004-07-08 15:08:12.357 8626.04
4407960459 200902 2009-04-13 08:35:39.717 12707.50
4407960103 200812 2009-03-11 11:55:04.890 13498.92
4407960033 200411 2005-04-12 15:42:00.170 5241.58
4407960103 200811 2008-12-10 11:39:08.797 9973.94
4407962202 200812 2009-02-24 10:21:34.200 20312.48
4407960103 200902 2009-04-13 08:35:39.717 15000.00
4407964081 200812 2009-03-12 17:11:26.513 13480.99 表二 [code] [swcode]
4407039901 44070301
4407859901 44078501
4407960103 44070100
4407960459 44070100
4407910003 44070100
4407910004 44070100
4407910005 44070100
4407910006 44070100
4407910007 44070100
4407910008 44070100 查询[swcode]44070100 下的所有cpcode 在[sb_ym]2008年的总金额最后的结果ts_amt总金额应该是
61769.09

SELECT SUM(tsjh.ts_amt)
FROM dbo.cpcode INNER JOIN dbo.tsjh ON dbo.cpcode.code = dbo.tsjh.cpcode CROSS JOIN dbo.history_tsjh_yts
WHERE (dbo.cpcode.swcode = '44070100')我用这个视图做出来的SQL查询的结果数值是正确数的好几倍......
好像有很多重复数据加进去了郁闷高手给个正确的写法谢谢,..

你给的结果对于你的测试数据来说是不对的吧？---测试数据---
if object_id('[ta]') is not null drop table [ta]
go
create table [ta]([cpcode] bigint,[sb_ym] varchar(10),[op_date] datetime,[ts_amt] numeric(7,2))
insert [ta]
select 4407960103,200812,'2009-01-15 09:37:05.483',1962.69 union all
select 4407960209,200901,'2009-04-08 15:39:58.043',13190.88 union all
select 4407960103,200403,'2004-07-08 15:08:12.357',8626.04 union all
select 4407960459,200902,'2009-04-13 08:35:39.717',12707.50 union all
select 4407960103,200812,'2009-03-11 11:55:04.890',13498.92 union all
select 4407960033,200411,'2005-04-12 15:42:00.170',5241.58 union all
select 4407960103,200811,'2008-12-10 11:39:08.797',9973.94 union all
select 4407962202,200812,'2009-02-24 10:21:34.200',20312.48 union all
select 4407960103,200902,'2009-04-13 08:35:39.717',15000.00 union all
select 4407964081,200812,'2009-03-12 17:11:26.513',13480.99
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([code] bigint,[swcode] int)
insert [tb]
select 4407039901,44070301 union all
select 4407859901,44078501 union all
select 4407960103,44070100 union all
select 4407960459,44070100 union all
select 4407910003,44070100 union all
select 4407910004,44070100 union all
select 4407910005,44070100 union all
select 4407910006,44070100 union all
select 4407910007,44070100 union all
select 4407910008,44070100

---查询---
select
  sum([ts_amt])
from
  ta a,
  tb b
where
  a.[cpcode]=b.[code]
and
  left(a.[sb_ym],4)='2008'
and
  b.swcode='44070100'
---结果-------------------------------------------
25435.55（所影响的行数为 1 行）

---------------------------------
--  Author: liangCK 小梁
---------------------------------

--> 生成测试数据: @tb1
DECLARE @tb1 TABLE (cpcode BIGINT,sb_ym INT,op_date DATETIME,ts_amt NUMERIC(7,2))
INSERT INTO @tb1
SELECT 4407960103,200812,'2009-01-15 09:37:05.483',1962.69 UNION ALL
SELECT 4407960209,200901,'2009-04-08 15:39:58.043',13190.88 UNION ALL
SELECT 4407960103,200403,'2004-07-08 15:08:12.357',8626.04 UNION ALL
SELECT 4407960459,200902,'2009-04-13 08:35:39.717',12707.50 UNION ALL
SELECT 4407960103,200812,'2009-03-11 11:55:04.890',13498.92 UNION ALL
SELECT 4407960033,200411,'2005-04-12 15:42:00.170',5241.58 UNION ALL
SELECT 4407960103,200811,'2008-12-10 11:39:08.797',9973.94 UNION ALL
SELECT 4407962202,200812,'2009-02-24 10:21:34.200',20312.48 UNION ALL
SELECT 4407960103,200902,'2009-04-13 08:35:39.717',15000.00 UNION ALL
SELECT 4407964081,200812,'2009-03-12 17:11:26.513',13480.99

--> 生成测试数据: @tb2
DECLARE @tb2 TABLE (code BIGINT,swcode INT)
INSERT INTO @tb2
SELECT 4407039901,44070301 UNION ALL
SELECT 4407859901,44078501 UNION ALL
SELECT 4407960103,44070100 UNION ALL
SELECT 4407960459,44070100 UNION ALL
SELECT 4407910003,44070100 UNION ALL
SELECT 4407910004,44070100 UNION ALL
SELECT 4407910005,44070100 UNION ALL
SELECT 4407910006,44070100 UNION ALL
SELECT 4407910007,44070100 UNION ALL
SELECT 4407910008,44070100--SQL查询如下:SELECT SUM(A.ts_amt)
FROM @tb1 AS A
    JOIN @tb2 AS B
ON B.code=A.cpcode
WHERE B.swcode=44070100
    AND sb_ym LIKE '2008%'

create table tb1([cpcode] varchar(20), [sb_ym] varchar(6), [op_date] datetime,[ts_amt] float)
insert tb1
select '4407960103','200812','2009-01-15 09:37:05.483',1962.69 union all
select '4407960209','200901','2009-04-08 15:39:58.043',13190.88 union all
select '4407919011','200403','2004-07-08 15:08:12.357',8626.04 union all
select '4407960459','200902','2009-04-13 08:35:39.717',12707.50 union all
select '4407964070','200812','2009-03-11 11:55:04.890',13498.92 union all
select '4407960033','200411','2005-04-12 15:42:00.170',5241.58 union all
select '4407960299','200811','2008-12-10 11:39:08.797',9973.94 union all
select '4407962202','200812','2009-02-24 10:21:34.200',20312.48 union all
select '4407960459','200902','2009-04-13 08:35:39.717',15000.00 union all
select '4407964081','200812','2009-03-12 17:11:26.513',13480.99create table tb2([code] varchar(20),[swcode] varchar(20))
insert tb2
select '4407039901','44070301' union all
select '4407859901','44078501' union all
select '4407910001','44070100' union all
select '4407910002','44070100' union all
select '4407910003','44070100' union all
select '4407910004','44070100' union all
select '4407910005','44070100' union all
select '4407910006','44070100' union all
select '4407910007','44070100' union all
select '4407910008','44070100'
insert tb2
select '4407039901','44070301' union all
select '4407859901','44078501' union all
select '4407960103','44070100' union all
select '4407960459','44070100' union all
select '4407910003','44070100' union all
select '4407910004','44070100' union all
select '4407910005','44070100' union all
select '4407910006','44070100' union all
select '4407910007','44070100' union all
select '4407910008','44070100'--楼主第一次写的数据一个关联也没有，后加的还行。select b.swcode,sum(a.ts_amt) 总金额
from tb1 a join tb2 b on a.cpcode=b.code and left(a.sb_ym,4)<>convert(varchar(4),op_date,120) group by b.swcode
/*
swcode 总金额
-------------------- ----------------------
44070100 1962.69
*/

select b.swcode,sum(a.ts_amt) 总金额
from tb1 a join tb2 b on a.cpcode=b.code and b.swcode = '44070100' and left(a.sb_ym,4)<>convert(varchar(4),op_date,120) group by b.swcode

楼主，这个金额是表一所有金额的总和select
  sum([ts_amt])
from
  ta a,
  tb b
where
  a.[cpcode]=b.[code]
/**
----------------------------------------
61769.09（所影响的行数为 1 行）
**/

select sum(isnull(ts_amt,0))
from ta
where cpcode in (
select code from tb where swcode = '44070100'
)
and sb_ym <>convert(varchar(4),left(year(op_date),4))+convert(varchar(2),left(month(op_date),2))

调试易

SQL语句问题..高手进

解决方案 »