一句简单的sql，返回行数，怎么写？100分，在线等

Question

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')
------------------------------------------------
子查询的个数有可能比实际的返回的行数多，
结果例子，返回3行数据，但是子查询是5条,这个5每行都带着，
-----------
id     行数
-----------
1       5
2       5
3       5

Answer 1

SELECT DECLINE_INFORM_TABLE.DECLINE_INFORM_ID,
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'))

Answer 2

SELECT DECLINE_INFORM_TABLE.DECLINE_INFORM_ID,
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'))

Answer 3

试试看SELECT DECLINE_INFORM_TABLE.DECLINE_INFORM_ID,a.rownum
  FROM DECLINE_INFORM_TABLE,
       ( SELECT RELATION_DATA_TABLE.SEARCH_REQUEST_NO,count(*) as rownum
          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
  WHERE COMPANY_ID = '100000018'
  AND DECLINE_REQUEST_NUMBER = a.SEARCH_REQUEST_NO

Answer 4

给你个思路啊直接把子查询在SELECT 语句里复制一下不就成了么？一个COUNT（*） 一个 SELECT ID 就是稍微乱点，思路应该是对的select * ,(子查询)
  from tb1
where id in (子查询)