Select id from b Having (SUM(Case When ID=1 Then 1 Else 0 End)+0.0)/Count(1) >0.5
select id from b where flag=1 group by id having count(id)*2>(select count(*) from b)
select id from b group by id having sum(case when flag=1 then 1 else 0 end)/count(1)>0.5
改下Select id from b Group By id Having (SUM(Case When Flag=1 Then 1 Else 0 End)+0.0)/Count(1) >=0.5
--建立测试环境 Create Table B(id Int,Flag Int) Insert B Values(1,0) Insert B Values(1,1) Insert B Values(1,1) Insert B Values(2,0) Insert B Values(1,1) Insert B Values(3,1) Insert B Values(3,0) Insert B Values(2,1) Insert B Values(2,0) --测试 Select id from b Group By id Having (SUM(Case When Flag=1 Then 1 Else 0 End)+0.0)/Count(1) >=0.5 --删除测试环境 Drop table B --结果 /* id 1 3 */
不是这样吗?Select id from 用户消费明晰表 Group By id Having (SUM(Case When Flag=1 Then 1 Else 0 End)+0.0)/Count(1) >=0.5
你是要这样的吗、?Select Name from 用户表 Where Id In (Select id from 用户消费明晰表 Group By id Having (SUM(Case When Flag=1 Then 1 Else 0 End)+0.0)/Count(1) >=0.5)
select a.* from 用户表 a where a.id = b.userid and (select count(*) from 用户消费表 b where b.userid = a.id and exists(select 1 from 用户消费明晰表 where id = b.mid and flag = 1)) > (select count(*) from 用户消费表 b where b.userid = a.id and not exists(select 1 from 用户消费明晰表 where id = b.mid and flag = 1))
select a.* from 用户表 a where a.id = b.userid and isnull((select count(*) from 用户消费表 b where b.userid = a.id and exists(select 1 from 用户消费明晰表 where id = b.mid and flag = 1)),0) > isnull((select count(*) from 用户消费表 b where b.userid = a.id and not exists(select 1 from 用户消费明晰表 where id = b.mid and flag = 1)),0)
Select id from b Group By id Having (SUM(Case When Flag=1 Then 1 Else 0 End)+0.0)/Count(1) >=0.5
表a 字段id 表b 字段a.id ,flag a和b是1:N 找出b表中flag=1的记录占总记录数的50%以上的a.id declare @count int set select @count=count(*) from a select AA.id from ( select id from a group by id hvaing count(*)/@count>0.5 ) AA,b where AA.id=b.id and flag=1
select id from( Select id,flag from b group by id,flag Having vag(flag)>0.5) where flag=1
select A.id from( Select id,flag,avg(flag) as [avg] from b group by id,flag) as A where A.flag=1 and [avg]>0.5
group by id
having count(id)*2>(select count(*) from b)
from b
group by id
having sum(case when flag=1 then 1 else 0 end)/count(1)>0.5
Group By id
Having (SUM(Case When Flag=1 Then 1 Else 0 End)+0.0)/Count(1) >=0.5
Create Table B(id Int,Flag Int)
Insert B Values(1,0)
Insert B Values(1,1)
Insert B Values(1,1)
Insert B Values(2,0)
Insert B Values(1,1)
Insert B Values(3,1)
Insert B Values(3,0)
Insert B Values(2,1)
Insert B Values(2,0)
--测试
Select id from b
Group By id
Having (SUM(Case When Flag=1 Then 1 Else 0 End)+0.0)/Count(1) >=0.5
--删除测试环境
Drop table B
--结果
/*
id
1
3
*/
select id
from b
group by id
having sum(case when flag=1 then 1 else 0 end)/count(1)>0.5
--------------------------
这个语句什么都查不出来的。
是用户表(id,name),
用户消费表(id,userid),
用户消费明晰表(id,mid,flag)
userid:用户表id
mid:消费表id
flag:打折标记找出所有打折消费次数大于总消费次数50%的用户
Group By id
Having (SUM(Case When Flag=1 Then 1 Else 0 End)+0.0)/Count(1) >=0.5
(Select id from 用户消费明晰表
Group By id
Having (SUM(Case When Flag=1 Then 1 Else 0 End)+0.0)/Count(1) >=0.5)
a.*
from
用户表 a
where
a.id = b.userid
and
(select
count(*)
from
用户消费表 b
where
b.userid = a.id
and
exists(select
1
from
用户消费明晰表
where
id = b.mid and flag = 1))
>
(select
count(*)
from
用户消费表 b
where
b.userid = a.id
and
not exists(select
1
from
用户消费明晰表
where
id = b.mid and flag = 1))
a.*
from
用户表 a
where
a.id = b.userid
and
isnull((select
count(*)
from
用户消费表 b
where
b.userid = a.id
and
exists(select
1
from
用户消费明晰表
where
id = b.mid and flag = 1)),0)
>
isnull((select
count(*)
from
用户消费表 b
where
b.userid = a.id
and
not exists(select
1
from
用户消费明晰表
where
id = b.mid and flag = 1)),0)
Group By id
Having (SUM(Case When Flag=1 Then 1 Else 0 End)+0.0)/Count(1) >=0.5
表b 字段a.id ,flag
a和b是1:N
找出b表中flag=1的记录占总记录数的50%以上的a.id
declare @count int
set select @count=count(*) from a
select AA.id
from
(
select id from a group by id hvaing count(*)/@count>0.5
) AA,b
where AA.id=b.id and flag=1
Select id,flag from b group by id,flag Having vag(flag)>0.5)
where flag=1
Select id,flag,avg(flag) as [avg] from b group by id,flag) as A
where A.flag=1 and [avg]>0.5