ELEMENT1 ELEMENT2 ELEMENT3 ELEMENT4 ELEMENT5
1 1 2 1 3
2 2 2 2 1
2 1 3 1 3
1 3 3 2 1
3 3 2 2 3已有表如上现在想得出每个字段中为2的个数ELEMENT1COUNT ELEMENT2COUNT ELEMENT3COUNT ELEMENT4COUNT ELEMENT5COUNT
2 1 3 3 0 请大家帮忙,尽量要高效率,谢谢
1 1 2 1 3
2 2 2 2 1
2 1 3 1 3
1 3 3 2 1
3 3 2 2 3已有表如上现在想得出每个字段中为2的个数ELEMENT1COUNT ELEMENT2COUNT ELEMENT3COUNT ELEMENT4COUNT ELEMENT5COUNT
2 1 3 3 0 请大家帮忙,尽量要高效率,谢谢
ELEMENT1COUNT=sum(case when ELEMENT1=2 then 1 else 0 end ),
ELEMENT2COUNT=sum(case when ELEMENT2=2 then 1 else 0 end ),
ELEMENT3COUNT=sum(case when ELEMENT3=2 then 1 else 0 end ),
ELEMENT4COUNT=sum(case when ELEMENT4=2 then 1 else 0 end ),
ELEMENT5COUNT=sum(case when ELEMENT5=2 then 1 else 0 end )
from tb
sum(case when ELEMENT1=2 then 1 else 0 end) as ELEMENT1COUNT,
sum(case when ELEMENT2=2 then 1 else 0 end) as ELEMENT2COUNT,
sum(case when ELEMENT3=2 then 1 else 0 end) as ELEMENT3COUNT,
sum(case when ELEMENT4=2 then 1 else 0 end) as ELEMENT4COUNT,
sum(case when ELEMENT5=2 then 1 else 0 end) as ELEMENT5COUNT
from tb
(SELECT COUNT(1) WHERE ELEMENT2=2) AS ELEMENT2COUNT ,
(SELECT COUNT(1) WHERE ELEMENT3=2) AS ELEMENT3Count,
(SELECT COUNT(1) WHERE ELEMENT4=2) AS ELEMENT4Count,
(SELECT COUNT(1) WHERE ELEMENT5=2) AS ELEMENT5Count
--> 数据库版本:
--> Microsoft SQL Server 2008 (RTM) - 10.0.1600.22
--> 测试数据:[TB]
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[TB]')
AND type in (N'U'))
DROP TABLE [TB]
GO---->建表
create table [TB]([ELEMENT1] int,[ELEMENT2] int,[ELEMENT3] int,[ELEMENT4] int,[ELEMENT5] int)
insert [TB]
select 1,1,2,1,3 union all
select 2,2,2,2,1 union all
select 2,1,3,1,3 union all
select 1,3,3,2,1 union all
select 3,3,2,2,3
GO--> 查询结果
SELECT sum(case when [ELEMENT1]= 2 then 1 else 0 end ) ,
sum(case when [ELEMENT2]= 2 then 1 else 0 end),
sum(case when [ELEMENT3]= 2 then 1 else 0 end),
sum(case when [ELEMENT4]= 2 then 1 else 0 end ),
sum(case when [ELEMENT5]= 2 then 1 else 0 end )
FROM [TB]
--> 删除表格
--DROP TABLE [TB]
(天下树之多,不必死一棵。) 等 级:
的效率高:
DROP TABLE aCREATE TABLE a
(ELEMENT1 INT,
ELEMENT2 INT,
ELEMENT3 INT,
ELEMENT4 INT,
ELEMENT5 INT)
DECLARE @i INT
SET @i =0
WHILE @i < 1000000
BEGIN
SET @i = @i +1
INSERT INTO a (
ELEMENT1,
ELEMENT2,
ELEMENT3,
ELEMENT4,
ELEMENT5
) VALUES (
/* ELEMENT1 - INT */ 0,
/* ELEMENT2 - INT */ 1,
/* ELEMENT3 - INT */ 2,
/* ELEMENT4 - INT */ 3,
/* ELEMENT5 - INT */ 4 )
ENDGO
DBCC FREEPROCCACHE
GO
DBCC DROPCLEANBUFFERS
GOSELECT (SELECT COUNT(1) FROM a WHERE ELEMENT1=2) AS ELEMENT1Count,
(SELECT COUNT(1) FROM a WHERE ELEMENT2=2) AS ELEMENT2COUNT ,
(SELECT COUNT(1) FROM a WHERE ELEMENT3=2) AS ELEMENT3Count,
(SELECT COUNT(1) FROM a WHERE ELEMENT4=2) AS ELEMENT4Count,
(SELECT COUNT(1) FROM a WHERE ELEMENT5=2) AS ELEMENT5CountGO
DBCC FREEPROCCACHE
GO
DBCC DROPCLEANBUFFERS
GOSELECT sum(case when [ELEMENT1]= 2 then 1 else 0 end ) ,
sum(case when [ELEMENT2]= 2 then 1 else 0 end),
sum(case when [ELEMENT3]= 2 then 1 else 0 end),
sum(case when [ELEMENT4]= 2 then 1 else 0 end ),
sum(case when [ELEMENT5]= 2 then 1 else 0 end )
FROM a