ALTER PROCEDURE [dbo].[Proce_Find_OutPutSum_Line_day]
@M01_AreaCode varchar(50),
@M04_WorkSort varchar(50),
@M05_TeamCode varchar(50),
@startyear varchar(50),
@End_year varchar(50),
@M06_StationCode varchar(50),
@P05_PartNumber varchar(50)
AS
BEGIN if(@M01_AreaCode ='')
begin
set @M01_AreaCode=null
end
if(@M04_WorkSort='')
begin
set @M04_WorkSort=null
end if(@M05_TeamCode='')
begin
set @M05_TeamCode=null
end if(@M06_StationCode='')
begin
set @M06_StationCode=null
end
if(@P05_PartNumber='')
begin
set @P05_PartNumber=null
end
select M02_LineCode,sum(P01_Output) as P01_Output,years=year(Currentdate),months=month(Currentdate),days=day(Currentdate)
Into #Output from P01_HourVolume where M01_AreaCode=isnull(@M01_AreaCode,M01_AreaCode)
and M04_WorkSort=isnull(@M04_WorkSort,M04_WorkSort)
and M05_TeamCode=isnull(@M05_TeamCode,M05_TeamCode)
and M06_StationCode=isnull(@M06_StationCode,M06_StationCode)
and P05_PartNumber=isnull(@P05_PartNumber,P05_PartNumber)
and convert(datetime,Currentdate) between convert(datetime,@startyear) and convert(datetime,@End_year)
group by Currentdate,M02_LineCode
declare @sql varchar(max)
set @sql='select Date=(case when years=''10000'' then ''TOTAL'' else ltrim(years)+''-''+ltrim(months)+''-''+ltrim(days) end) '
;with cte as
(
(select top 1000 years,months,M02_LineCode,sum(P01_Output) as P_output,days from #Output group by M02_LineCode,years,months,days order by years,months,days ) union all
(select max(10000) as years,max(10000) months,M02_LineCode,sum(P01_Output) as P_output,max(10000)as days from #Output group by M02_LineCode)
)select *,(select sum(P_output) from cte where years=t.years and months=t.months and days=t.days) as Total Into #cte from cte as t ;with cte1 as
(
select M02_LineCode from #Output group by M02_LineCode
)select @sql = @sql + ' ,sum(case M02_LineCode when ''' + M02_LineCode + ''' then P_output else NULL end) [' + M02_LineCode + ']' from cte1 order by M02_LineCode
select @sql = @sql + ' ,Total from #cte as t group by years,months,days,Total order by years,months,days' exec (@sql) drop table #Output
END
解决方案 »
- 正则删除含不合格值的行
- sql server 2005 连接问题
- 关于临时表的问题`
- 一个类似bbs的message功能,如果系统要向所有用户发信息有两种方法,一是一个用户增加一条记录,这样实现很方便,缺点是太浪费数据库资源.
- sql语句求和问题
- SQL 数据库 查询的若干问题
- SQL问题!看看我的错误在哪里?谢谢
- 谁能进来解释一下这句SQL的意思
- 请教大伙一个问题,在VB中实现将SQL SERVER的查询结果插入ACCESS中!用LINKED SERVER
- 这条sql语句怎么写(vb版里没解决,到这试试)
- SQL Server Profiler如何实现自动运行,定期清除的功能?
- 在线求 sql server 2005从sp3升级到sp4 出错。。。
编号 时间 出行方式 部门 总钱数
1 2013-12-16 1 1 100
1 2013-11-14 2 1 200
1 2013-12-15 3 3 100
1 2013-11-16 1 4 300
1 2013-12-17 4 2 100
1 2013-10-18 1 2 400
1 2013-09-19 1 6 80
1 2013-12-30 3 5 100
1 2013-08-28 1 5 90
...--需求报表
分组方式 出行方式一 出行方式二 出行方式三 出行方式四 总计
部门1 money money money money sum(money)
部门2 money money money money sum(money)
部门3 money money money money sum(money)
部门4 money money money money sum(money)
部门5 money money money money sum(money)
...
合计 sum(money) sum(money) sum(money) sum(money) sum(money)
CREATE TABLE [dbo].[P01_HourVolume](
[id] [int] IDENTITY(1,1) NOT NULL,
[P01_HourVolumeCode] [varchar](50) NOT NULL,
[M05_TeamCode] [varchar](50) NOT NULL,
[M03_WorkMake] [varchar](50) NOT NULL,
[M04_WorkSort] [varchar](10) NOT NULL,
[M00_FamilyCode] [varchar](20) NOT NULL,
[M01_AreaCode] [varchar](50) NOT NULL,
[M02_LineCode] [varchar](50) NOT NULL,
[P05_PartNumber] [varchar](100) NOT NULL,
[M07_ModelTypeCode] [varchar](50) NOT NULL,
[M06_StationCode] [varchar](50) NOT NULL,
[P01_HumanCount] [numeric](10, 2) NULL,
[P01_PRT] [numeric](10, 2) NULL,
[P01_Output] [numeric](10, 0) NULL,
[P01_Scrap] [numeric](10, 0) NULL,
[NetworkPoints] [varchar](200) NULL,
[StartDT] [varchar](50) NULL,
[Creater] [varchar](80) NULL,
[EndDt] [varchar](50) NULL,
[Updater] [varchar](80) NULL,
[OperaFlag] [varchar](6) NULL,
[Currentdate] [varchar](50) NULL,
[isend] [varchar](50) NULL,
[iskey] [int] NULL
)
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-12-16 11:25:48
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
-- Dec 28 2012 20:23:12
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据:[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([编号] int,[时间] datetime,[出行方式] int,[部门] int,[总钱数] int)
insert [huang]
select 1,'2013-12-16',1,1,100 union all
select 1,'2013-11-14',2,1,200 union all
select 1,'2013-12-15',3,3,100 union all
select 1,'2013-11-16',1,4,300 union all
select 1,'2013-12-17',4,2,100 union all
select 1,'2013-10-18',1,2,400 union all
select 1,'2013-09-19',1,6,80 union all
select 1,'2013-12-30',3,5,100 union all
select 1,'2013-08-28',1,5,90
--------------开始查询--------------------------declare @s nvarchar(4000)
set @s=''
Select @s=@s+','+quotename('出行方式'+CONVERT(VARCHAR(2),[出行方式]))+'=sum(case when [出行方式]='+quotename([出行方式],'''')+' then [总钱数] else 0 end)'
from [huang] group by [出行方式]
exec('select ''部门''+convert(varchar(2),[部门]) 分组方式'+@s+' from [huang] group by [部门]')
----------------结果----------------------------
/*
分组方式 出行方式1 出行方式2 出行方式3 出行方式4
------ ----------- ----------- ----------- -----------
部门1 100 200 0 0
部门2 400 0 0 100
部门3 0 0 100 0
部门4 300 0 0 0
部门5 90 0 100 0
部门6 80 0 0 0
*/
--把要我要行转列与要显示的字段查询出来放到临时表
select M02_LineCode,sum(P01_Output) as P01_Output,years=year(Currentdate),months=month(Currentdate),days=day(Currentdate)
Into #Output from P01_HourVolume where M01_AreaCode=isnull(@M01_AreaCode,M01_AreaCode) --查询条件
and M04_WorkSort=isnull(@M04_WorkSort,M04_WorkSort)
and M05_TeamCode=isnull(@M05_TeamCode,M05_TeamCode)
and M06_StationCode=isnull(@M06_StationCode,M06_StationCode)
and P05_PartNumber=isnull(@P05_PartNumber,P05_PartNumber)
and convert(datetime,Currentdate) between convert(datetime,@startyear) and convert(datetime,@End_year)
group by Currentdate,M02_LineCode --分组
declare @sql varchar(max) --把总年份设置为一万,方便后面排序
set @sql='select Date=(case when years=''10000'' then ''TOTAL'' else ltrim(years)+''-''+ltrim(months)+''-''+ltrim(days) end) '
;with cte as
(
(select top 1000 years,months,M02_LineCode,sum(P01_Output) as P_output,days from #Output group by M02_LineCode,years,months,days order by years,months,days ) union all
(select max(10000) as years,max(10000) months,M02_LineCode,sum(P01_Output) as P_output,max(10000)as days from #Output group by M02_LineCode) --上面一条单条的,下面一条查询总的 统计每列总的
)select *,(select sum(P_output) from cte where years=t.years and months=t.months and days=t.days) as Total Into #cte from cte as t --统计每行总的 ;with cte1 as
(
select M02_LineCode from #Output group by M02_LineCode --把要实现的行转列的名称在cte中查询出来
)select @sql = @sql + ' ,sum(case M02_LineCode when ''' + M02_LineCode + ''' then P_output else NULL end) [' + M02_LineCode + ']' from cte1 order by M02_LineCode
select @sql = @sql + ' ,Total from #cte as t group by years,months,days,Total order by years,months,days'
--用动态语句实现行转列 exec (@sql)
create table 数据表(
编号 int,
时间 datetime,
出行方式 int,
部门 int,
总钱数 int
)insert into 数据表
select 1 ,'2013-12-16', 1, 1, 100 union all
select 1 ,'2013-11-14', 2, 1, 200 union all
select 1 ,'2013-12-15', 3, 3, 100 union all
select 1 ,'2013-11-16', 1, 4, 300 union all
select 1 ,'2013-12-17', 4, 2, 100 union all
select 1 ,'2013-10-18', 1, 2, 400 union all
select 1 ,'2013-09-19', 1, 6, 80 union all
select 1 ,'2013-12-30', 3, 5, 100 union all
select 1 ,'2013-08-28', 1, 5, 90
go
declare @sql nvarchar(max);
set @sql = '';
select
@sql = @sql + ',sum(case when 出行方式 = '+cast(出行方式 as varchar)+' then 总钱数 else 0 end) as 出行方式' +
cast(出行方式 as varchar)
from 数据表
group by 出行方式
select @sql = 'select ''部门''+cast(部门 as varchar) as 部门' + @sql +
' from 数据表' +
' group by ''部门''+cast(部门 as varchar)'
--select @sql
exec(@sql)
/*
部门 出行方式1 出行方式2 出行方式3 出行方式4
部门1 100 200 0 0
部门2 400 0 0 100
部门3 0 0 100 0
部门4 300 0 0 0
部门5 90 0 100 0
部门6 80 0 0 0*/
可以用一条语句直接查询出来了吗 项目框架下不好用你的出行方式的值,有多少种呢,还是不确定的出行方式就4中 是确定的这样:create table 数据表(
编号 int,
时间 datetime,
出行方式 int,
部门 int,
总钱数 int
)insert into 数据表
select 1 ,'2013-12-16', 1, 1, 100 union all
select 1 ,'2013-11-14', 2, 1, 200 union all
select 1 ,'2013-12-15', 3, 3, 100 union all
select 1 ,'2013-11-16', 1, 4, 300 union all
select 1 ,'2013-12-17', 4, 2, 100 union all
select 1 ,'2013-10-18', 1, 2, 400 union all
select 1 ,'2013-09-19', 1, 6, 80 union all
select 1 ,'2013-12-30', 3, 5, 100 union all
select 1 ,'2013-08-28', 1, 5, 90
goselect '部门'+cast(部门 as varchar) as 部门,
sum(case when 出行方式 = 1 then 总钱数 else 0 end) as 出行方式1,
sum(case when 出行方式 = 2 then 总钱数 else 0 end) as 出行方式2,
sum(case when 出行方式 = 3 then 总钱数 else 0 end) as 出行方式3,
sum(case when 出行方式 = 4 then 总钱数 else 0 end) as 出行方式4
from 数据表
group by '部门'+cast(部门 as varchar)
/*
部门 出行方式1 出行方式2 出行方式3 出行方式4
部门1 100 200 0 0
部门2 400 0 0 100
部门3 0 0 100 0
部门4 300 0 0 0
部门5 90 0 100 0
部门6 80 0 0 0
*/
可以用一条语句直接查询出来了吗 项目框架下不好用你的出行方式的值,有多少种呢,还是不确定的出行方式就4中 是确定的这样:create table 数据表(
编号 int,
时间 datetime,
出行方式 int,
部门 int,
总钱数 int
)insert into 数据表
select 1 ,'2013-12-16', 1, 1, 100 union all
select 1 ,'2013-11-14', 2, 1, 200 union all
select 1 ,'2013-12-15', 3, 3, 100 union all
select 1 ,'2013-11-16', 1, 4, 300 union all
select 1 ,'2013-12-17', 4, 2, 100 union all
select 1 ,'2013-10-18', 1, 2, 400 union all
select 1 ,'2013-09-19', 1, 6, 80 union all
select 1 ,'2013-12-30', 3, 5, 100 union all
select 1 ,'2013-08-28', 1, 5, 90
goselect '部门'+cast(部门 as varchar) as 部门,
sum(case when 出行方式 = 1 then 总钱数 else 0 end) as 出行方式1,
sum(case when 出行方式 = 2 then 总钱数 else 0 end) as 出行方式2,
sum(case when 出行方式 = 3 then 总钱数 else 0 end) as 出行方式3,
sum(case when 出行方式 = 4 then 总钱数 else 0 end) as 出行方式4
from 数据表
group by '部门'+cast(部门 as varchar)
/*
部门 出行方式1 出行方式2 出行方式3 出行方式4
部门1 100 200 0 0
部门2 400 0 0 100
部门3 0 0 100 0
部门4 300 0 0 0
部门5 90 0 100 0
部门6 80 0 0 0
*/
这个好用 呵呵 能在加上一列显示总钱数不 统计每个部门的总钱数
可以用一条语句直接查询出来了吗 项目框架下不好用你的出行方式的值,有多少种呢,还是不确定的出行方式就4中 是确定的这样:create table 数据表(
编号 int,
时间 datetime,
出行方式 int,
部门 int,
总钱数 int
)insert into 数据表
select 1 ,'2013-12-16', 1, 1, 100 union all
select 1 ,'2013-11-14', 2, 1, 200 union all
select 1 ,'2013-12-15', 3, 3, 100 union all
select 1 ,'2013-11-16', 1, 4, 300 union all
select 1 ,'2013-12-17', 4, 2, 100 union all
select 1 ,'2013-10-18', 1, 2, 400 union all
select 1 ,'2013-09-19', 1, 6, 80 union all
select 1 ,'2013-12-30', 3, 5, 100 union all
select 1 ,'2013-08-28', 1, 5, 90
goselect '部门'+cast(部门 as varchar) as 部门,
sum(case when 出行方式 = 1 then 总钱数 else 0 end) as 出行方式1,
sum(case when 出行方式 = 2 then 总钱数 else 0 end) as 出行方式2,
sum(case when 出行方式 = 3 then 总钱数 else 0 end) as 出行方式3,
sum(case when 出行方式 = 4 then 总钱数 else 0 end) as 出行方式4
from 数据表
group by '部门'+cast(部门 as varchar)
/*
部门 出行方式1 出行方式2 出行方式3 出行方式4
部门1 100 200 0 0
部门2 400 0 0 100
部门3 0 0 100 0
部门4 300 0 0 0
部门5 90 0 100 0
部门6 80 0 0 0
*/
这个好用 呵呵 能在加上一列显示总钱数不 统计每个部门的总钱数
这样:select '部门'+cast(部门 as varchar) as 部门,
sum(case when 出行方式 = 1 then 总钱数 else 0 end) as 出行方式1,
sum(case when 出行方式 = 2 then 总钱数 else 0 end) as 出行方式2,
sum(case when 出行方式 = 3 then 总钱数 else 0 end) as 出行方式3,
sum(case when 出行方式 = 4 then 总钱数 else 0 end) as 出行方式4,
SUM(总钱数) as 总计
from 数据表
group by '部门'+cast(部门 as varchar)
/*
部门 出行方式1 出行方式2 出行方式3 出行方式4 总计
部门1 100 200 0 0 300
部门2 400 0 0 100 500
部门3 0 0 100 0 100
部门4 300 0 0 0 300
部门5 90 0 100 0 190
部门6 80 0 0 0 80
*/
可以用一条语句直接查询出来了吗 项目框架下不好用你的出行方式的值,有多少种呢,还是不确定的出行方式就4中 是确定的这样:create table 数据表(
编号 int,
时间 datetime,
出行方式 int,
部门 int,
总钱数 int
)insert into 数据表
select 1 ,'2013-12-16', 1, 1, 100 union all
select 1 ,'2013-11-14', 2, 1, 200 union all
select 1 ,'2013-12-15', 3, 3, 100 union all
select 1 ,'2013-11-16', 1, 4, 300 union all
select 1 ,'2013-12-17', 4, 2, 100 union all
select 1 ,'2013-10-18', 1, 2, 400 union all
select 1 ,'2013-09-19', 1, 6, 80 union all
select 1 ,'2013-12-30', 3, 5, 100 union all
select 1 ,'2013-08-28', 1, 5, 90
goselect '部门'+cast(部门 as varchar) as 部门,
sum(case when 出行方式 = 1 then 总钱数 else 0 end) as 出行方式1,
sum(case when 出行方式 = 2 then 总钱数 else 0 end) as 出行方式2,
sum(case when 出行方式 = 3 then 总钱数 else 0 end) as 出行方式3,
sum(case when 出行方式 = 4 then 总钱数 else 0 end) as 出行方式4
from 数据表
group by '部门'+cast(部门 as varchar)
/*
部门 出行方式1 出行方式2 出行方式3 出行方式4
部门1 100 200 0 0
部门2 400 0 0 100
部门3 0 0 100 0
部门4 300 0 0 0
部门5 90 0 100 0
部门6 80 0 0 0
*/
这个好用 呵呵 能在加上一列显示总钱数不 统计每个部门的总钱数
这样:select '部门'+cast(部门 as varchar) as 部门,
sum(case when 出行方式 = 1 then 总钱数 else 0 end) as 出行方式1,
sum(case when 出行方式 = 2 then 总钱数 else 0 end) as 出行方式2,
sum(case when 出行方式 = 3 then 总钱数 else 0 end) as 出行方式3,
sum(case when 出行方式 = 4 then 总钱数 else 0 end) as 出行方式4,
SUM(总钱数) as 总计
from 数据表
group by '部门'+cast(部门 as varchar)
/*
部门 出行方式1 出行方式2 出行方式3 出行方式4 总计
部门1 100 200 0 0 300
部门2 400 0 0 100 500
部门3 0 0 100 0 100
部门4 300 0 0 0 300
部门5 90 0 100 0 190
部门6 80 0 0 0 80
*/
sum(总钱数) as 总数 总钱数应该是每个部门四中出行方式的总和
select '部门'+cast(部门 as varchar) as 部门,
sum(case when 出行方式 = 1 then 总钱数 else 0 end) as 出行方式1,
sum(case when 出行方式 = 2 then 总钱数 else 0 end) as 出行方式2,
sum(case when 出行方式 = 3 then 总钱数 else 0 end) as 出行方式3,
sum(case when 出行方式 = 4 then 总钱数 else 0 end) as 出行方式4,
SUM(总钱数) as 总计
from 数据表
group by '部门'+cast(部门 as varchar)
/*
部门 出行方式1 出行方式2 出行方式3 出行方式4 总计
部门1 100 200 0 0 300
部门2 400 0 0 100 500
部门3 0 0 100 0 100
部门4 300 0 0 0 300
部门5 90 0 100 0 190
部门6 80 0 0 0 80
*/