账号表:Accounts
accountid ,acountName,accountType,accountDesc,BeginAmount,Amount,editedBy,editedDateTime
会计明目表:AccountCode
accountCode,accountCodeName,accountType,,editedBy,editedDateTime
流水记录表:AccountTally
logid,accountid,accountcode,amount,accountDate,Appored,VouchNo,…
![ContractedBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
![ExpandedBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
--视图:
ALTER VIEW [dbo].[uvw_AccountsCollRpt]
AS
SELECT m.accountcodename ,c.accountname, t.amount ,t.accountid,t.accountcode,t.accountdate,m.accounttype
from T_AccountTally t inner join M_accountCode m on t.accountcode= m.accountcode
inner join M_accounts c on c.accountid = t.accountid
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
select * from [uvw_AccountsCollRpt
--存储:
alter procedure [dbo].[usp_AccountsCollRpt]
(@accountType tinyint)
AS
declare @sql varchar(8000)
set @sql = 'select accountname as 帐号名,accountid as 帐号编号'
select @sql = @sql + ' , sum(case accountcodename when ''' + accountcodename + ''' then amount else 0 end) [' + accountcodename + ']'
from (select distinct accountcodename from [uvw_AccountsCollRpt] where accounttype=@accountType) as a
set @sql = @sql + ' from [uvw_AccountsCollRpt] group by accountname,accountid'
exec(@sql)
RETURN
![ContractedBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
![ExpandedBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[usp_AccountsCollRpt]
@accountid nvarchar(300) --必需是nvarchar
-- exec [usp_AccountsCollRpt] '''1101001'',''1101027'',''1102053'''
AS
declare @sql varchar(8000)
set @sql = 'select accountname as 帐号名,accountid as 帐号编号 '
select @sql = @sql + ' , sum(case accountcodename when ''' + accountcodename + ''' then amount else 0 end) [' + accountcodename + ']'
from (select distinct accountcodename from [uvw_AccountsCollRpt] where accounttype=1) as a
set @sql = @sql + ' from [uvw_AccountsCollRpt] where accountid in ( ' +@accountid+' ) group by accountname,accountid'
exec(@sql)
declare @sql1 varchar(8000)
set @sql1 = 'select accountname as 帐号名,accountid as 帐号编号 '
select @sql1 = @sql1 + ' , sum(case accountcodename when ''' + accountcodename + ''' then amount else 0 end) [' + accountcodename + ']'
from (select distinct accountcodename from [uvw_AccountsCollRpt] where accounttype=0) as a
set @sql1 = @sql1 + ' from [uvw_AccountsCollRpt] where accountid in ('+ @accountid+') group by accountname,accountid'
exec(@sql1)
RETURN
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO