select name, ltrim(sum(case ok when '成功' then 1 else 0 end)*100.0/count(1))+'%' from tb group by name
select name, 成功率, row_number()over(order by 成功率 desc) as AS from ( select name, ltrim(sum(case ok when '成功' then 1 else 0 end)*100.0/count(1))+'%' as 成功率 from tb group by name )t
select name, 成功率, row_number()over(order by 成功率 desc) as [AS] from ( select name, ltrim(sum(case ok when '成功' then 1 else 0 end)*100.0/count(1))+'%' as 成功率 from tb group by name )t
SELECT [NAME] , SUM(CASE WHEN [OK] = N'成功' THEN 1 ELSE 0 END) * 1.0 / COUNT(*) , DENSE_RANK() OVER ( ORDER BY SUM(CASE WHEN [OK] = N'成功' THEN 1 ELSE 0 END) * 1.0 / COUNT(*) DESC ) 名次1 , RANK() OVER ( ORDER BY SUM(CASE WHEN [OK] = N'成功' THEN 1 ELSE 0 END) * 1.0 / COUNT(*) DESC ) 名次2 FROM TB GROUP BY [NAME]
/* 我有表A,里面字段有id,sample_no,sample_name;如下: id sample_no sample_name 1 ZW421100001 黄鹤楼(珍品) 2 ZW421100002 黄鹤楼(红) 3 ZW421100003 黄鹤楼 4 ZW421100004 红金龙(红) 5 ZW421100005 中华 6 ZW421100006 红金龙 希望查询出来结果为 某一品牌系列 出现次数从多到少排序 sample_name 次数 黄鹤楼 3 红金龙 2 中华 1 */ ------------------------------------------------------------------------ go if OBJECT_ID('A')is not null drop table A go create table A( id varchar(2), sample_no varchar(20), sample_name varchar(20) ) go insert A select '1','ZW421100001','黄鹤楼(珍品)' union all select '2','ZW421100002','黄鹤楼(红)' union all select '3','ZW421100003','黄鹤楼' union all select '4','ZW421100004','红金龙(红)' union all select '5','ZW421100005','中华' union all select '6','ZW421100006','红金龙'--------------------------------------------------------------------------- select (case when charindex('(',sample_name)>0 --返回字符'('出现的开始位置。 then left(sample_name,charindex('(',sample_name)-1) else sample_name end) as 品牌名, count(1) 次数 from A group by case when charindex('(',sample_name)>0 then left(sample_name,charindex('(',sample_name)-1) else sample_name end order by count(1) desc --------------------------------------------------------------------------- /* 结果表 品牌名 次数 黄鹤楼 3 红金龙 2 中华 1 */ ---------------------------------------------------------------- /* 表tbl ID NAME OK 1 张三 成功 2 李四 成功 3 张三 失败 4 黎明 成功 5 刘备 成功 6 李四 失败 如何查出每个人的成功率是多少? SQL语句。 */go if OBJECT_ID('tbl') is not null drop table tbl go create table tbl( id varchar(2), name varchar(20), ok varchar(4) check (ok in('成功','失败')) ) go insert tbl select '1','张三','成功' union all select '2','李四','成功' union all select '3','张三','失败' union all select '4','黎明','成功' union all select '5','刘备','失败'------------------------------------------------------- select name as 姓名, left(成功率,charindex('.',CAST(成功率 as varchar))+2)+'%', row_number()over(order by 成功率 desc) as 排名 from ( select name, ltrim(sum(case ok when '成功' then 1 else 0 end)*100.0/count(1))+'%' as 成功率 from tbl group by name )t
/* 表tbl ID NAME OK 1 张三 成功 2 李四 成功 3 张三 失败 4 黎明 成功 5 刘备 成功 6 李四 失败 如何查出每个人的成功率是多少? SQL语句。 */go if OBJECT_ID('tbl') is not null drop table tbl go create table tbl( id varchar(2), name varchar(20), ok varchar(4) check (ok in('成功','失败')) ) go insert tbl select '1','张三','成功' union all select '2','李四','成功' union all select '3','张三','失败' union all select '4','黎明','成功' union all select '5','刘备','失败'------------------------------------------------------- select name as 姓名, left(成功率,charindex('.',CAST(成功率 as varchar))+2)+'%', row_number()over(order by 成功率 desc) as 排名 from ( select name, ltrim(sum(case ok when '成功' then 1 else 0 end)*100.0/count(1))+'%' as 成功率 from tbl group by name )t
create table tb(id int,name nvarchar(20),ok nvarchar(4)) go insert tb select 1,'张三','成功' union all select 2,'李四','成功' union all select 3,'张三','失败' union all select 4,'黎明','成功' union all select 5,'刘备','失败' go select name as 姓名,LTRIM(s)+'%' as 成功率,RANK()over(order by s desc)排名 from( select name,SUM(case when ok='成功' then 100 else 0 end)/COUNT(*)s from tb group by name )t /* 姓名 成功率 排名 -------------------- ------------- -------------------- 黎明 100% 1 李四 100% 1 张三 50% 3 刘备 0% 4(4 行受影响)*/ go drop table tb
name,
ltrim(sum(case ok when '成功' then 1 else 0 end)*100.0/count(1))+'%'
from
tb
group by
name
name,
成功率,
row_number()over(order by 成功率 desc) as AS
from
(
select
name,
ltrim(sum(case ok when '成功' then 1 else 0 end)*100.0/count(1))+'%' as 成功率
from
tb
group by
name
)t
name,
成功率,
row_number()over(order by 成功率 desc) as [AS]
from
(
select
name,
ltrim(sum(case ok when '成功' then 1 else 0 end)*100.0/count(1))+'%' as 成功率
from
tb
group by
name
)t
SUM(CASE WHEN [OK] = N'成功' THEN 1
ELSE 0
END) * 1.0 / COUNT(*) ,
DENSE_RANK() OVER ( ORDER BY SUM(CASE WHEN [OK] = N'成功' THEN 1
ELSE 0
END) * 1.0 / COUNT(*) DESC ) 名次1 ,
RANK() OVER ( ORDER BY SUM(CASE WHEN [OK] = N'成功' THEN 1
ELSE 0
END) * 1.0 / COUNT(*) DESC ) 名次2
FROM TB
GROUP BY [NAME]
我有表A,里面字段有id,sample_no,sample_name;如下:
id sample_no sample_name
1 ZW421100001 黄鹤楼(珍品)
2 ZW421100002 黄鹤楼(红)
3 ZW421100003 黄鹤楼
4 ZW421100004 红金龙(红)
5 ZW421100005 中华
6 ZW421100006 红金龙
希望查询出来结果为 某一品牌系列 出现次数从多到少排序
sample_name 次数
黄鹤楼 3
红金龙 2
中华 1
*/
------------------------------------------------------------------------
go
if OBJECT_ID('A')is not null
drop table A
go
create table A(
id varchar(2),
sample_no varchar(20),
sample_name varchar(20)
)
go
insert A
select '1','ZW421100001','黄鹤楼(珍品)' union all
select '2','ZW421100002','黄鹤楼(红)' union all
select '3','ZW421100003','黄鹤楼' union all
select '4','ZW421100004','红金龙(红)' union all
select '5','ZW421100005','中华' union all
select '6','ZW421100006','红金龙'---------------------------------------------------------------------------
select (case when charindex('(',sample_name)>0 --返回字符'('出现的开始位置。
then left(sample_name,charindex('(',sample_name)-1)
else sample_name end) as 品牌名,
count(1) 次数 from A
group by
case when charindex('(',sample_name)>0
then left(sample_name,charindex('(',sample_name)-1)
else sample_name end
order by count(1) desc
---------------------------------------------------------------------------
/*
结果表
品牌名 次数
黄鹤楼 3
红金龙 2
中华 1
*/
----------------------------------------------------------------
/*
表tbl
ID NAME OK
1 张三 成功
2 李四 成功
3 张三 失败
4 黎明 成功
5 刘备 成功
6 李四 失败
如何查出每个人的成功率是多少? SQL语句。
*/go
if OBJECT_ID('tbl') is not null
drop table tbl
go
create table tbl(
id varchar(2),
name varchar(20),
ok varchar(4) check (ok in('成功','失败'))
)
go
insert tbl
select '1','张三','成功' union all
select '2','李四','成功' union all
select '3','张三','失败' union all
select '4','黎明','成功' union all
select '5','刘备','失败'-------------------------------------------------------
select
name as 姓名,
left(成功率,charindex('.',CAST(成功率 as varchar))+2)+'%',
row_number()over(order by 成功率 desc) as 排名
from
(
select
name,
ltrim(sum(case ok when '成功' then 1 else 0 end)*100.0/count(1))+'%' as 成功率
from
tbl
group by
name
)t
表tbl
ID NAME OK
1 张三 成功
2 李四 成功
3 张三 失败
4 黎明 成功
5 刘备 成功
6 李四 失败
如何查出每个人的成功率是多少? SQL语句。
*/go
if OBJECT_ID('tbl') is not null
drop table tbl
go
create table tbl(
id varchar(2),
name varchar(20),
ok varchar(4) check (ok in('成功','失败'))
)
go
insert tbl
select '1','张三','成功' union all
select '2','李四','成功' union all
select '3','张三','失败' union all
select '4','黎明','成功' union all
select '5','刘备','失败'-------------------------------------------------------
select
name as 姓名,
left(成功率,charindex('.',CAST(成功率 as varchar))+2)+'%',
row_number()over(order by 成功率 desc) as 排名
from
(
select
name,
ltrim(sum(case ok when '成功' then 1 else 0 end)*100.0/count(1))+'%' as 成功率
from
tbl
group by
name
)t
go
insert tb
select 1,'张三','成功' union all
select 2,'李四','成功' union all
select 3,'张三','失败' union all
select 4,'黎明','成功' union all
select 5,'刘备','失败'
go
select name as 姓名,LTRIM(s)+'%' as 成功率,RANK()over(order by s desc)排名 from(
select name,SUM(case when ok='成功' then 100 else 0 end)/COUNT(*)s from tb group by name
)t
/*
姓名 成功率 排名
-------------------- ------------- --------------------
黎明 100% 1
李四 100% 1
张三 50% 3
刘备 0% 4(4 行受影响)*/
go
drop table tb