FineUI 官方论坛

标题: EF语句问题 [打印本页]
作者: shouzhi2007    时间: 2014-5-7 17:27
标题: EF语句问题
  1. SELECT
  2. S.KC_Bianhao AS 编号,
  3. s.KC_Code AS 物资编码,
  4. s.KC_Name AS 物品名称,
  5. s.KC_Specification AS 规格型号,
  6. s.KC_Unit AS 单位,
  7. s.KC_price AS 单价,
  8. s.KC_InitNum AS 原始库存,
  9. sum(CASE  when KC_InsOuts.num >0 then KC_InsOuts.num else 0 end) as 本月入库,
  10. sum(CASE  when KC_InsOuts.num <0 then KC_InsOuts.num else 0 end) as 本月出库 ,
  11. ( S.KC_InitNum + sum(CASE  when KC_InsOuts.num >0 then KC_InsOuts.num else 0 end) + sum(CASE  when KC_InsOuts.num <0 then KC_InsOuts.num else 0 end)) as 当期库存
  12. ,(s.KC_price * sum(S.KC_InitNum) + sum(KC_InsOuts.num)) AS 总金额
  13. ,s.KC_UpperNumber AS 警戒高线库
  14. ,(( S.KC_InitNum + sum(CASE  when KC_InsOuts.num >0 then KC_InsOuts.num else 0 end) + sum(CASE  when KC_InsOuts.num <0 then KC_InsOuts.num else 0 end))-s.KC_UpperNumber) AS 超储数量
  15. FROM dbo.KC_Spareparts S
  16. JOIN (SELECT [KC_Bianhao]
  17.       
  18.       ,Kc_Ins.[KC_Code]
  19.       ,Kc_Ins.[KC_Name]
  20.       ,Kc_Ins.[KC_Specification]
  21.       ,Kc_Ins.[KC_Unit]
  22.       ,Kc_Ins.[KC_Number] AS num
  23.      
  24.   FROM [AppBox].[dbo].[KC_SparepartsIns] Kc_Ins
  25.   UNION SELECT [KC_Bianhao]
  26.       
  27.       ,Kc_Outs.[KC_Code]
  28.       ,Kc_Outs.[KC_Name]
  29.       ,Kc_Outs.[KC_Specification]
  30.       ,Kc_Outs.[KC_Unit]
  31.       ,Kc_Outs.[KC_Number] * (-1) AS num
  32.       
  33.   FROM [AppBox].[dbo].[KC_SparepartsOuts] Kc_Outs) KC_InsOuts
  34.   ON S.KC_Bianhao = KC_InsOuts.KC_Bianhao

  36.   group BY s.KC_Bianhao ,s.KC_Code,s.KC_Name,s.KC_Specification,s.KC_Unit,s.KC_price,s.KC_InitNum,s.KC_UpperNumber
  37.   
  38.   
复制代码
查询结果如下图，
[attach]5221[/attach]

怎么用EF写出来呢





欢迎光临 FineUI 官方论坛 (https://fineui.com/BBS/) Powered by Discuz! X3.4