请教:
会员表:client_info
client_id name city
1001 c1 北京
1002 c2 上海
1003 c3 南京
1004 c4 上海机器表 mac_info
mac_id mac_name client_id state(状态:0休眠 1开机 2关机)
1 t1 1001 1
2 t2 1001 1
3 t3 1002 0
4 t4 1002 2
5 t5 1004 1
6 t6 1003 1希望得到每座城市的机器数目,休眠、开机、关机的台数?
city num sleep open close
北京 2 2 0 0
上海 3 1 1 1
南京 1 0 1 0
a.city,
(select count(1) from mac_info where client_id=a.client_id) as [num],
(select count(1) from mac_info where client_id=a.client_id and state=0) as [sleep],
(select count(1) from mac_info where client_id=a.client_id and state=1) as [open],
(select count(1) from mac_info where client_id=a.client_id and state=2) as [close]
from client_info a
sum(if(state=0,1,0)) as `sleep`,
sum(if(state=1,1,0)) as `open`,
sum(if(state=2,1,0)) as `close`
from client_info c inner join mac_info m on c.client_id=m.client_id
group by city
运行后,数据库提示:ERROR: function if(boolean, integer, integer) does not exist
HINT: No function matches the given name and argument types. You may need to add explicit type casts.