22,209
社区成员
发帖
与我相关
我的任务
分享
ID HomeLine icapLine AwayLine Status
10008904 0.76 -1.75 1.10 1
10008904 1.07 -2 0.79 2
10008904 0.95 -1.5 1.32 3
10008905 0.76 1.75 1.10 1
10008905 0.98 1.5 1.32 2
10008906 0.76 1.75 1.10 1
10008906 1.07 0 0.79 2
10008906 0.98 1.5 0.32 3
10008907 0.97 -1.75 0.10 1
10008907 1.07 0 2.79 2
10008907 0.98 1.5 0.32 3
--如果icapLine的值同时小于0,那么任意两个HomeLine的值大于0.96则满足
--如果icapLine的值同时大于0,那么任意两个AwayLine的值大于0.96则满足
--如果icapLine的其中某一个值等于0 那么HomeLine 与 AwayLine 相比较,值小的 + (icapLine其中某一个值大于0,所对应的AwayLine大于0。96 or icapLine其中某一个值小于0,所对应的HomeLine大于0。96) 满足
--如果icapLine的其中某一个值等于0 那么HomeLine 与 AwayLine 相比较,值小的 + (icapLine其中某一个值小于0,所对应的HomeLine大于0。96 or icapLine其中某一个值大于0,所对应的AwayLine大于0。96) 满足
--还有个条件根据需要自己改改,太绕口了
--类似这样,自己再改改
select [ID] from [tb] t group by [ID]
having (sum(case when [icapLine]<0 then 1 else 0 end)=count(1)
and sum(case when [HomeLine]>0.96 then 1 else 0 end)>=2) or
(sum(case when [icapLine]>0 then 1 else 0 end)=count(1)
and sum(case when [AwayLine]>0.96 then 1 else 0 end)>=2) or
(sum(case when [icapLine]=0 then 1 else 0 end)>=1 and
case when (select HomeLine from [tb] where id=t.id and [icapLine]=0)>=
(select AwayLine from [tb] where id=t.id and [icapLine]=0) then
(select AwayLine from [tb] where id=t.id and [icapLine]=0) else
(select HomeLine from [tb] where id=t.id and [icapLine]=0) end
+(select min(AwayLine) from [tb] where id=t.id and [icapLine]>0) > 0.96 or
sum(case when [icapLine]=0 then 1 else 0 end)>=1 and
case when (select HomeLine from [tb] where id=t.id and [icapLine]=0)>=
(select AwayLine from [tb] where id=t.id and [icapLine]=0) then
(select AwayLine from [tb] where id=t.id and [icapLine]=0) else
(select HomeLine from [tb] where id=t.id and [icapLine]=0) end
+(select min(HomeLine) from [tb] where id=t.id and [icapLine]<0) > 0.96)
create table tb(ID varchar(10), HomeLine decimal(18,2), icapLine decimal(18,2), AwayLine decimal(18,2), Status int)
insert into tb values('10008904' , 0.76 , -1.75 , 1.10 , 1)
insert into tb values('10008904' , 1.07 , -2 , 0.79 , 2)
insert into tb values('10008904' , 0.95 , -1.5 , 1.32 , 3)
insert into tb values('10008905' , 0.76 , 1.75 , 1.10 , 1)
insert into tb values('10008905' , 0.98 , 1.5 , 1.32 , 2)
insert into tb values('10008906' , 0.76 , 1.75 , 1.10 , 1)
insert into tb values('10008906' , 1.07 , 0 , 0.79 , 2)
insert into tb values('10008906' , 0.98 , 1.5 , 0.32 , 3)
insert into tb values('10008907' , 0.97 , -1.75 , 0.10 , 1)
insert into tb values('10008907' , 1.07 , 0 , 2.79 , 2)
insert into tb values('10008907' , 0.98 , 1.5 , 0.32 , 3)
go
select id from tb where HomeLine > 0.96 and id not in (select distinct ID from tb where icapLine > 0) group by id having count(1) > 1
union all
select id from tb where AwayLine > 0.96 and id not in (select distinct ID from tb where icapLine < 0) group by id having count(1) > 1
drop table tb
/*
id
----------
10008905
(所影响的行数为 1 行)
*/
--类似这样。
select [ID] from [tb] group by [ID]
having (sum(case when [icapLine]<0 then 1 else 0 end)=count(1)
and sum(case when [HomeLine]>0.96 then 1 else 0 end)>=2) or
(sum(case when [icapLine]>0 then 1 else 0 end)=count(1)
and sum(case when [AwayLine]>0.96 then 1 else 0 end)>=2)