〓〓〓〓〓〓 请教SQL语句高手 〓〓〓〓〓〓

[blueskywjs]

SQL = "select DisTinct(c.Tariff_CustomerID),c.Tariff_Customer,c.Tariff_RoomID,a.应缴费用总额,b.实缴费用总额,e.上月欠费额,d.历史欠费 from " & _
"(select Tariff_CustomerID,Tariff_RoomID, sum(Tariff_Money) as 应缴费用总额 from Tariff where Format(Tariff_Date,'YYYYMM') = " & time_month & " group by Tariff_CustomerID,Tariff_RoomID) a," & _
"(select Tariff_CustomerID,Tariff_RoomID, sum(Tariff_Money) as 历史欠费 from Tariff where Tariff_Sign ='0' group by Tariff_CustomerID,Tariff_RoomID) d," & _
"(select Tariff_CustomerID,Tariff_RoomID, sum(Tariff_Money) as 上月欠费额 from Tariff where Tariff_Sign ='0' and format(Tariff_Date,'YYYYMM') = " & time_month & " group by Tariff_CustomerID,Tariff_RoomID) e," & _
"(select Tariff_CustomerID,Tariff_RoomID, sum(Tariff_Money) as 实缴费用总额 from Tariff where Tariff_Sign='1' and Format(Tariff_Date,'YYYYMM') = " & time_month & " group by Tariff_CustomerID,Tariff_RoomID) b, " & _
"Tariff c where c.Tariff_CustomerID=a.Tariff_CustomerID and c.Tariff_RoomID=a.Tariff_RoomID and c.Tariff_CustomerID=b.Tariff_CustomerID and c.Tariff_RoomID=b.Tariff_RoomID and c.Tariff_CustomerID=e.Tariff_CustomerID and c.Tariff_RoomID=e.Tariff_RoomID and c.Tariff_CustomerID=d.Tariff_CustomerID and c.Tariff_RoomID=d.Tariff_RoomID"



请问上述程序错在哪里？

为什么上月欠费额和历史欠费不能显示每个的费用？而显示的是全部的？

[blueskywjs]

up

[blasterboy]

up

[lustful]

SQL = "select DisTinct(c.Tariff_CustomerID),c.Tariff_Customer,c.Tariff_RoomID,a.应缴费用总额,b.实缴费用总额,e.上月欠费额,d.历史欠费 from " & _
"(select Tariff_CustomerID,Tariff_RoomID, sum(Tariff_Money) as 应缴费用总额 from Tariff where Format(Tariff_Date,'YYYYMM') = " & time_month & " group by Tariff_CustomerID,Tariff_RoomID) a," & _
"(select Tariff_CustomerID,Tariff_RoomID, sum(Tariff_Money) as 历史欠费 from Tariff where Tariff_Sign ='0' group by Tariff_CustomerID,Tariff_RoomID) d," & _
"(select Tariff_CustomerID,Tariff_RoomID, sum(Tariff_Money) as 上月欠费额 from Tariff where Tariff_Sign ='0' and format(Tariff_Date,'YYYYMM') = " & time_month & " group by Tariff_CustomerID,Tariff_RoomID) e," & _
"(select Tariff_CustomerID,Tariff_RoomID, sum(Tariff_Money) as 实缴费用总额 from Tariff where Tariff_Sign='1' and Format(Tariff_Date,'YYYYMM') = " & time_month & " group by Tariff_CustomerID,Tariff_RoomID) b, " & _
"Tariff c where c.Tariff_CustomerID*=a.Tariff_CustomerID and c.Tariff_RoomID*=a.Tariff_RoomID and c.Tariff_CustomerID*=b.Tariff_CustomerID and c.Tariff_RoomID*=b.Tariff_RoomID and c.Tariff_CustomerID*=e.Tariff_CustomerID and c.Tariff_RoomID*=e.Tariff_RoomID and c.Tariff_CustomerID*=d.Tariff_CustomerID and c.Tariff_RoomID*=d.Tariff_RoomID"

[fraser01]

如此复杂的SQL语句，当然分开来看还可以，但是要实现一个目标的读取需要这样写SQL语句，这说明了你的数据库设计存在严重的问题。或者说系统设计出了大问题。
建议你重新设计系统数据库。

[blueskywjs]

lustful():

出现错误！

[zhangxiushu]

如此复杂的SQL语句

[lustful]

不会啊，我刚测试过
报什么错？

[blueskywjs]

是不报错，不过查不出数据来

[blueskywjs]

up

[zwbhyy]

重新设计数据库吧

[cyliao]

一条这么长的语句，即使没有错效率也太慢，换存储过程或视图吧，这样清晰效率也高

[blueskywjs]

cyliao(.NET）：

您好，我不会用视图和存储过程，我用的是Access数据库，请问该如何用啊

[DaiLM]

在access中是增加查询