drop
procedure
Model_his_mid;
GO
create procedure Model_his_mid(
/**/ /*
* @vColumns 分组的列 (最后一个字段后面一定不要加,)
* @vClause 查询表的条件(没有条件='')
* @vOrderby 查询结果排序的字段 ''为默认排序
* @vTop 取出的行数 ''为全部
*/
@vColumns nvarchar ( 1000 ),
@vClause nvarchar ( 4000 ),
@vOrderby nvarchar ( 100 ),
@vTop nvarchar ( 50 )
)
as
declare @sql nvarchar ( 4000 )
declare @xColumns nvarchar ( 4000 )
declare @vTable nvarchar ( 50 )
declare @vColumnName nvarchar ( 100 )
declare @vSumColName nvarchar ( 100 )
declare @vNum nvarchar ( 50 )
declare @vAllNum nvarchar ( 50 )
declare @vSumOrCount nvarchar ( 50 )
set @xColumns = ''
/**/ /*
* 需要赋值的参数
*
* @vTable 真正查询的表名
* @vColumnName 纵向转横向的列名
* @vSumColName 取合计值的列名
* @vNum 数值的别名
* @vAllNum 每行合计的列名 (如果不要合计则='')
* @vSumOrCount sum或者count
*
*/
set @vTable = ' his_mid_tab '
set @vColumnName = ' hsks '
set @vSumColName = ' qty '
set @vNum = ' num '
set @vAllNum = ' allnum '
set @vSumOrCount = ' sum '
select
@xColumns = @xColumns
+ ' sum(case ' + @vColumnName + ' when ' + char ( 39 ) + [ a ] . [ column ] + char ( 39 ) + ' then ' + @vNum + ' else 0 end) as '''
+ [ a ] . [ column ] + ''' , '
/**/ /*
* 需要比例加入此语句
* + '(' + 'cast(round(sum(case ' + @vColumnName + ' when '+ char(39) + [a].[column] + char(39)+ ' then ' + @vNum + ' else 0 end)*100.00' + '/sum(' + @vNum + '),2) as decimal(10,2))) as ' + [a].[column] + '_i, '
*/
/**/ /**
* 要动态显示列的sql语句 住院 OUTP_OR_INP = '0' 门诊OUTP_OR_INP = '1'
*/
from ( select distinct DEPT_NAME as [ column ] from his_mzkm_tab where OUTP_OR_INP = ' 0 ' ) as a
set @xColumns = substring ( @xColumns , 0 , len ( @xColumns ))
set @sql = ' select '
if ( @vTop <> '' )
set @sql = @sql + ' top ' + @vTop + ' '
set @sql = @sql + @vColumns + ' , ' + @xColumns
if ( @vAllNum <> '' )
set @sql = @sql + ' , sum( ' + @vNum + ' ) as ' + @vAllNum
set @sql = @sql + ' from ( '
+ ' select ' + @vColumns + ' , ' + @vColumnName + ' , ' + @vSumOrCount + ' ( ' + @vSumColName + ' ) as ' + @vNum + ' from ' + @vTable
if ( @vClause <> '' )
set @sql = @sql + ' where ' + @vClause
set @sql = @sql + ' group by ' + @vColumns + ' , ' + @vColumnName
set @sql = @sql + ' ) as casual_table group by ' + @vColumns + ' '
if ( @vOrderby <> '' )
set @sql = @sql + ' order by ' + @vOrderby
print @sql
exec sp_executesql @sql with recompile
/**/ /*
* sql语句 引号之内的为sql中的几个参数分别为(分组的列,条件,排序,取出行数)
* exec Model_his_mid 'kjkm','date = ''8''','kjkm desc',''
*/
/**/ /*
* sql语句
* exec Model_his_mid 'kjkm','','kjkm desc','15'
*/
GO
create procedure Model_his_mid(
/**/ /*
* @vColumns 分组的列 (最后一个字段后面一定不要加,)
* @vClause 查询表的条件(没有条件='')
* @vOrderby 查询结果排序的字段 ''为默认排序
* @vTop 取出的行数 ''为全部
*/
@vColumns nvarchar ( 1000 ),
@vClause nvarchar ( 4000 ),
@vOrderby nvarchar ( 100 ),
@vTop nvarchar ( 50 )
)
as
declare @sql nvarchar ( 4000 )
declare @xColumns nvarchar ( 4000 )
declare @vTable nvarchar ( 50 )
declare @vColumnName nvarchar ( 100 )
declare @vSumColName nvarchar ( 100 )
declare @vNum nvarchar ( 50 )
declare @vAllNum nvarchar ( 50 )
declare @vSumOrCount nvarchar ( 50 )
set @xColumns = ''
/**/ /*
* 需要赋值的参数
*
* @vTable 真正查询的表名
* @vColumnName 纵向转横向的列名
* @vSumColName 取合计值的列名
* @vNum 数值的别名
* @vAllNum 每行合计的列名 (如果不要合计则='')
* @vSumOrCount sum或者count
*
*/
set @vTable = ' his_mid_tab '
set @vColumnName = ' hsks '
set @vSumColName = ' qty '
set @vNum = ' num '
set @vAllNum = ' allnum '
set @vSumOrCount = ' sum '
select
@xColumns = @xColumns
+ ' sum(case ' + @vColumnName + ' when ' + char ( 39 ) + [ a ] . [ column ] + char ( 39 ) + ' then ' + @vNum + ' else 0 end) as '''
+ [ a ] . [ column ] + ''' , '
/**/ /*
* 需要比例加入此语句
* + '(' + 'cast(round(sum(case ' + @vColumnName + ' when '+ char(39) + [a].[column] + char(39)+ ' then ' + @vNum + ' else 0 end)*100.00' + '/sum(' + @vNum + '),2) as decimal(10,2))) as ' + [a].[column] + '_i, '
*/
/**/ /**
* 要动态显示列的sql语句 住院 OUTP_OR_INP = '0' 门诊OUTP_OR_INP = '1'
*/
from ( select distinct DEPT_NAME as [ column ] from his_mzkm_tab where OUTP_OR_INP = ' 0 ' ) as a
set @xColumns = substring ( @xColumns , 0 , len ( @xColumns ))
set @sql = ' select '
if ( @vTop <> '' )
set @sql = @sql + ' top ' + @vTop + ' '
set @sql = @sql + @vColumns + ' , ' + @xColumns
if ( @vAllNum <> '' )
set @sql = @sql + ' , sum( ' + @vNum + ' ) as ' + @vAllNum
set @sql = @sql + ' from ( '
+ ' select ' + @vColumns + ' , ' + @vColumnName + ' , ' + @vSumOrCount + ' ( ' + @vSumColName + ' ) as ' + @vNum + ' from ' + @vTable
if ( @vClause <> '' )
set @sql = @sql + ' where ' + @vClause
set @sql = @sql + ' group by ' + @vColumns + ' , ' + @vColumnName
set @sql = @sql + ' ) as casual_table group by ' + @vColumns + ' '
if ( @vOrderby <> '' )
set @sql = @sql + ' order by ' + @vOrderby
print @sql
exec sp_executesql @sql with recompile
/**/ /*
* sql语句 引号之内的为sql中的几个参数分别为(分组的列,条件,排序,取出行数)
* exec Model_his_mid 'kjkm','date = ''8''','kjkm desc',''
*/
/**/ /*
* sql语句
* exec Model_his_mid 'kjkm','','kjkm desc','15'
*/