帮忙写个SQL语句！

[io（王飞）]

有一种设备，不妨想象成一张板子，称为母板，母板上有许多端子，用来插线。表结构：
母板表：母板ID，母板名称 ；主键：母板ID
端子表：母板ID，端子号，状态（在用或空闲）；主键：（母板ID，端子号）
现在求：端子空闲率小于某个指定值的所有母板。

先谢过！

[playyuer]

闪了!

[伍子V5]

select 母板名称 from
(select b.母板名称,sum(case a. 状态 when '在用' then 1 else 0 end ) as 在用,sum(case a. 状态 when '空闲' then 1 else 0 end ) as 空闲 from 端子表 a
inner join 母板表 b
on a.母板ID=b.母板ID
group by b.母板名称) where 空闲/(在用+空闲)=指定值

[mjhnet]

试试

select * from
(
select 母板ID,count(*) total,empty as (select count(*) from 端子表 where 端子表.母板ID = A.母板ID and 状态 = '空闲') from 端子表 A group by 母板ID
) B
where empty/total<0.5

[sativa]

母版设为AA表，子版设为BB表,STATUE 为状态1为用，0为空
建立试图，也可以写在一起view1
select * from (select A.ID,COUNT(B.STATUE) as use from AA AS A LEFT JOIN BB AS B ON A.ID=B.ID AND B.STATUE='1' GROUP BY A.ID) AS C

view2
select * from (SELECT A.ID,COUNT(B.STATUE) as total from AA AS A LEFT JOIN BB AS B ON A.ID=B.ID GROUP BY A.ID) AS D
连接两个试图
select * from (select a.id,a.use/b.total as precent from view1 as a left join view2 as b on a.id=b.id) as c where prcent>0.02
就可以算出了

[mjhnet]

修正一下

select * from
(
select 母板ID,count(*) total,empty ＝ (select count(*) from 端子表 where 端子表.母板ID = A.母板ID and 状态 = '空闲') from 端子表 A group by 母板ID
) B
where empty/total<0.5

[pp_hotel]

我也来一个
select * from 母板表 where 母板id in(
select 母板id from 端子表 group by 母板id
having (select count(状态) from 端子表 where 状态=1)/(select count(状态) from 端子表 where 状态=0)<0.6)

[sativa]

母版为AAA,子版为BBB，状态为statue 0为空，1为用
select * user/(user+empty) as precent from (select a.id, count(case b.statue when '1' then 1 else 0 end ) as user,
count(case b.statue when '0' then 1 else 0 end ) as empty from aaa as a
left join bbb as b on a.id=b.id ) as c where precent >00.1

[zjcxc]

select * from 母板表 where 母板id in(
select 母板id from 端子表 group by 母板id having sum(case 状态 when '空闲' then 1 else 0 end)>指定值)

[shuiniu]

select a.* from
母板表 a join
(select 母板ID,
(sum(case 状态 when '空闲' then 1 end) * 1.0)/(sum(case 状态 when 状态 then 1
end) * 1.0) as 空闲率
from 端子表
group by 母板ID ) b
on a.母板ID = b.母板ID
where 空闲率 = 指定值