今天去面试dba 有两道题不会做

[weixin_45328507]

有没有大佬给个正确答案参考下

[wildwolv]

第一题： SELECT A .china_name, COUNT (b.opr_state_id) AS kxjjsl FROM spc_room A, rme_rack b WHERE b.opr_state_id = '170001' AND A .is_supportide = 1 AND b.posit_id = A .room_id GROUP BY A .china_name 第二题： SELECT * FROM rme_port WHERE super_res_id IN ( SELECT eqp_id FROM rme_eqp WHERE posit_id IN ( SELECT room_id FROM spc_room WHERE A .is_supportide = 1 ) )

[wildwolv]

第一题漏了个条件，应该是这样：

[weixin_45328507]

引用 2 楼 wildwolv 的回复：

第一题漏了个条件，应该是这样：

感谢

[听雨停了]

--题1
SELECT a.china_name,COUNT(b.opr_state_id) AS cnt 
FROM spc_room AS a 
INNER JOIN rme_rack AS b ON a.room_id=b.posit_id AND b.opr_state_id='170001'
WHERE a.issupportidc=1
HAVING COUNT(b.opr_state_id)>1000

--题2
--in
SELECT b.* FROM rme_eqp AS a
INNER JOIN rem_port AS b ON a.eqp_id=b.super_res_id
WHERE a.posid_id IN (
	SELECT room_id FROM spc_room
	WHERE issupportidc=1
)
--exists
SELECT b.* FROM rme_eqp AS a
INNER JOIN rem_port AS b ON a.eqp_id=b.super_res_id
WHERE exists (
	SELECT room_id FROM spc_room AS c
	WHERE issupportidc=1 AND a.posit_id = c.room_id
)

[听雨停了]

SELECT a.china_name,COUNT(b.opr_state_id) AS cnt 
FROM spc_room AS a 
INNER JOIN rme_rack AS b ON a.room_id=b.posit_id AND b.opr_state_id='170001'
WHERE a.issupportidc=1
GROUP BY a.china_name
HAVING COUNT(b.opr_state_id)>1000

少了个分组条件

[AHUA1001]

公司没有招人的计划，但是还是在网上发了好多招聘广告。 把项目中遇到的，解决不了的问题，当作面试题。