SELECT P1.cInvStd, P1.chuang,P1.DengGuiR,P1.DengGuiL, P1.ZhuangTai,P1.yigui
FROM (SELECT cInvStd,
sum(CASE P.cInvCCode WHEN '01001' THEN P.fKCQuantity ELSE '' END) AS chuang,
sum(CASE P.cInvCCode WHEN '01002' THEN (case p.cinvjname when '' then P.fKCQuantity ELSE '' END)ELSE '' end) AS DengGuiR,
sum(CASE P.cInvCCode WHEN '01002' THEN (case p.cinvjname when '左灯柜' then P.fKCQuantity ELSE '' END)ELSE '' end) AS DengGuiL,
sum(CASE P.cInvCCode WHEN '01003' THEN P.fKCQuantity ELSE '' END) AS ZhuangTai,
sum(CASE P.cInvCCode WHEN '01004' THEN P.fKCQuantity ELSE '' END) AS yigui
FROM pt_temp AS P where P.cinvccode='01001' or
P.cinvccode='01002' or
P.cinvccode='01003' or
P.cinvccode='01004'
GROUP BY P.cInvStd) AS P1
,我的fKCQuantity字段是数字型的,这样查询出来后就算我ELSE为空的也变成0了,我有什么办法让为空的不要显示0
FROM (SELECT cInvStd,
sum(CASE P.cInvCCode WHEN '01001' THEN P.fKCQuantity ELSE '' END) AS chuang,
sum(CASE P.cInvCCode WHEN '01002' THEN (case p.cinvjname when '' then P.fKCQuantity ELSE '' END)ELSE '' end) AS DengGuiR,
sum(CASE P.cInvCCode WHEN '01002' THEN (case p.cinvjname when '左灯柜' then P.fKCQuantity ELSE '' END)ELSE '' end) AS DengGuiL,
sum(CASE P.cInvCCode WHEN '01003' THEN P.fKCQuantity ELSE '' END) AS ZhuangTai,
sum(CASE P.cInvCCode WHEN '01004' THEN P.fKCQuantity ELSE '' END) AS yigui
FROM pt_temp AS P where P.cinvccode='01001' or
P.cinvccode='01002' or
P.cinvccode='01003' or
P.cinvccode='01004'
GROUP BY P.cInvStd) AS P1
,我的fKCQuantity字段是数字型的,这样查询出来后就算我ELSE为空的也变成0了,我有什么办法让为空的不要显示0
空值應該用null表示,而不用用兩個單引號表示,不知道你是否已經覺查到?我覺得下面語句要改成這樣
..........
sum(CASE P.cInvCCode WHEN '01002' THEN (case p.cinvjname when '' then P.fKCQuantity ELSE '' END)ELSE '' end) AS DengGuiR,
......
改成:
.........
sum(CASE P.cInvCCode WHEN '01002' THEN isnull(p.cinvjname,P.fKCQuantity) ELSE '' end) AS DengGuiR,
.....
就算改成那样,SUM后的值仍然是0啊,它不会为空的
可能是我的意思没有说清楚,我是想当p.cinvccode没有'01001'的时候,chuang 的值为空,而有的时候就为它的实际值,有可能为0.
,当它实际为0的时候,不也将它变为空了吗????????