SELECT vu_xskpd.spbh, --商品编号
sum(case when vu_xskpd.bz1='自提' then gjs end) 自提gjs, --销量
sum(case when vu_xskpd.bz1='代发' then gjs end) 代发gjs, --销量
sum(gjs) gjs,
sum(yje) yje---销售金额
FROM vu_xskpd (nolock), ---销量开票单----视图
tu_spzc (nolock) ----商品注册
WHERE ( vu_xskpd.spbh = tu_spzc.spbh ) and--两表关联
( khbh = 'gdqc001' ) AND ---客户编号
( vu_xskpd.nh = '2003' ) AND---年号
( vu_xskpd.qh = '08' ) ---期号
GROUP BY vu_xskpd.spbh
having (sum(gjs)<>0 or sum(je)<>0)---排除金额为0或销量为0的
ORDER BY vu_xskpd.spbh ASC
sum(case when vu_xskpd.bz1='自提' then gjs end) 自提gjs, --销量
sum(case when vu_xskpd.bz1='代发' then gjs end) 代发gjs, --销量
sum(gjs) gjs,
sum(yje) yje---销售金额
FROM vu_xskpd (nolock), ---销量开票单----视图
tu_spzc (nolock) ----商品注册
WHERE ( vu_xskpd.spbh = tu_spzc.spbh ) and--两表关联
( khbh = 'gdqc001' ) AND ---客户编号
( vu_xskpd.nh = '2003' ) AND---年号
( vu_xskpd.qh = '08' ) ---期号
GROUP BY vu_xskpd.spbh
having (sum(gjs)<>0 or sum(je)<>0)---排除金额为0或销量为0的
ORDER BY vu_xskpd.spbh ASC
解决方案 »
- 如何调用有返回值的存储过程去更新表
- 请我的查询哪里有问题?老提示错误.用星号代替列名就没问题.
- 大家帮我解释一下这个面试题
- 马上给分啦!!!关于随机批量更新的问题!!!
- SQL2000数据库备份问题
- 请问一个查询语句!
- 如何查找本系统中的的数据库?
- 使用jcreator api连接数据库问题:SQL Sever 2000 Driver for JDBC IError establish hing socket 如何解决
- 如何在SQL数据库中对传进来的数值进行小数位数保留???在线急求!
- ▲关于触发器的问题, 如何设置返回值?
- 高分求助:sql6.5升级到sql2000后,存储过程不能用!(最高可以追加到1000分!)
- 这样的SQL语句怎么写???
sum(case when vu_xskpd.bz1='自提' then gjs end) 自提gjs, --自提销量
sum(gjs) gjs,
sum(yje) yje---销售金额
FROM vu_xskpd (nolock), ---销量开票单----视图
tu_spzc (nolock) ----商品注册
WHERE ( vu_xskpd.spbh = tu_spzc.spbh ) and--两表关联
( khbh = 'gdqc001' ) AND ---客户编号
( vu_xskpd.nh = '2003' ) AND---年号
( vu_xskpd.qh = '08' ) ---期号
GROUP BY vu_xskpd.spbh
having (sum(gjs)<>0 or sum(je)<>0)---排除金额为0或销量为0的
ORDER BY vu_xskpd.spbh ASC
sum(gjs) gjs, --销量
sum(case when bzl = '自提' then gjs else 0 end) as 自提销量
sum(yje) yje---销售金额
FROM vu_xskpd (nolock), ---销量开票单----视图
tu_spzc (nolock) ----商品注册
WHERE ( vu_xskpd.spbh = tu_spzc.spbh ) and--两表关联
( khbh = 'gdqc001' ) AND ---客户编号
( vu_xskpd.nh = '2003' ) AND---年号
( vu_xskpd.qh = '08' ) ---期号
GROUP BY vu_xskpd.spbh,
having (sum(gjs)<>0 or sum(je)<>0)---排除金额为0或销量为0的
ORDER BY vu_xskpd.spbh ASC
sum(case when vu_xskpd.bz1='自提' then gjs end) 自提gjs, --自提销量
sum(gjs) gjs,
sum(yje) yje---销售金额
FROM vu_xskpd (nolock), ---销量开票单----视图
tu_spzc (nolock) ----商品注册
WHERE ( vu_xskpd.spbh = tu_spzc.spbh ) and--两表关联
( khbh = 'gdqc001' ) AND ---客户编号
( vu_xskpd.nh = '2003' ) AND---年号
( vu_xskpd.qh = '08' ) ---期号
GROUP BY vu_xskpd.spbh
having (sum(gjs)<>0 or sum(je)<>0)---排除金额为0或销量为0的
ORDER BY vu_xskpd.spbh ASC SELECT vu_xskpd.spbh, --商品编号
sum(case when vu_xskpd.bz1='自提' then gjs end) 自提gjs, --自提销量
sum(gjs) gjs,
sum(yje) yje---销售金额
FROM vu_xskpd (nolock), ---销量开票单----视图
tu_spzc (nolock) ----商品注册
WHERE ( vu_xskpd.spbh = tu_spzc.spbh ) and--两表关联
( khbh = 'gdqc001' ) AND ---客户编号
( vu_xskpd.nh = '2003' ) AND---年号
( vu_xskpd.qh = '08' ) ---期号
GROUP BY vu_xskpd.spbh
having (sum(gjs)<>0 or sum(je)<>0)---排除金额为0或销量为0的
ORDER BY vu_xskpd.spbh ASC
sum(case when vu_xskpd.bz1='自提' then gjs end) 自提,
sum(case when vu_xskpd.bz1='代发' then gjs end) 代发,
sum(yje) yje---销售金额
FROM vu_xskpd (nolock), ---销量开票单----视图
tu_spzc (nolock) ----商品注册
WHERE ( vu_xskpd.spbh = tu_spzc.spbh ) and--两表关联
( khbh = 'gdqc001' ) AND ---客户编号
( vu_xskpd.nh = '2003' ) AND---年号
( vu_xskpd.qh = '08' ) ---期号
GROUP BY vu_xskpd.spbh,
having (sum(gjs)<>0 or sum(je)<>0)---排除金额为0或销量为0的
ORDER BY vu_xskpd.spbh ASC
你看看CASE WHEN 用法就行了
sum(case when vu_xskpd.bz1='自提' then gjs else 0 end) 自提gjs, --销量
sum(case when vu_xskpd.bz1='代发' then gjs else 0 end) 代发gjs, --销量
sum(gjs) gjs,
sum(yje) yje---销售金额
FROM vu_xskpd (nolock), ---销量开票单----视图
tu_spzc (nolock) ----商品注册
WHERE ( vu_xskpd.spbh = tu_spzc.spbh ) and--两表关联
( khbh = 'gdqc001' ) AND ---客户编号
( vu_xskpd.nh = '2003' ) AND---年号
( vu_xskpd.qh = '08' ) ---期号
GROUP BY vu_xskpd.spbh
having (sum(gjs)<>0 or sum(je)<>0)---排除金额为0或销量为0的
ORDER BY vu_xskpd.spbh ASC