SELECT DECLINE_INFORM_TABLE.DECLINE_INFORM_ID
FROM DECLINE_INFORM_TABLE
WHERE COMPANY_ID = '100000018'
AND DECLINE_REQUEST_NUMBER in
(SELECT DISTINCT RELATION_DATA_TABLE.SEARCH_REQUEST_NO
FROM RELATION_DATA_TABLE
WHERE SEARCH_REQUEST_NO NOT IN
('JBRI1155789630765', 'JBRI1155790458437', 'JBRI1155885616125',
'JBRI1156066297734', 'JBRI1156066426796', 'JBRI1156210975750',
'JBRI1156211829312', 'JBRI1154949073687', 'JBRI1155710527828',
'JBRI1156232361953', 'JBRI1155890965796')
and engineer_id in
(select engineer_id
from engineer_data_table
where company_id = '100000018'))
-------------------------------------------------------
在上面原句的子查询中,能不能把字查询的行数返回
我想同时返回子查询的行数,如何写,??子查询如下,
SELECT DISTINCT RELATION_DATA_TABLE.SEARCH_REQUEST_NO
FROM RELATION_DATA_TABLE
WHERE SEARCH_REQUEST_NO NOT IN
('JBRI1155789630765', 'JBRI1155790458437', 'JBRI1155885616125',
'JBRI1156066297734', 'JBRI1156066426796', 'JBRI1156210975750',
'JBRI1156211829312', 'JBRI1154949073687', 'JBRI1155710527828',
'JBRI1156232361953', 'JBRI1155890965796')
and engineer_id in
(select engineer_id
from engineer_data_table
where company_id = '100000018')
FROM DECLINE_INFORM_TABLE
WHERE COMPANY_ID = '100000018'
AND DECLINE_REQUEST_NUMBER in
(SELECT DISTINCT RELATION_DATA_TABLE.SEARCH_REQUEST_NO
FROM RELATION_DATA_TABLE
WHERE SEARCH_REQUEST_NO NOT IN
('JBRI1155789630765', 'JBRI1155790458437', 'JBRI1155885616125',
'JBRI1156066297734', 'JBRI1156066426796', 'JBRI1156210975750',
'JBRI1156211829312', 'JBRI1154949073687', 'JBRI1155710527828',
'JBRI1156232361953', 'JBRI1155890965796')
and engineer_id in
(select engineer_id
from engineer_data_table
where company_id = '100000018'))
-------------------------------------------------------
在上面原句的子查询中,能不能把字查询的行数返回
我想同时返回子查询的行数,如何写,??子查询如下,
SELECT DISTINCT RELATION_DATA_TABLE.SEARCH_REQUEST_NO
FROM RELATION_DATA_TABLE
WHERE SEARCH_REQUEST_NO NOT IN
('JBRI1155789630765', 'JBRI1155790458437', 'JBRI1155885616125',
'JBRI1156066297734', 'JBRI1156066426796', 'JBRI1156210975750',
'JBRI1156211829312', 'JBRI1154949073687', 'JBRI1155710527828',
'JBRI1156232361953', 'JBRI1155890965796')
and engineer_id in
(select engineer_id
from engineer_data_table
where company_id = '100000018')
FROM RELATION_DATA_TABLE as a,(
Select engineer_id,count(*) as cn
from engineer_data_table
where company_id = '100000018' group by engineer_id) as b
WHERE a.engineer_id=b.engineer_id and
SEARCH_REQUEST_NO NOT IN
('JBRI1155789630765', 'JBRI1155790458437', 'JBRI1155885616125',
'JBRI1156066297734', 'JBRI1156066426796', 'JBRI1156210975750',
'JBRI1156211829312', 'JBRI1154949073687', 'JBRI1155710527828',
'JBRI1156232361953', 'JBRI1155890965796')
FROM DECLINE_INFORM_TABLE left join
(SELECT count(RELATION_DATA_TABLE.SEARCH_REQUEST_NO) as num,RELATION_DATA_TABLE.SEARCH_REQUEST_NO
FROM RELATION_DATA_TABLE
WHERE SEARCH_REQUEST_NO NOT IN
('JBRI1155789630765', 'JBRI1155790458437', 'JBRI1155885616125',
'JBRI1156066297734', 'JBRI1156066426796', 'JBRI1156210975750',
'JBRI1156211829312', 'JBRI1154949073687', 'JBRI1155710527828',
'JBRI1156232361953', 'JBRI1155890965796')
and engineer_id in
(select engineer_id
from engineer_data_table
where company_id = '100000018') group by RELATION_DATA_TABLE.SEARCH_REQUEST_NO) a
on DECLINE_REQUEST_NUMBER=a.SEARCH_REQUEST_NO
WHERE COMPANY_ID = '100000018'
AND DECLINE_REQUEST_NUMBER in
(SELECT DISTINCT RELATION_DATA_TABLE.SEARCH_REQUEST_NO
FROM RELATION_DATA_TABLE
WHERE SEARCH_REQUEST_NO NOT IN
('JBRI1155789630765', 'JBRI1155790458437', 'JBRI1155885616125',
'JBRI1156066297734', 'JBRI1156066426796', 'JBRI1156210975750',
'JBRI1156211829312', 'JBRI1154949073687', 'JBRI1155710527828',
'JBRI1156232361953', 'JBRI1155890965796')
and engineer_id in
(select engineer_id
from engineer_data_table
where company_id = '100000018'))
-------------
SELECT (RELATION_DATA_TABLE.SEARCH_REQUEST_NO) as num
FROM RELATION_DATA_TABLE
WHERE SEARCH_REQUEST_NO NOT IN
('JBRI1155789630765', 'JBRI1155790458437', 'JBRI1155885616125',
'JBRI1156066297734', 'JBRI1156066426796', 'JBRI1156210975750',
'JBRI1156211829312', 'JBRI1154949073687', 'JBRI1155710527828',
'JBRI1156232361953', 'JBRI1155890965796')
and engineer_id in
(select engineer_id
from engineer_data_table
where company_id = '100000018')
cnt=(
select count(*) from
(SELECT DISTINCT RELATION_DATA_TABLE.SEARCH_REQUEST_NO
FROM RELATION_DATA_TABLE
WHERE SEARCH_REQUEST_NO NOT IN
('JBRI1155789630765', 'JBRI1155790458437', 'JBRI1155885616125',
'JBRI1156066297734', 'JBRI1156066426796', 'JBRI1156210975750',
'JBRI1156211829312', 'JBRI1154949073687', 'JBRI1155710527828',
'JBRI1156232361953', 'JBRI1155890965796')
and engineer_id in
(select engineer_id
from engineer_data_table
where company_id = '100000018')
)a where SEARCH_REQUEST_NO = DECLINE_INFORM_TABLE.DECLINE_REQUEST_NUMBER)
FROM DECLINE_INFORM_TABLE
WHERE COMPANY_ID = '100000018'
AND DECLINE_REQUEST_NUMBER in
(SELECT DISTINCT RELATION_DATA_TABLE.SEARCH_REQUEST_NO
FROM RELATION_DATA_TABLE
WHERE SEARCH_REQUEST_NO NOT IN
('JBRI1155789630765', 'JBRI1155790458437', 'JBRI1155885616125',
'JBRI1156066297734', 'JBRI1156066426796', 'JBRI1156210975750',
'JBRI1156211829312', 'JBRI1154949073687', 'JBRI1155710527828',
'JBRI1156232361953', 'JBRI1155890965796')
and engineer_id in
(select engineer_id
from engineer_data_table
where company_id = '100000018'))
cnt = (
SELECT count(DISTINCT RELATION_DATA_TABLE.SEARCH_REQUEST_NO)
FROM RELATION_DATA_TABLE
WHERE SEARCH_REQUEST_NO NOT IN
('JBRI1155789630765', 'JBRI1155790458437', 'JBRI1155885616125',
'JBRI1156066297734', 'JBRI1156066426796', 'JBRI1156210975750',
'JBRI1156211829312', 'JBRI1154949073687', 'JBRI1155710527828',
'JBRI1156232361953', 'JBRI1155890965796')
and engineer_id in
(select engineer_id
from engineer_data_table
where company_id = '100000018')
and SEARCH_REQUEST_NO in(
SELECT DECLINE_INFORM_TABLE.DECLINE_INFORM_ID
FROM DECLINE_INFORM_TABLE
WHERE COMPANY_ID = '100000018')
)
FROM DECLINE_INFORM_TABLE
WHERE COMPANY_ID = '100000018'
AND DECLINE_REQUEST_NUMBER in
(SELECT DISTINCT RELATION_DATA_TABLE.SEARCH_REQUEST_NO
FROM RELATION_DATA_TABLE
WHERE SEARCH_REQUEST_NO NOT IN
('JBRI1155789630765', 'JBRI1155790458437', 'JBRI1155885616125',
'JBRI1156066297734', 'JBRI1156066426796', 'JBRI1156210975750',
'JBRI1156211829312', 'JBRI1154949073687', 'JBRI1155710527828',
'JBRI1156232361953', 'JBRI1155890965796')
and engineer_id in
(select engineer_id
from engineer_data_table
where company_id = '100000018'))