数据库表如下 交易为当场付款,结款为过了一定时间后偿还的欠款
provider totalprice payment paytype
a 20 未付款 交易
a 30 现金 交易
a 10 电汇 结款
b 10 现金 交易
b 40 未付款 交易
c 20 未付款 交易
b 20 支票 结款
求一sql语句 查到如下所示
供应商 合计
a 10
b 20
c 20计算方法为 合计= 某个供应商的未付款总额 - 某个供应商的结款总额sql查出来的要是一个table,因为要用gredview显示的,请各位前辈多多帮忙!!
provider totalprice payment paytype
a 20 未付款 交易
a 30 现金 交易
a 10 电汇 结款
b 10 现金 交易
b 40 未付款 交易
c 20 未付款 交易
b 20 支票 结款
求一sql语句 查到如下所示
供应商 合计
a 10
b 20
c 20计算方法为 合计= 某个供应商的未付款总额 - 某个供应商的结款总额sql查出来的要是一个table,因为要用gredview显示的,请各位前辈多多帮忙!!
(select provider 供应商,sum(nvl(totalprice,0)) 合计
from tablename where paytype = '结款'
group by provider) a,
(select provider 供应商,sum(nvl(totalprice,0)) 合计
from tablename where paytype = '交易 '
group by provider) b
where a.provider = b.provider
declare @t1 table(provider varchar(20),totalprice int ,payment varchar(20), paytype varchar(20))
insert into @t1 values('a',20,'未付款','交易')
insert into @t1 values('a',30,'现金','交易')
insert into @t1 values('a',10,'电汇','结款')
insert into @t1 values('b',10,'现金','交易')
insert into @t1 values('b',40,'未付款','交易')
insert into @t1 values('c',20,'未付款','交易')
insert into @t1 values('b',20,'支票','结款')
select A.provider , (A.totalprice1 - A.totalprice2) as totalprice from(
select provider,
sum(case payment when '未付款' then totalprice else 0 end) as totalprice1 ,
sum(case paytype when '结款' then totalprice else 0 end) as totalprice2
from @t1
group by provider)A
//用的是 oracle语法 sqlserver的话,你自己在改改那个nvl(如果第一个参数是null,用第二个值替代)select a.providername 供应商,nvl(b.totalprice,0) - nvl(c.totalprice,0) 合计
from tprovider a,
(select provider,sum(nvl(totalprice,0)) totalprice
from tablename where paytype = '结款'
group by provider) b,
(select provider,sum(nvl(totalprice,0)) totalprice
from tablename where paytype = '交易 '
group by provider) c
where a.provider = b.provider(+) and
a.provider = c.provider(+)
select provider,
sum(case payment when '未付款' then totalprice when '电汇' or '支票' then -totalprice else 0 end ) as totalprice1 ,
sum(case paytype when '结款' then totalprice when '电汇' or '支票' then -totalprice else 0 end ) as totalprice2
from TABLE1 group by provider
sum(case payment when '未付款' then totalprice when '电汇' or '支票' then -totalprice else 0 end ) as 合计
from TABLE1 group by provider
select provider as 供应商,
sum(case payment when '未付款' then totalprice when '电汇' then -totalprice when '支票' then -totalprice else 0 end ) as 合计
from t1 group by provider这个测试通过
select provider as 供应商,
sum(case when payment='未付款' then totalprice when payment='电汇' or payment='支票'
then -totalprice else 0 end ) as 合计
from t1 group by provider
sum(case when payment='未付款' then totalprice when paytype='结款' then -totalprice else 0 end ) as 合计
from t1 group by provider这个也行
(
provider varchar(10),
totalprice int,
payment varchar(10),
paytype varchar(10)
)
insert into @T select 'a',20,'未付款','交易'
union all select 'a',30,'现金','交易'
union all select 'a',10,'电汇','结款'
union all select 'b',10,'现金','交易'
union all select 'b',40,'未付款','交易'
union all select 'c',20,'未付款','交易'
union all select 'b',20,'支票','结款'
--合计= 某个供应商的未付款总额 - 某个供应商的结款总额
select provider,
sum(case when payment='未付款' then totalprice else 0 end)-sum(case when paytype='结款' then totalprice else 0 end) '合计'
from @T group by provider
(
provider varchar(10),
totalprice int,
payment varchar(10),
paytype varchar(10)
)
insert into @T select 'a',20,'未付款','交易'
union all select 'a',30,'现金','交易'
union all select 'a',10,'电汇','结款'
union all select 'b',10,'现金','交易'
union all select 'b',40,'未付款','交易'
union all select 'c',20,'未付款','交易'
union all select 'b',20,'支票','结款'
--合计= 某个供应商的未付款总额 - 某个供应商的结款总额
select provider,
sum(case when payment='未付款' then totalprice else 0 end)-sum(case when paytype='结款' then totalprice else 0 end) '合计'
from @T group by provider
provider 合计
---------- -----------
a 10
b 20
c 20
(
provider varchar(10),
totalprice int,
payment varchar(10),
paytype varchar(10)
)
insert into @T select 'a',20,'未付款','交易'
union all select 'a',30,'现金','交易'
union all select 'a',10,'电汇','结款'
union all select 'b',10,'现金','交易'
union all select 'b',40,'未付款','交易'
union all select 'c',20,'未付款','交易'
union all select 'b',20,'支票','结款'
--合计= 某个供应商的未付款总额 - 某个供应商的结款总额
--(1)
select provider '供应商',
sum(case when payment='未付款' then totalprice else 0 end)-sum(case when paytype='结款' then totalprice else 0 end) '合计'
from @T group by provider
--(2)
select provider '供应商',
sum(case when payment='未付款' then totalprice else 0 end)-sum(case when paytype='结款' then totalprice else 0 end) '合计'
from @T group by provider 供应商 合计
---------- -----------
a 10
b 20
c 20(3 行受影响)