问题描述
第一个sqlSELECT 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第二个sqlSELECT 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次 问题补充：第一个sql count算的是 对话数量，第二个sql count 算的是 有效对话数量，显示的时候要分2列，显示在一条记录里（对话数量 有效对话数量），不是把他们合并，放在一列里
解决方案
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 bwhere a.user_id = b.idand 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') 大概意思就是两表关联，直接count计算的就是对话数量，加了decode判断的就是有效对话数量因为你没给表结构，所以可能直接运行不一定能用，你自己再改改
解决方案二：
SELECT COUNT(0) vc,user_id,chat_basic_id,count(decode(tt.sendrole_type,'0',tt.sendrole_type)FROM BUSINESS_CHAT_MSG T ,(SELECT ID as user_id ,sendrole_type as sendrole_type 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,sendrole_type) ttwhere t.USER_ID = tt.user_id group by t.user_id，chat_basic_id你给的语句有问题，group by现实两个字段，但group by 后面只给一个，而且外层最终结束括号也没有。
解决方案三：
如果是oracle 可以考虑用with as如：WITH qb1 AS (SELECT inst_id FROM gv$session), qb2 AS (SELECT unique inst_id FROM qb1 UNION ALL SELECT unique inst_id FROM qb1)SELECT /*+ MATERIALIZE */ *FROM qb1, qb2WHERE qb1.inst_id = qb2.inst_id;
解决方案四：
WHERE (COMPANY_ID = 'e4510180373c41a09a87b1f768a9b660' or (COMPANY_ID = 'e4510180373c41a09a87b1f768a9b660' AND sendrole_type='0' )) 不可以么？