IIF a.停用标志=0 select b.id,a.id,sum(准备工时*准备人数),sum(操作工时*操作人数) FROM 表1 a join 表2 b on a.主表id=b.主表id else select b.id,a.id,sum(准备工时*准备人数)=0,sum(操作工时*操作人数) FROM 表1 a join 表2 b on a.主表id=b.主表id endIIF
select b.id,a.id,sum(准备工时*准备人数)=case when 停用标志=0 then sum(准备工时*准备人数)else 0 end,sum(操作工时*操作人数) FROM 表1 a join 表2 b on a.主表id=b.主表id
SELECT ManHaur_CTBL.LingJianID, zbgs = CASE WHEN tingyongbz = 0 THEN SUM(ManHaur_CTBL.LingJianZBGS * ManHaur_CTBL.ZhunBeiRS) ELSE 0 END, SUM(ManHaur_CTBL.LingJianCZGS * ManHaur_CTBL.CaoZuoRS) AS czgs, Manhaur_ZTBL.TingYongBZ FROM ManHaur_CTBL INNER JOIN Manhaur_ZTBL ON ManHaur_CTBL.LingJianID = Manhaur_ZTBL.LingJianID GROUP BY ManHaur_CTBL.LingJianID, Manhaur_ZTBL.TingYongBZ谢谢各位大虾。 如何将他写成视图呢??
create view viewName asSELECT ManHaur_CTBL.LingJianID, zbgs = CASE WHEN tingyongbz = 0 THEN SUM(ManHaur_CTBL.LingJianZBGS * ManHaur_CTBL.ZhunBeiRS) ELSE 0 END, SUM(ManHaur_CTBL.LingJianCZGS * ManHaur_CTBL.CaoZuoRS) AS czgs, Manhaur_ZTBL.TingYongBZ FROM ManHaur_CTBL INNER JOIN Manhaur_ZTBL ON ManHaur_CTBL.LingJianID = Manhaur_ZTBL.LingJianID GROUP BY ManHaur_CTBL.LingJianID, Manhaur_ZTBL.TingYongBZ
create view viewName asSELECT ManHaur_CTBL.LingJianID, zbgs = CASE WHEN tingyongbz = 0 THEN SUM(ManHaur_CTBL.LingJianZBGS * ManHaur_CTBL.ZhunBeiRS) ELSE 0 END, SUM(ManHaur_CTBL.LingJianCZGS * ManHaur_CTBL.CaoZuoRS) AS czgs, Manhaur_ZTBL.TingYongBZ FROM ManHaur_CTBL INNER JOIN Manhaur_ZTBL ON ManHaur_CTBL.LingJianID = Manhaur_ZTBL.LingJianID GROUP BY ManHaur_CTBL.LingJianID, Manhaur_ZTBL.TingYongBZgo
select b.id,a.id,sum(准备工时*准备人数),sum(操作工时*操作人数) FROM 表1 a join 表2 b on a.主表id=b.主表id
else
select b.id,a.id,sum(准备工时*准备人数)=0,sum(操作工时*操作人数) FROM 表1 a join 表2 b on a.主表id=b.主表id
endIIF
zbgs = CASE WHEN tingyongbz = 0 THEN SUM(ManHaur_CTBL.LingJianZBGS * ManHaur_CTBL.ZhunBeiRS)
ELSE 0 END, SUM(ManHaur_CTBL.LingJianCZGS * ManHaur_CTBL.CaoZuoRS)
AS czgs, Manhaur_ZTBL.TingYongBZ
FROM ManHaur_CTBL INNER JOIN
Manhaur_ZTBL ON ManHaur_CTBL.LingJianID = Manhaur_ZTBL.LingJianID
GROUP BY ManHaur_CTBL.LingJianID, Manhaur_ZTBL.TingYongBZ谢谢各位大虾。
如何将他写成视图呢??
asSELECT ManHaur_CTBL.LingJianID,
zbgs = CASE WHEN tingyongbz = 0 THEN SUM(ManHaur_CTBL.LingJianZBGS * ManHaur_CTBL.ZhunBeiRS)
ELSE 0 END, SUM(ManHaur_CTBL.LingJianCZGS * ManHaur_CTBL.CaoZuoRS)
AS czgs, Manhaur_ZTBL.TingYongBZ
FROM ManHaur_CTBL INNER JOIN
Manhaur_ZTBL ON ManHaur_CTBL.LingJianID = Manhaur_ZTBL.LingJianID
GROUP BY ManHaur_CTBL.LingJianID, Manhaur_ZTBL.TingYongBZ
asSELECT ManHaur_CTBL.LingJianID,
zbgs = CASE WHEN tingyongbz = 0 THEN SUM(ManHaur_CTBL.LingJianZBGS * ManHaur_CTBL.ZhunBeiRS)
ELSE 0 END, SUM(ManHaur_CTBL.LingJianCZGS * ManHaur_CTBL.CaoZuoRS)
AS czgs, Manhaur_ZTBL.TingYongBZ
FROM ManHaur_CTBL INNER JOIN
Manhaur_ZTBL ON ManHaur_CTBL.LingJianID = Manhaur_ZTBL.LingJianID
GROUP BY ManHaur_CTBL.LingJianID, Manhaur_ZTBL.TingYongBZgo