如题
tb1:
s_name s_state
a 开机
b 开机
c 关机
d 开机
e 关机tb2
s_name kaiji guanji
a 1 0
b 1 0
c 0 1
d 1 0
e 0 1
合计 3 2基础表tb1(s_name是不定的),
想实现查询如tb2内容(显示开机和关机的数量,因为这是一天的数据,但查询出来的可能是一段时间开机或关机的数量)
在存储过程中实现,麻烦各位帮忙!
十分感谢
tb1:
s_name s_state
a 开机
b 开机
c 关机
d 开机
e 关机tb2
s_name kaiji guanji
a 1 0
b 1 0
c 0 1
d 1 0
e 0 1
合计 3 2基础表tb1(s_name是不定的),
想实现查询如tb2内容(显示开机和关机的数量,因为这是一天的数据,但查询出来的可能是一段时间开机或关机的数量)
在存储过程中实现,麻烦各位帮忙!
十分感谢
-- Author: happyflystone
-- Date : 2009-02-28 14:23:21
-- Ver: Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86)
-- Apr 14 2006 01:12:25
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
--
-------------------------------------------------------------------------- Test Data: ta
IF OBJECT_ID('ta') IS NOT NULL
DROP TABLE ta
Go
CREATE TABLE ta(s_name NVARCHAR(10),s_state NVARCHAR(2))
Go
INSERT INTO ta
SELECT 'a','开机' UNION ALL
SELECT 'b','开机' UNION ALL
SELECT 'c','关机' UNION ALL
SELECT 'd','开机' UNION ALL
SELECT 'e','关机'
GO
--Start
select ltrim(isnull(s_name,'sum')) as s_name,sum(k) as K,sum(g) as g
from(
SELECT
s_name,
case when s_state = '开机' then 1 else 0 end as k,
case when s_state = '关机' then 1 else 0 end as g
FROM
TA
) b
group by s_name
with rollup--Result:
/*s_name K g
---------- ----------- -----------
a 1 0
b 1 0
c 0 1
d 1 0
e 0 1
sum 3 2*/
--End
as
begin
select
(isnull(s_name,'sum')) as s_name,
sum(case when s_state = '开机' then 1 else 0 end) as k,
sum(case when s_state = '关机' then 1 else 0 end) as g
FROM
TA
group by s_name
with rollup
end
goexec pr_test drop proc pr_test
if object_id('tb')is not null drop table tb
go
create table tb(s_name varchar(10), s_state varchar(10))
insert tb select 'a' , '开机'
insert tb select 'b' , '开机'
insert tb select 'c' , '关机'
insert tb select 'd' , '开机'
insert tb select 'e', '关机'
select s_name,kaiji=case when s_state='开机' then 1 else 0 end ,
guanji=case when s_state='关机' then 1 else 0 end from tb
union all
select '合计',sum(case when s_state='开机' then 1 else 0 end),
sum(case when s_state='关机' then 1 else 0 end) from tb
/*s_name kaiji guanji
---------- ----------- -----------
a 1 0
b 1 0
c 0 1
d 1 0
e 0 1
合计 3 2
*/
insert into @tb1 select 'a','开机'
union all select 'b','开机'
union all select 'c','关机'
union all select 'd','开机'
union all select 'e','关机'
select s_name,开机=sum(case when s_state='开机' then 1 else 0 end),
关机=SUM(case when s_state='关机' then 1 else 0 end)
from @tb1 group by s_name
union all
select s_name='合计',开机,关机 from
(select 开机=sum(case when s_state='开机' then 1 else 0 end),
关机=SUM(case when s_state='关机' then 1 else 0 end)
from @tb1) b
s_name 开机 关机
---------- ----------- -----------
a 1 0
b 1 0
c 0 1
d 1 0
e 0 1
合计 3 2(6 行受影响)
insert into @tb1 select 'a','开机'
union all select 'b','开机'
union all select 'c','关机'
union all select 'd','开机'
union all select 'e','关机'
select s_name,开机=sum(case when s_state='开机' then 1 else 0 end),
关机=SUM(case when s_state='关机' then 1 else 0 end)
from @tb1 group by s_name
union all
select s_name='合计',开机,关机 from
(select 开机=sum(case when s_state='开机' then 1 else 0 end),
关机=SUM(case when s_state='关机' then 1 else 0 end)
from @tb1) b
s_name 开机 关机
---------- ----------- -----------
a 1 0
b 1 0
c 0 1
d 1 0
e 0 1
合计 3 2(6 行受影响)