表
lid hid max current state
1 2001 8 9 1
2 2001 8 9 0
3 2001 5 7 11 2002 8 9 0
2 2002 8 9 0
3 2002 5 7 0
1 2003 8 9 1
2 2003 8 9 0
3 2003 5 7 1
1 2004 8 9 1
2 2004 8 9 1
3 2004 5 7 1
1 2005 8 9 -1
2 2005 8 9 -1
3 2005 5 7 -1
有1亿条记录要求查询所有 Max>= ? && current >=? (?值可变)
如果Hid 值相同,则取LID最小的值,并统计state= 1的个数 和0和1个数(-1)不计算例按上的数据 Max>=8 && current>=8 则结果如下
1 2001 8 9 1
1 2002 8 9 0
1 2003 8 9 1
1 2004 8 9 1Count = 3 Alls = 4 P = 75% 红色必需要
lid hid max current state
1 2001 8 9 1
2 2001 8 9 0
3 2001 5 7 11 2002 8 9 0
2 2002 8 9 0
3 2002 5 7 0
1 2003 8 9 1
2 2003 8 9 0
3 2003 5 7 1
1 2004 8 9 1
2 2004 8 9 1
3 2004 5 7 1
1 2005 8 9 -1
2 2005 8 9 -1
3 2005 5 7 -1
有1亿条记录要求查询所有 Max>= ? && current >=? (?值可变)
如果Hid 值相同,则取LID最小的值,并统计state= 1的个数 和0和1个数(-1)不计算例按上的数据 Max>=8 && current>=8 则结果如下
1 2001 8 9 1
1 2002 8 9 0
1 2003 8 9 1
1 2004 8 9 1Count = 3 Alls = 4 P = 75% 红色必需要
as
(select ROW_NUMBER() over(partition by hid order by lid) as row_num, *
from test3
where [max] >= 8 and [current] >= 8 and ([state] = 1 or [state] = 0))
select * into #t from tb1 where row_num = 1;select SUM([state]) as [Count], COUNT(*) as Alls, STR(ROUND(SUM([state]) * 100 / COUNT(*), 0)) + '%' as 'P'
from #t;
sum(case when state=1 then 1 else 0 end) as [count]
,count(1) as [count]
,P=Left(sum(case when state=1 then 1 else 0 end)*1.0 /count(1)*1.0*100,5) +'%'
from B a where max>=8 and [current]>=8 AND state>=0
AND not exists ( select 1 from B where a.hid=hid AND a.lid>lid)