求SQL查询语句
Table1:
IDSCORETEAM
001OKA
002NGA
003OKA
004OKB
005NGB
需要将同一TEAM内SCORE的数量查出来,结果如下
TEAMOK_NUMNG_NUMOK_RATE
A 2 1 66.7%
B 1 1 50%
求查询语句或存储过程,谢谢
[解决办法]
select team,
ok_num=sum(case when score='OK' then 1 else 0 end),
ng_num=sum(case when score='NG' then 1 else 0 end),
ok_rate=sum(case when score='OK' then 1 else 0 end)/count(1)*100.0
from [tb]
group by team
----------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-05-14 13:10:24
-- Version:
-- Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)
--Jun 17 2011 00:54:03
--Copyright (c) Microsoft Corporation
--Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1, v.721)
--
----------------------------
--> 测试数据：[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([ID] varchar(3),[SCORE] varchar(2),[TEAM] varchar(1))
insert [huang]
select '001','OK','A' union all
select '002','NG','A' union all
select '003','OK','A' union all
select '004','OK','B' union all
select '005','NG','B'
--------------开始查询--------------------------
select [team], OK_NUM =SUM(CASE WHEN [team] =[team] AND score='OK' THEN 1 ELSE 0 END ),
NG_NUM =SUM(CASE WHEN [team] =[team] AND score='NG ' THEN 1 ELSE 0 END ),
OK_RATE=CONVERT(CHAR(5),CONVERT(DECIMAL(5,2),(SUM(CASE WHEN [team] =[team] AND score='OK' THEN 1 ELSE 0 END )*1.0/(SUM(CASE WHEN [team] =[team] AND score='OK' THEN 1 ELSE 0 END )+SUM(CASE WHEN [team] =[team] AND score='NG ' THEN 1 ELSE 0 END ))*100)))+'%'
from [huang]
GROUP BY [team]
---------------结果----------------------------
/*
team OK_NUM NG_NUM OK_RATE
---- ----------- ----------- -------
A 2 1 66.67%
B 1 1 50.00%
*/
CREATE TABLE t1
(
id VARCHAR(3),
score VARCHAR(2),
team VARCHAR(1)
)
INSERT INTO t1
SELECT '001','OK','A' UNION ALL
SELECT '002','NG','A' UNION ALL
SELECT '003','OK','A' UNION ALL
SELECT '004','OK','B' UNION ALL
SELECT '005','NG','B'
SELECT * FROM t1
SELECTteam,
SUM(CASE WHEN score='OK' THEN 1 ELSE 0 END) AS OK_NUM,
SUM(CASE WHEN score='NG' THEN 1 ELSE 0 END) AS NG_NUM,
LTRIM(CAST(SUM(CASE WHEN score='OK' THEN 1 ELSE 0 END)*1.0/SUM(1)*100 AS DECIMAL(18,2)))+'%' AS OK_RATE
FROMt1
GROUP BY team
teamOK_NUMNG_NUMOK_RATE
A2166.67%
B1150.00%
---author:viola
---date:2013.05.14
---version:sql server 2008 r2
CREATE table table1
(
ID int not null identity primary key,
Scroe char(2) not null,
Team char(1) not null
)
INSERT INTO table1 VALUES('OK','A')
INSERT INTO table1 VALUES('OK','A')
INSERT INTO table1 VALUES('NG','A')
INSERT INTO table1 VALUES('OK','A')
INSERT INTO table1 VALUES('NG','B')
INSERT INTO table1 VALUES('OK','B')
SELECT Team,Ok=sum(CASE Scroe WHEN 'OK' THEN 1 ELSE 0 END),
NG=sum(CASE Scroe WHEN 'NG' THEN 1 ELSE 0 END),
rate=convert(char(5),convert(decimal(5,2),(sum(CASE Scroe WHEN 'OK' THEN 1 ELSE 0 END)*1.0/COUNT(1))*100))+'%',COUNT(1)
FROM table1
GROUP BY Team
