需求如下:表A
---------------
jcode CombId
000001 1
000031 1
000031 2通过select jcode from A where CombId=@CombId
可选出jcode
表B
--------------------------
jcode RpID Value
000001 1 300000
000031 2 500000
000001 3 100000
000031 4 200000
表C
-------------------------
jcode RpID date
000001 1 2008-3-1
000031 2 2008-3-1
000001 3 2008-3-2
000031 4 2008-3-2
现在要通过参数@CombId求出jcode在表B Value值的汇总,条件是RpID等于表C的对应jcode的date字段的最大时间我原来想用的语句是:
select sum(Value) from B where jcode in(select jcode from A where CombId=@CombId)
and RpID in(select RpID from C where jcode in(select jcode from A where CombId=@CombId))这样显然是不行的,后面的RpID就乱了,请高手的指教!!!
---------------
jcode CombId
000001 1
000031 1
000031 2通过select jcode from A where CombId=@CombId
可选出jcode
表B
--------------------------
jcode RpID Value
000001 1 300000
000031 2 500000
000001 3 100000
000031 4 200000
表C
-------------------------
jcode RpID date
000001 1 2008-3-1
000031 2 2008-3-1
000001 3 2008-3-2
000031 4 2008-3-2
现在要通过参数@CombId求出jcode在表B Value值的汇总,条件是RpID等于表C的对应jcode的date字段的最大时间我原来想用的语句是:
select sum(Value) from B where jcode in(select jcode from A where CombId=@CombId)
and RpID in(select RpID from C where jcode in(select jcode from A where CombId=@CombId))这样显然是不行的,后面的RpID就乱了,请高手的指教!!!
解决方案 »
- sql server 全文索引查询慢
- select * from sysobjects where name=''与 select name='' from sysobjects
- 关于中文检索的问题.
- 求一SQL(根據當前表列計算的結果選擇輸出列)
- SQL 日期字段值的更新问题
- 疑问?导出的SQL在导入出错?
- 查询视图时增加一列row_number
- 看这个存储过程有错吗?怎么执行有问题?
- 小问题:view是实时反映表格的变动吗?
- 这样的难题谁会解决,难题都是相对而言!急!急!
- 用dts包,把文本导入数据库,怎么判断文本文件中的数据已经在数据库中存在.高手来看看,提供思路的有分.
- cmd sql脚本
from tb b inner join
(select *
from tc c
where not exists(select 1 from tc where jcode = c.jcode and date > a.date)) c
on b.jcode = c.jcode and b.rpid = c.rpid
where exists (select 1 from ta where jcode = b.jcode and CombId=@CombId )
If object_id('ta') is not null
Drop table ta
Go
Create table ta(jcode varchar(6),CombId int)
Go
Insert into ta
select '000001',1 union all
select '000031',1 union all
select '000031',2
Go
-- Test Data: tB
If object_id('tB') is not null
Drop table tB
Go
Create table tB(jcode varchar(6),RpID int,Value int)
Go
Insert into tB
select '000001',1,300000 union all
select '000031',2,500000 union all
select '000001',3,100000 union all
select '000031',4,200000
Go
-- Test Data: tC
If object_id('tC') is not null
Drop table tC
Go
Create table tC(jcode varchar(6),RpID int,date smalldatetime)
Go
Insert into tC
select '000001',1,'2008-3-1' union all
select '000031',2,'2008-3-1' union all
select '000001',3,'2008-3-2' union all
select '000031',4,'2008-3-2'
Go
--Start
select sum(value)
from tb b inner join
(select *
from tc c
where not exists(select 1 from tc where jcode = c.jcode and date > c.date)) c
on b.jcode = c.jcode and b.rpid = c.rpid
where exists (select 1 from ta where jcode = b.jcode and CombId=1 )
--Result:
/*
-----------
300000(所影响的行数为 1 行)*/
--End
FROM TB B
LEFT JOIN (SELECT T.JCODE,T.RPID FROM TC T
WHERE NOT EXISTS(SELECT 1 FROM TC WHERE JCODE = T.JCODE AND DATE > A.DATE)) A
ON B.JCODE = A.JCODE AND B.RPID = A.RPID
WHERE B.JCODE IN (SELECT JCODE FROM TA WHERE COMBID=@COMBID )