set nocount on
go
Create table #tmp (name varchar(3),X int,Y int)
go
Insert into #tmp values ('a',1,3)
Insert into #tmp values ('b',0,0)
Insert into #tmp values ('c',0,2)
Insert into #tmp values ('a',1,4)
Insert into #tmp values ('b',1,3)
Insert into #tmp values ('c',0,0)
Insert into #tmp values ('b',1,2)
go
--例子是否有錯?Y值不為0的比例如果得小於60%的話就沒記錄了。(例子中是小於70%的,道理一樣)Select distinct b.name From (
Select name From
(Select name,Sum(case when y=0 then 0 else 1 end) as cnt,count(*) as p,sum(Y) as Pavg From
#tmp tmp group by name ) as ss
where cnt*1.0 /p<0.7 and Pavg*1.0 /cnt >=2) a INNER JOIN #tmp b ON a.name=b.name
where b.x=1
Drop table #tmp
go
Create table #tmp (name varchar(3),X int,Y int)
go
Insert into #tmp values ('a',1,3)
Insert into #tmp values ('b',0,0)
Insert into #tmp values ('c',0,2)
Insert into #tmp values ('a',1,4)
Insert into #tmp values ('b',1,3)
Insert into #tmp values ('c',0,0)
Insert into #tmp values ('b',1,2)
go
--例子是否有錯?Y值不為0的比例如果得小於60%的話就沒記錄了。(例子中是小於70%的,道理一樣)Select distinct b.name From (
Select name From
(Select name,Sum(case when y=0 then 0 else 1 end) as cnt,count(*) as p,sum(Y) as Pavg From
#tmp tmp group by name ) as ss
where cnt*1.0 /p<0.7 and Pavg*1.0 /cnt >=2) a INNER JOIN #tmp b ON a.name=b.name
where b.x=1
Drop table #tmp
select name, avg(y) a,avg (case y when 0 then 0 else 1 end) b from table1 where x=1 group by name
)as n where n.a >2 and n.b >0.6
from mytable t1,
(select name, sum(y) sumY, sum(decode(y,0,0,1)) countYnot0,count(*) countY
from mytable
group by name) t2
where t1.x=t2.x
and t1.x=1
and countYnot0 < countY * 0.6
and sumY >= countY * 2
/不要用除法判断
from mytable t1,
(select name, sum(y) sumY, sum(decode(y,0,0,1)) countYnot0,count(*) countY
from mytable
group by name) t2
where t1.name=t2.name --刚才写错了
and t1.x=1
and countYnot0 < countY * 0.6
and sumY >= countY * 2
/不要用除法判断
go
Insert into tmp values ('a',1,3)
Insert into tmp values ('b',0,0)
Insert into tmp values ('c',0,2)
Insert into tmp values ('a',1,4)
Insert into tmp values ('b',1,3)
Insert into tmp values ('c',0,0)
Insert into tmp values ('b',1,2) select distinct name from tmp t where
(select count(*) from tmp where
x=1 and y<>0 and t.name=name)/(select count(*) from tmp where
t.name=name)>0.6
and
(select avg(y) from tmp where t.y=y)>2
条件也打错了!
select distinct name from tmp t where
(select count(*) from tmp where
x=1 and y<>0 and t.name=name)/(select count(*) from tmp where
t.name=name)>0.6
and
(select avg(y) from tmp where t.name=name and y<>0)>2
from mytable t1,
(select name, sum(y) sumY, sum(decode(y,0,0,1)) countYnot0,count(*) countY
from mytable
group by name) t2
where t1.name=t2.name --刚才写错了
and t1.x=1
and countYnot0 < countY * 0.6
and sumY >= countYnot0 * 2 --经按钮同志提醒,再次改正
为什么"不要用除法判断"??
select name from
(select sum(case y when 0 then 0 else 1 end) as ocount,count(y) as ycount,avg(y) as acount,name from table1 group by [name]) as xtable
where acount>=2 and ocount/ycount<0.6
SQL Server中
Select 1/3 結果是0
Select 1*1.0/3 結果是0.333333
select distinct name from table1 t where
(select count(*) from table1 where
x=1 and y<>0 and t.name=name)/(select count(*) from table1 where
t.name=name)<=0.6
and
(select avg(y) from table1 where t.name=name and y<>0 )>=2
and x=1
请大家看看有无问题!!谢谢
X,Y字段的数据类型是float的话,就没有小数点的问题
(select count(*) from table1 where
x=1 and y<>0 and t.name=name)/(select count(*) from table1 where
t.name=name)<=0.6
and
(select avg(y) from table1 where t.name=name and y<>0 )>=2
and x=1
有错:
改为:
select distinct name from table1 t where
convert(float,(select count(*) from table1 where
x=1 and y<>0 and t.name=name))/(select count(*) from table1 where
t.name=name)<=0.6
and
(select avg(y) from table1 where t.name=name and y<>0 )>=2
and x=1
select distinct name from table1 where x =1 and name in (
select name from table1 where y<>0 group by name having avg(convert(float,y)) >2
)
and name in (
select name from table1 group by name having avg(convert(float,case y when 0 then 0 else 1 end)) < 0.7
)