一条sql问题!!!(20分)

雨

雨林^-^

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-05-19

• #1

我有一个表
品名 金额 识别字段(1为出货2为退货)
name jin tax

现在我想用一条sql实现
求和金额 按品名分组 在求和分组的同时用出货金额-退货金额
大概是 select name,sum(jin) from table group by name 但我不知道在这同时如何用出货金额-退货金额请高手指点

 

H

hxt33

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-05-19

• #2

select * ,isnull(a.jin1,0)-isnull(b.jin2,0) as abc from (
select name,sum(jin) as jin1 from table where tax='1' group by name ) a left join (select name,sum(jin) as jin2 from table where tax='2' group by name ) b on a.name=b.name

 

清

清新空气

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-05-19

• #3

SELECT name,sum(jin) AS 进, '' AS 出 from table where tax="1" group by name
union all
SELECT name,'' AS 进,sum(jin) AS 出 from table where tax="2" group by name
给分吧

 

Q

qingquan

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-05-19

• #4

select Name, isnull(进,0) as 进,isnull(出,0) as 出 ,(进-出) as 差额
from (
SELECT name,sum(jin) AS, '' AS 出 from table where tax="1" group by name
union all
SELECT name,'' AS 进,sum(jin) AS 出 from table where tax="2" group by name
) ss

 

雨

雨林^-^

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-05-19

• #5

呵,我的意思是能不能不通过子查询实现,因为这样会影响速度的能不能通过一条sql用判断和涵数实现!!!!(呵,如果是用子查询的话我已经实现了,就是因为速度太慢了,请高手指点呀!!!)

 

W

williamlea

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-05-19

• #6

select name,sum(jin*(case tax when 1 then 1.00 else -1.00 end)) from table1 group by name
这样就可以了，name为字符型，jin和tax都为数值型，执行结果完全正确

 

雨

雨林^-^

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-05-20

• #7

to williamlea
tax 是字符型呀这个数据结构是不能改变的,还有没有其他的转换方法呀!!!!!

 

雨

雨林^-^

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-05-20

• #8

呵,已搞顶多谢指点!!!!