SQL 多表统计(SQL SERVER)(300分)

/*Table :品名表 Owner: &nbsp;WXF*/<br>Create Table Commodity_Table<br>(<br>&nbsp; C_IDSTR &nbsp; &nbsp; &nbsp; &nbsp; DoMain_6 &nbsp;not null,<br>&nbsp; C_Name &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;DoMain_20 not null, <br>&nbsp; Primary key (C_IDSTR)<br>);<br>--------------------------------------------------<br><br>/*Table :类别表 Owner: &nbsp;WXF*/<br>Create Table C_Type<br>(<br>&nbsp; C_TypeID &nbsp; &nbsp; &nbsp; &nbsp; DoMain_6 &nbsp;not null,<br>&nbsp; C_TypeName &nbsp; &nbsp;DoMain_20 not null,<br>&nbsp; C_ParentField &nbsp; &nbsp;DoMain_6 &nbsp;not Null,<br>&nbsp; Primary key (C_TypeID)<br>);<br>-----------------------------------------------<br>/*Table :发出表 &nbsp;SendCoupons_Table*/<br>Create Table SendCoupons_Table<br>(<br>&nbsp;C_ID &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; DoMain_10 not null, &nbsp; &nbsp; &nbsp; &nbsp;/*编号*/<br>&nbsp;C_TypeID &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;DoMain_6 &nbsp;not null, &nbsp; &nbsp; &nbsp; &nbsp;/*类型*/<br>&nbsp;S_SendD &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; DoMain_Date, /*发出日期*/<br>&nbsp;S_FaceValue &nbsp; &nbsp; &nbsp; &nbsp;DoMain_Currencies,/*面值*/<br>&nbsp;S_EndD &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; DoMain_Date, /*截止日期*/<br>&nbsp;S_RecipientsCompany DoMain_50, &nbsp; &nbsp; &nbsp; &nbsp;/*领用单位*/<br>&nbsp;S_Flag &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; DoMain_10, &nbsp; &nbsp; &nbsp; &nbsp;/*记号*/<br>&nbsp;S_Memo &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;DoMain_50 &nbsp; &nbsp; &nbsp; &nbsp;/*备注*/<br>&nbsp;C_IDSTR &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;DoMain_50 &nbsp; &nbsp; &nbsp; &nbsp;/*品名*/ <br>);<br>ALTER TABLE SendCoupons_Table ADD CONSTRAINT PK_SendCoupons_Table PRIMARY KEY (C_ID,C_TypeID);<br>ALTER TABLE SendCoupons_Table ADD CONSTRAINT FK_SendCoupons_Table FOREIGN KEY (C_TypeID) &nbsp;REFERENCES C_Type (C_TypeID)<br>ON DELETE CASCADE<br>ON UPDATE CASCADE;<br>-----------------------------------------------------<br>/*Table :回收表 Owner: &nbsp;WXF*/<br>Create Table RecoveryCoupons_Table<br>(<br>&nbsp;C_ID &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; DoMain_10 not null, &nbsp; &nbsp; &nbsp; &nbsp;/*编号*/<br>&nbsp;C_TypeID &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;DoMain_6 &nbsp;not null, &nbsp; &nbsp; &nbsp; &nbsp;/*类型*/<br>&nbsp;R_SendD &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; DoMain_Date, /*回收日期*/<br>&nbsp;S_Memo &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;DoMain_50 &nbsp; &nbsp; &nbsp; &nbsp;/*备注*/<br>);<br>ALTER TABLE RecoveryCoupons_Table ADD CONSTRAINT PK_RecoveryCoupons_Table PRIMARY KEY (C_ID,C_TypeID);<br>ALTER TABLE RecoveryCoupons_Table ADD CONSTRAINT FK_RecoveryCoupons_Table FOREIGN KEY (C_TypeID) &nbsp;REFERENCES C_Type (C_TypeID)<br><br>********************************************<br>*****************实例数据********************<br>/*Table :类别表 C_Type*/<br>C_TypeID &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;C_TypeName<br>10 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;A类<br>11 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;B类<br>-----------------------------------------<br>/*Table :品名表Commodity_Table*/<br>C_IDSTR &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;C_Name &nbsp;<br>00001 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; A品<br>00002 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; B品<br>-------------------------------------------<br>/*Table :发出表 &nbsp;SendCoupons_Table*/<br>C_ID &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; C_TypeID &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; S_SendD &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; S_FaceValue &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;S_EndD &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; C_IDSTR<br>021010 &nbsp; &nbsp; &nbsp; 10 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2008-05-01 &nbsp; &nbsp; &nbsp; &nbsp;30 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2008-05-20 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 00001<br>021011 &nbsp; &nbsp; &nbsp; 10 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2008-05-01 &nbsp; &nbsp; &nbsp; &nbsp;30 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2008-05-20 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 00001<br>021010 &nbsp; &nbsp; &nbsp; 11 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2008-03-01 &nbsp; &nbsp; &nbsp; &nbsp;20 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2008-05-24 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 00002<br>021011 &nbsp; &nbsp; &nbsp; 11 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2008-03-01 &nbsp; &nbsp; &nbsp; &nbsp;20 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2008-05-24 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 00002<br>--------------------------------------------------------------------------------------------------------------------<br>/*Table :回收表 &nbsp;RecoveryCoupons_Table*/<br>C_ID &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; C_TypeID &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; R_SendD &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;<br>021010 &nbsp; &nbsp; &nbsp; 10 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2008-05-01 &nbsp; &nbsp; &nbsp; <br>021011 &nbsp; &nbsp; &nbsp; 10 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2008-05-01 &nbsp; &nbsp; &nbsp;<br>021010 &nbsp; &nbsp; &nbsp; 11 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2008-03-01 &nbsp;<br>-----------------------------------------------------<br>跟据实例数据及日期条件要实现的统计结果:<br>品名 &nbsp; &nbsp; &nbsp; &nbsp;类别 &nbsp; &nbsp; &nbsp; 面值 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;面值总额 &nbsp; &nbsp; &nbsp; &nbsp; 发出数 &nbsp; &nbsp; &nbsp; &nbsp;回收数 &nbsp; &nbsp; &nbsp; 未回收数(发出数-回收数) <br>&nbsp;A品 &nbsp; &nbsp; &nbsp; &nbsp;A类 &nbsp; &nbsp; &nbsp; &nbsp;30 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 60 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0<br>&nbsp;B品 &nbsp; &nbsp; &nbsp; &nbsp;B类 &nbsp; &nbsp; &nbsp; &nbsp; 20 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;40 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1

