求一条超复杂SQL语句。小弟我头疼了。SQL高手来~
求一条超复杂SQL语句。我头疼了。SQL高手来~~！
我有这样几张表：
T_Dish:存放字段：DishNo(菜品编号)，DishName(菜品名),DishClassNo(菜品小类号)
T_Bill_His：存放字段：BillNo(帐单号)，BusinessDate(时间),
T_BuyDish_His:存放字段：BillNo(帐单号),DishNo(菜品编号)，DishName(菜品名),SmallDishPrice(菜品小计）
T_DishClass：存放字段：DishClassNo(菜品小类号),DishClassName(菜品小类名);
S_Bill_His：存放字段：BillType(单据类型),BillDate（单据时间）,BillSumPrice(金额)，Dept2Code(部门编号)
S_FactureDept_DishClass：存放字段：FactureDeptNo(部门编号),DishClassNo（菜品小类号）
通过这些表，这些字段，求出某一段时间内，某类菜品DishClassNo总计消费了多少RMB，这类菜品所对应的部门FactureDeptNo在这段时间内领了多少钱的物品BillSumPrice,领用物品的单据类型BillType=3，然后还有2者之间的差值是多少~
具体要求的表如下：
2007-5-1 至 2007-5-30 之间
__________________________________________________
| 菜品小类 | 卖出金额 | 购买金额 | 毛利润 |
|————————————————————————|
| 湘菜 | 48568.5 | 35620 | 12948.5|
………………………………
…………………………
……………………
…………
如果还需要什么条件字段，请说。。可能是我考虑遗漏了
表的结构不可以更改。。。
SQL高手，，小弟跪求了。。。。。
搞定分再加。。。。谢谢谢谢
不知道的XDJM也请帮顶下，，不尽感激。。。。。
------解决方案--------------------
老蔡 18:33:52
试一下
老蔡 18:33:54
select T_DishClass.DishClassName as 菜品小类, Q_BuyDish_His.卖出金额, Q_Bill_His.购买金额,
Q_BuyDish_His.卖出金额-Q_Bill_His.购买金额 as 毛利润
from (T_DishClass inner join
(select T_Dish.DishClassNo, sum(T_BuyDish_His.SmallDishPrice) as 卖出金额,
from (T_BuyDish_His inner join T_Bill_His on T_BuyDish_His.BillNo=T_Bill_His.BillNo)
inner join T_Dish on T_BuyDish_His.DishNo=T_Dish.DishNo
where T_Bill_His.BusinessDate between '2007-5-1 ' and '2007-5-30 '
group by T_Dish.DishClassNo) as Q_BuyDish_His on T_DishClass.DishClassNo=Q_BuyDish_His.DishClassNo)
inner join
(select DishClassNo, sum(BillSumPrice) as 购买金额
from S_FactureDept_DishClass inner join S_Bill_His on S_FactureDept_DishClass.FactureDeptNo=S_Bill_His.Dept2Code
where BillDate between '2007-5-1 ' and '2007-5-30 '
group by DishClassNo) as Q_Bill_His on T_DishClass.DishClassNo=Q_Bill_His.DishClassNo
老蔡 18:34:41
可以的话俺再去接分
我有这样几张表：
T_Dish:存放字段：DishNo(菜品编号)，DishName(菜品名),DishClassNo(菜品小类号)
T_Bill_His：存放字段：BillNo(帐单号)，BusinessDate(时间),
T_BuyDish_His:存放字段：BillNo(帐单号),DishNo(菜品编号)，DishName(菜品名),SmallDishPrice(菜品小计）
T_DishClass：存放字段：DishClassNo(菜品小类号),DishClassName(菜品小类名);
S_Bill_His：存放字段：BillType(单据类型),BillDate（单据时间）,BillSumPrice(金额)，Dept2Code(部门编号)
S_FactureDept_DishClass：存放字段：FactureDeptNo(部门编号),DishClassNo（菜品小类号）
通过这些表，这些字段，求出某一段时间内，某类菜品DishClassNo总计消费了多少RMB，这类菜品所对应的部门FactureDeptNo在这段时间内领了多少钱的物品BillSumPrice,领用物品的单据类型BillType=3，然后还有2者之间的差值是多少~
具体要求的表如下：
2007-5-1 至 2007-5-30 之间
__________________________________________________
| 菜品小类 | 卖出金额 | 购买金额 | 毛利润 |
|————————————————————————|
| 湘菜 | 48568.5 | 35620 | 12948.5|
………………………………
…………………………
……………………
…………
如果还需要什么条件字段，请说。。可能是我考虑遗漏了
表的结构不可以更改。。。
SQL高手，，小弟跪求了。。。。。
搞定分再加。。。。谢谢谢谢
不知道的XDJM也请帮顶下，，不尽感激。。。。。
------解决方案--------------------
老蔡 18:33:52
试一下
老蔡 18:33:54
select T_DishClass.DishClassName as 菜品小类, Q_BuyDish_His.卖出金额, Q_Bill_His.购买金额,
Q_BuyDish_His.卖出金额-Q_Bill_His.购买金额 as 毛利润
from (T_DishClass inner join
(select T_Dish.DishClassNo, sum(T_BuyDish_His.SmallDishPrice) as 卖出金额,
from (T_BuyDish_His inner join T_Bill_His on T_BuyDish_His.BillNo=T_Bill_His.BillNo)
inner join T_Dish on T_BuyDish_His.DishNo=T_Dish.DishNo
where T_Bill_His.BusinessDate between '2007-5-1 ' and '2007-5-30 '
group by T_Dish.DishClassNo) as Q_BuyDish_His on T_DishClass.DishClassNo=Q_BuyDish_His.DishClassNo)
inner join
(select DishClassNo, sum(BillSumPrice) as 购买金额
from S_FactureDept_DishClass inner join S_Bill_His on S_FactureDept_DishClass.FactureDeptNo=S_Bill_His.Dept2Code
where BillDate between '2007-5-1 ' and '2007-5-30 '
group by DishClassNo) as Q_Bill_His on T_DishClass.DishClassNo=Q_Bill_His.DishClassNo
老蔡 18:34:41
可以的话俺再去接分