求SQL语句~~~在线等

[sinwing]

表B结构如下
Company nvarchar(2) –公司 主健
Agencyid nvarchar(10)—工号 主健
Productid nvarchar(3)—产品号 主健
Wrcvdate int –日期 主健
Fyp decimal(17,6)—首年保费
要求编写sql语句查询2006年8月1日～31日每一天的当月累计 fyp


表C结构如下
Company nvarchar(2) --公司 主健
Agencyid nvarchar(10) --工号 主健
Policy nvarchar(8) --保单号 主健
Crtable nvarchar(4) –险种代码 主健
Life nvarhar(2)
Coverage nvarchar(2)
Rider nvarchar(2)
要求编写SQL语句查询(companycode,policycode,crtable)组合键值重复的记录明细.

[qiule]

学习

[hpx021131]

沙发？

[sp4]

select * from c inner join (select companycode,policycode,crtable from c group by companycode,policycode,crtable having count(1)>1) as d
where c.companycode=d.companycode and c.policycode=d.policycode and c.crtable=d.crtable

[sp4]

第一个：
Wrcvdate int –日期 主健

这个日期是怎么保存的？什么格式？

第2个：
select c.* from c inner join (select companycode,policycode,crtable from c group by companycode,policycode,crtable having count(1)> 1) as d
on c.companycode=d.companycode and c.policycode=d.policycode and c.crtable=d.crtable

[sinwing]

int格式的，就是20060801 和 20060831 这样

[leo_lesley]

第一个不明白是什么意思?

---要求编写SQL语句查询(companycode,policycode,crtable)组合键值重复的记录明细. 
select * from 
表C a 
where (select count(1) from 表C where a.companycode=companycode and a.policycode=policycode and a.crtable=crtable)>1

[sp4]

select Wrcvdate,sum(Fyp) from b where Wrcvdate between 20060801 and 20060831 group by Wrcvdate

[leo_lesley]

---要求编写sql语句查询2006年8月1日～31日每一天的当月累计   fyp 
select Company,Agencyid,Productid,Wrcvdate,
Fyp=(select sum(Fyp) from 表B where a.Company=Company and a.Agencyid=Agencyid and a.Productid=Productid and Wrcvdate<=a.Wrcvdate)
from 表B
where cast(Wrcvdate as datetime) between '2006-8-1' and '2006-08-31'

[sinwing]

第一个月累计的意思是要计算
截止到每一天的月累积

[jackyerror]

For Question #1

select companyID, agencyID, productID,
SUM(fyp) OVER (PARTITION BY companyID, agencyID, productID ORDER BY wrcvDate ROWS UNBOUNDED PRECEDING) AS "FYP_Csum"
FROM table_B
where wrcvDate between '2007-08-01' and '2007-08-31'

For Qeustion #2

select * from table_C a,
(select companycode,policycode,crtable from table_C
group by companycode,policycode,crtable
having count(*) > 1) b
where a.companycode = b.companycode
and a.policycode = b.policycode
and a.crtable = b.crtable


Both they were tested in Teradata V2R6