如何将这2个查询合并，只查询一次

Question

[b]第一个sql[/b]
SELECT COUNT(0),user_id,onlinekf_get_customer_name(user_id) customerName FROM(
SELECT COUNT(0),user_id
FROM BUSINESS_CHAT_MSG T
WHERE T.USER_ID IN
(SELECT ID
FROM BASE_CUSTOMER_INFO T
WHERE COMPANY_ID = 'e4510180373c41a09a87b1f768a9b660')
AND chat_time>=to_date('2014-04-01 00:00:00','yyyy-MM-dd hh24:mi:ss')
AND chat_time<=to_date('2014-04-25 23:59:59','yyyy-MM-dd hh24:mi:ss')
GROUP BY user_id,t.chat_basic_id)
GROUP BY user_id
[b]第二个sql[/b]
SELECT COUNT(0),user_id,onlinekf_get_customer_name(user_id) customerName FROM(
SELECT COUNT(0) vc,user_id,chat_basic_id
FROM BUSINESS_CHAT_MSG T
WHERE T.USER_ID IN
(SELECT ID
FROM BASE_CUSTOMER_INFO T
WHERE COMPANY_ID = 'e4510180373c41a09a87b1f768a9b660')
AND sendrole_type='0'

AND chat_time>=to_date('2014-04-01 00:00:00','yyyy-MM-dd hh24:mi:ss')
AND chat_time<=to_date('2014-04-25 23:59:59','yyyy-MM-dd hh24:mi:ss')
GROUP BY user_id,t.chat_basic_id)
GROUP BY user_id
两个sql唯一的区别是其中有个条件不同，但却要查询2次，能否合并成一个，只查询一次，不能通过union，union还是查了2次

Answer 1

[code="sql"]
select a.user_id,
onlinekf_get_customer_name(user_id) customerName ,
count(1) 对话数量,
sum(decode(b.sendrole_type,'0',1,0)) 有效对话数量
from BUSINESS_CHAT_MSG a,BASE_CUSTOMER_INFO b
where a.user_id = b.id
and b.COMPANY_ID = 'e4510180373c41a09a87b1f768a9b660')

AND b.chat_time>=to_date('2014-04-01 00:00:00','yyyy-MM-dd hh24:mi:ss')
AND b.chat_time<=to_date('2014-04-25 23:59:59','yyyy-MM-dd hh24:mi:ss')
[/code]

大概意思就是两表关联，直接count计算的就是对话数量，加了decode判断的就是有效对话数量

因为你没给表结构，所以可能直接运行不一定能用，你自己再改改