查询语句！解决办法_Sql Server

查询语句！
表
lid hid max current state
1 2001 8 9 1
2 2001 8 9 0
3 2001 5 7 1

1 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 1

Count = 3 Alls = 4 P = 75% 红色必需要

------解决方案--------------------

SQL code

with tb1 as(select ROW_NUMBER() over(partition by hid order by lid) as row_num, *from test3where [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;
------解决方案--------------------
SQL codeselect 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>=0AND not exists ( select 1 from B where a.hid=hid AND a.lid>lid)
------解决方案--------------------
探讨

引用:
SQL code
with tb1
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……

------解决方案--------------------
探讨

引用:

引用:
SQL code
with tb1
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 [stat……

------解决方案--------------------
 你创建个存储过程，然后再调用
------解决方案--------------------
1亿条数据，肯定是要超时的，你应该事先查好，创建个中间表什么的，然后从中间表里查才好，否则，只能是超时了。
------解决方案--------------------
直接查询1亿条？有索引吗？最好还是先分区，尽量减少单表数据量后再统计。