现有一个表A,里面有字段companyid,type(type=1,2,3),status(status=维修,停用,使用)
现在要统计成如下格式
-----------------------------------------------------------------------------------
公司 | type=1 | type = 2 | type =3
|总数|维修数|停用数|使用数| 总数|维修数|停用数|使用数 |总数|维修数停用数|使用数
----------------------------------------------------------------------------------
如何使用sql语句?
现在要统计成如下格式
-----------------------------------------------------------------------------------
公司 | type=1 | type = 2 | type =3
|总数|维修数|停用数|使用数| 总数|维修数|停用数|使用数 |总数|维修数停用数|使用数
----------------------------------------------------------------------------------
如何使用sql语句?
sum(case when type=1 then 1 else 0 end) [1_总数],
sum(case when type=1 and status='维修' then 1 else 0 end) [1_维修数],
sum(case when type=1 and status='停用' then 1 else 0 end) [1_停用数],
sum(case when type=1 and status='使用' then 1 else 0 end) [1_使用数],
sum(case when type=2 then 1 else 0 end) [2_总数],
sum(case when type=2 and status='维修' then 1 else 0 end) [2_维修数],
sum(case when type=2 and status='停用' then 1 else 0 end) [2_停用数],
sum(case when type=2 and status='使用' then 1 else 0 end) [2_使用数],
sum(case when type=3 then 1 else 0 end) [3_总数],
sum(case when type=3 and status='维修' then 1 else 0 end) [3_维修数],
sum(case when type=3 and status='停用' then 1 else 0 end) [3_停用数],
sum(case when type=3 and status='使用' then 1 else 0 end) [3_使用数]
from a
group by companyid
来这个帖子,
我帮你问了。
大家都说表头难实现。
有报表工具的。直接拉拉就好了比如fastreport
declare @t table( companyid varchar(30) , [type] int ,[status] varchar(30))
insert into @t values( 'company', 1 , '维修')
insert into @t values( 'company', 1 , '停用')
insert into @t values( 'company', 2 , '停用')
insert into @t values( 'company1', 1 , '停用')
select a.companyid,
sum( case [type] when 1 then a.[总数] else 0 end) as [1总数],
sum( case [type] when 1 then a.[维修数] else 0 end) as [1维修数],
sum( case [type] when 1 then a.[停用数] else 0 end) as [1停用数],
sum( case [type] when 1 then a.[使用数] else 0 end) as [1使用数],
sum( case [type] when 2 then a.[总数] else 0 end) as [2总数],
sum( case [type] when 2 then a.[维修数] else 0 end) as [2维修数],
sum( case [type] when 2 then a.[停用数] else 0 end) as [2停用数],
sum( case [type] when 2 then a.[使用数] else 0 end) as [2使用数],
sum( case [type] when 3 then a.[总数] else 0 end) as [3总数],
sum( case [type] when 3 then a.[维修数] else 0 end) as [3维修数],
sum( case [type] when 3 then a.[停用数] else 0 end) as [3停用数],
sum( case [type] when 3 then a.[使用数] else 0 end) as [3使用数]
from(
select companyid,[type],COUNT(*) as [总数],
SUM( case [status] when '维修' then 1 else 0 end) as [维修数],
SUM( case [status] when '停用' then 1 else 0 end) as [停用数],
SUM( case [status] when '使用' then 1 else 0 end) as [使用数]
from @t group by companyid,[type]) a group by a.companyid
insert into @t values( 'company', 1 , '维修')
insert into @t values( 'company', 1 , '停用')
insert into @t values( 'company', 2 , '停用')
insert into @t values( 'company1', 1 , '停用')
select a.companyid,
sum( case [type] when 1 then a.[总数] else 0 end) as [1总数],
sum( case [type] when 1 then a.[维修数] else 0 end) as [1维修数],
sum( case [type] when 1 then a.[停用数] else 0 end) as [1停用数],
sum( case [type] when 1 then a.[使用数] else 0 end) as [1使用数],
sum( case [type] when 2 then a.[总数] else 0 end) as [2总数],
sum( case [type] when 2 then a.[维修数] else 0 end) as [2维修数],
sum( case [type] when 2 then a.[停用数] else 0 end) as [2停用数],
sum( case [type] when 2 then a.[使用数] else 0 end) as [2使用数],
sum( case [type] when 3 then a.[总数] else 0 end) as [3总数],
sum( case [type] when 3 then a.[维修数] else 0 end) as [3维修数],
sum( case [type] when 3 then a.[停用数] else 0 end) as [3停用数],
sum( case [type] when 3 then a.[使用数] else 0 end) as [3使用数]
from(
select companyid,[type],COUNT(*) as [总数],
SUM( case [status] when '维修' then 1 else 0 end) as [维修数],
SUM( case [status] when '停用' then 1 else 0 end) as [停用数],
SUM( case [status] when '使用' then 1 else 0 end) as [使用数]
from @t group by companyid,[type]) a group by a.companyid顶一个!!!!!!
create table #temp(CompanyId int,[Type] smallint,status nvarchar(10))
insert into #temp
select 1,1,'維護' union all
select 1,1,'停用' union all
select 1,3,'使用' union all
select 2,2,'維護' union all
select 3,1,'維護'
--select * from #temp
--CompanyId Type status
------------- ------ ----------
--1 1 維護
--1 1 停用
--1 3 使用
--2 2 維護
--3 1 維護
--
--(5 個資料列受到影響)with A as(
select companyid,type,status,count([Type]) as Num from #temp group by companyid,type,status
)
select companyid,max(case when type=1 and status='維護' then Num else 0 end) as Type1維護,
max(case when type=1 and status='停用' then Num else 0 end) as Type1停用,
max(case when type=1 and status='使用' then Num else 0 end) as Type1使用,
max(case when type=2 and status='維護' then Num else 0 end) as Type2維護,
max(case when type=2 and status='停用' then Num else 0 end) as Type2停用,
max(case when type=2 and status='使用' then Num else 0 end) as Type2使用,
max(case when type=3 and status='維護' then Num else 0 end) as Type3維護,
max(case when type=3 and status='停用' then Num else 0 end) as Type3停用,
max(case when type=3 and status='使用' then Num else 0 end) as Type3使用
from A group by companyid
--companyid Type1維護 Type1停用 Type1使用 Type2維護 Type2停用 Type2使用 Type3維護 Type3停用 Type3使用
------------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
--1 1 1 0 0 0 0 0 0 1
--2 0 0 0 1 0 0 0 0 0
--3 1 0 0 0 0 0 0 0 0
--
--(3 個資料列受到影響)
如Reporting Service,SSAS+Analyzer2005