34,575
社区成员
发帖
与我相关
我的任务
分享
select 收款摘要=case when type=1 then 摘要+'收款' else ''end,
收款金额=case when type=1 then 金额 else 0 end,
付款摘要=case when type=2 then 摘要+'付款' else ''end,
付款金额=case when type=2 then 金额 else 0 end
from BankrollDayReport
/*检查是否存在该表*/
IF OBJECT_ID(N'[tempdb].dbo.[#BankrollDayReport]') IS NOT NULL
DROP TABLE [#BankrollDayReport]
/*创建该表*/
CREATE TABLE [#BankrollDayReport]
(
[Type] varchar(2),
[Summary] varchar(10),
[money] decimal(9,2)
)
/*插入数据*/
INSERT INTO [#BankrollDayReport]
SELECT '01','a','3000'
UNION
SELECT '02','a','4300'
UNION
SELECT '01','b','230'
UNION
SELECT '02','b','3506'
UNION
SELECT '01','c','2366'
UNION
SELECT '02','d','5600'
/*原表数据*/
SELECT * FROM [#BankrollDayReport]
/*查询结果*/
SELECT CASE WHEN [收入金额] IS NOT NULL THEN [收款摘要] ELSE NULL END AS [收款摘要],[收入金额],
CASE WHEN [支出金额] IS NOT NULL THEN [付款摘要] ELSE NULL END AS [付款摘要],[支出金额]
FROM
(
SELECT
[Summary]+'收款' AS [收款摘要],
SUM(CASE [type] WHEN '01' THEN [money] ELSE NULL END) AS [收入金额],
[Summary]+'付款' AS [付款摘要],
SUM(CASE [type] WHEN '02' THEN [money] ELSE NULL END) AS [支出金额]
FROM
[#BankrollDayReport]
GROUP BY [Summary]
) [TTemp]
/*查询结束 销毁表*/
DROP TABLE [#BankrollDayReport]
declare @BankrollDayReport table(Summary varchar(10),money int,type varchar(10))
insert into @BankrollDayReport select '1',10,'01'
insert into @BankrollDayReport select '1',20,'02'
insert into @BankrollDayReport select '2',30,'01'
insert into @BankrollDayReport select '2',40,'02'
select summary,ru=(select sum(money) from @BankrollDayReport where summary=a.summary and type='01'),
summary ,chu=(select sum(money) from @BankrollDayReport where summary=a.summary and type='02')
from @BankrollDayReport a
group by summary
个人认为:这收入和支出应该通过一个字段来关联的,除非数据库里就只有两条记录。那这样的表跟废表也毫无区别了。。
这个收入和支出两条数据没有关联?
仅仅是:
select a.Summary [收入摘要],a.money [收入金额],b.Summary [支出摘要],b.money [支出金额]
from BankrollDayReport a,BankrollDayReport b where a.type='01' and b.type='02'