看不清楚。<br>每个 &nbsp;C_TypeID 是不是只有一个面值？<br>每个 &nbsp;C_ID 是不是仅发出（回收）一个？

C_ID+C_TypeID 为一个主键 代表一个唯一的值<br>ALTER TABLE SendCoupons_Table ADD CONSTRAINT PK_SendCoupons_Table PRIMARY KEY (C_ID,C_TypeID);

直接晕倒,楼主居然连弄几张形象点的包含数据的表都懒的弄,直接把SQL给....

********************************************<br>*****************实例数据********************<br>下面就是数据呀

select tb1.*,C_Name,C_TypeName,<br>&nbsp; &nbsp; &nbsp; &nbsp;上期发出数,上期发出面值总额,<br>&nbsp; &nbsp; &nbsp; &nbsp;上期收回数,上期收回面值总额,<br>&nbsp; &nbsp; &nbsp; &nbsp;本期发出数,本期发出面值总额,<br>&nbsp; &nbsp; &nbsp; &nbsp;本期收回数,本期收回面值总额,<br>&nbsp; &nbsp; &nbsp; &nbsp;后期发出数,后期发出面值总额,<br>&nbsp; &nbsp; &nbsp; &nbsp;后期收回数,后期收回面值总额 <br>from<br>(select C_TypeId,S_FaceValue,C_IdStr<br>&nbsp;from SendCoupons_Table<br>&nbsp;where 1=1<br>&nbsp;group by C_TypeId,S_FaceValue,C_IdStr) tb1<br>&nbsp;left join<br> (select C_TypeId,S_FaceValue,C_IdStr,<br>&nbsp; &nbsp; &nbsp; &nbsp; Count(*) 上期发出数,sum(S_FaceValue) 上期发出面值总额<br> from SendCoupons_Table<br> where S_SendD&lt;'2007-1-1' &nbsp; <br> group by C_TypeId,S_FaceValue,C_IdStr) tb2<br>&nbsp;on (tb1.C_TypeId=tb2.C_TypeId and tb1.S_FaceValue=tb2.S_FaceValue and tb1.C_IdStr=tb2.C_IdStr)<br>&nbsp;left join<br> (select a.C_TypeId,a.S_FaceValue,a.C_IdStr,<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Count(*) 上期收回数,sum(S_FaceValue) 上期收回面值总额<br> from SendCoupons_Table a,RecoveryCoupons_Table b<br> where R_SendD&lt;'2007-1-1'<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;and a.C_Id=b.C_Id<br> group by a.C_TypeId,a.S_FaceValue,a.C_IdStr) tb3<br>&nbsp;on (tb1.C_TypeId=tb3.C_TypeId and tb1.S_FaceValue=tb3.S_FaceValue and tb1.C_IdStr=tb3.C_IdStr)<br>&nbsp;left join<br> (select C_TypeId,S_FaceValue,C_IdStr,<br>&nbsp; &nbsp; &nbsp; &nbsp; Count(*) 本期发出数,sum(S_FaceValue) 本期发出面值总额<br> from SendCoupons_Table<br> where S_SendD&gt;='2007-1-1'<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; and S_SendD&lt;='2008-1-1'<br> group by C_TypeId,S_FaceValue,C_IdStr) tb4 <br>on (tb1.C_TypeId=tb4.C_TypeId and tb1.S_FaceValue=tb4.S_FaceValue and tb1.C_IdStr=tb4.C_IdStr)<br>left join<br> (select a.C_TypeId,a.S_FaceValue,a.C_IdStr,<br>&nbsp; &nbsp; &nbsp; &nbsp; Count(*) 本期收回数,sum(S_FaceValue) 本期收回面值总额<br> from SendCoupons_Table a,RecoveryCoupons_Table b<br> where S_SendD&gt;='2007-1-1'<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; and S_SendD&lt;='2008-1-1'<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;and a.C_Id=b.C_Id<br> group by a.C_TypeId,a.S_FaceValue,a.C_IdStr) tb5<br>on (tb1.C_TypeId=tb5.C_TypeId and tb1.S_FaceValue=tb5.S_FaceValue and tb1.C_IdStr=tb5.C_IdStr)<br>left join<br> (select C_TypeId,S_FaceValue,C_IdStr,<br>&nbsp; &nbsp; &nbsp; &nbsp; Count(*) 后期发出数,sum(S_FaceValue) 后期发出面值总额<br> from SendCoupons_Table<br> where S_SendD&gt;'2008-1-1'<br> group by C_TypeId,S_FaceValue,C_IdStr) tb6<br>on (tb1.C_TypeId=tb6.C_TypeId and tb1.S_FaceValue=tb6.S_FaceValue and tb1.C_IdStr=tb6.C_IdStr)<br>left join<br> (select a.C_TypeId,a.S_FaceValue,a.C_IdStr,<br>&nbsp; &nbsp; &nbsp; &nbsp; Count(*) 后期收回数,sum(S_FaceValue) 后期收回面值总额<br> from SendCoupons_Table a,RecoveryCoupons_Table b<br> where R_SendD&lt;'2008-1-1'<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; and a.C_Id=b.C_Id<br> group by a.C_TypeId,a.S_FaceValue,a.C_IdStr) tb7<br>on (tb1.C_TypeId=tb7.C_TypeId and tb1.S_FaceValue=tb7.S_FaceValue and tb1.C_IdStr=tb7.C_IdStr)<br>left join<br> C_Type<br>on tb1.C_TypeId=C_Type.C_TypeId<br>left join<br> Commodity_Table<br>on tb1.C_IdStr=Commodity_Table.C_IdStr<br><br>mmo:以上未经测试！且未全部完成！？其中的日期'2007-1-1'和'2008-1-1'为条件值。

头晕!

自己搞定了，谢谢楼上的，虽然没有采用你的SQL，不过你搞出这么一大串，辛苦了！[]

[]