[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次
[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判断的就是有效对话数量
因为你没给表结构，所以可能直接运行不一定能用，你自己再改改
WHERE (COMPANY_ID = 'e4510180373c41a09a87b1f768a9b660' or (COMPANY_ID = 'e4510180373c41a09a87b1f768a9b660' AND sendrole_type='0' )) 不可以么？
[code="java"]
如果是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, qb2
WHERE qb1.inst_id = qb2.inst_id;
[/code]
[code="sql"]
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) tt
where t.USER_ID = tt.user_id
group by t.user_id，chat_basic_id
[/code]
你给的语句有问题，group by现实两个字段，但group by 后面只给一个，而且外层最终结束括号也没有。