SELECT a.*,b. bugcount
FROM testcase1 a,(SELECT COUNT(*) as bugcount
FROM test_case_bug ) b
WHERE a.proj_id = b.proj_id AND a.function_id = b.function_id AND
a.case_id = b.test_case_id and (a.proj_id = 'STA_PROMNG_TEST') AND (b.bugcount >= '1')
FROM testcase1 a,(SELECT COUNT(*) as bugcount
FROM test_case_bug ) b
WHERE a.proj_id = b.proj_id AND a.function_id = b.function_id AND
a.case_id = b.test_case_id and (a.proj_id = 'STA_PROMNG_TEST') AND (b.bugcount >= '1')
(SELECT COUNT(*)
FROM test_case_bug b
WHERE a.proj_id = b.proj_id AND a.function_id = b.function_id AND
a.case_id = b.test_case_id) AS bugcount
FROM testcase1 a
WHERE (proj_id = 'STA_PROMNG_TEST') AND (exists (SELECT *
FROM test_case_bug b
WHERE a.proj_id = b.proj_id AND a.function_id = b.function_id AND
a.case_id = b.test_case_id) )
(SELECT COUNT(*)
FROM test_case_bug b
WHERE a.proj_id = b.proj_id AND a.function_id = b.function_id AND
a.case_id = b.test_case_id
--此条件应该放在这里
having count(*)>='1'
) AS bugcount
FROM testcase1 a
WHERE (proj_id = 'STA_PROMNG_TEST')
--AND (bugcount >= '1') 而不应该放在这里,因为这就像局部变量的原理,在这里是访问不到b表的
2楼的虽然可以执行,但是少了其中的一个条件(bugcount >= '1')
(SELECT COUNT(*)
FROM test_case_bug b
WHERE a.proj_id = b.proj_id AND a.function_id = b.function_id AND
a.case_id = b.test_case_id) AS bugcount
FROM testcase1 a
WHERE (proj_id = 'STA_PROMNG_TEST') AND ((SELECT COUNT(*)
FROM test_case_bug b
WHERE a.proj_id = b.proj_id AND a.function_id = b.function_id AND
a.case_id = b.test_case_id) >= '1')
别名是不能作为字段放在where,order by和Group by中
SELECT a.*,
(SELECT COUNT(*)
FROM test_case_bug b
WHERE a.proj_id = b.proj_id AND a.function_id = b.function_id AND
a.case_id = b.test_case_id) AS bugcount
FROM testcase1 a
WHERE (proj_id = 'STA_PROMNG_TEST')
) a where bugcount >= '1'