第一题: 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 ) )
--题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少了个分组条件
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
)
)
--题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少了个分组条件