SQL命令错误 当没有用 EXISTS 引入子查询时，在选择列表中只能指定一个表达式

来源：百度知道 编辑：UC知道 时间：2024/05/30 22:16:38
SELECT (SELECT SERVICE_ID, COUNT(*) AS 定制用户数
FROM TM_SMCUSERS
WHERE (ACTIONID IN ('1')) GROUP BY SERVICE_ID) -
(SELECT SERVICE_ID, COUNT(*) AS 取消用户数
FROM TM_SMCUSERS AS TM_SMCUSERS_1
WHERE (ACTIONID IN ('2')) GROUP BY SERVICE_ID) AS 当前用户数

想一次性求6个业务的当前用户数，可是报错了，高手帮忙改下。
呵呵 报错更严重了。实际是等于
各彩信业务定制业务次数总和
SELECT SERVICE_ID, COUNT(*) AS 各业务定制次数
FROM TM_SMCUSERS where actionid=1
GROUP BY SERVICE_ID
HAVING (COUNT(*) >= 1)

各彩信业务退订业务次数总和
SELECT SERVICE_ID, COUNT(*) AS 各业务取消次数
FROM TM_SMCUSERS where actionid=2
GROUP BY SERVICE_ID
HAVING (COUNT(*) >= 1)
这两个分别查询的求差。就是 例如service_id 分别为1，2，3，4，5，6，的话就是分别求各自的“定制总数减去取消总数=当前用户数”。

不用这么麻烦。
sql server
select 定制用户数-取消用户数 ,service_id
from ( select sum(case when actionid = 1 then 1 else 0) 定制用户数,sum(case when actionid=2 then 1 else 0) 取消用户数
from TM_SMCUSERS
group by service_id) tmp

mysql

select 定制用户数-取消用户数 ,service_id
from ( select sum(if(actionid = 1,1,0) 定制用户数,sum(if(actionid=2,1,0) 取消用户数
from TM_SMCUSERS
group by service_id) tmp

SELECT (SELECT COUNT(*) AS 定制用户数
FROM TM_SMCUSERS
WHERE (ACTIONID IN ('1')) GROUP BY SERVICE_ID) -
(SELECT COUNT(*) AS 取消用户数
FROM TM_SMCUSERS AS TM_SMCUSERS_1
WHERE (ACTIONID IN ('2')) GROUP BY SERVICE_ID) AS 当前用户数

那个是减号吗？
不知道你是不是这个意思，你先运行看看吧

SELECT a.service_id,a.定制用户数-b.取消用户数 from
(SELECT SERVICE_ID,COUNT(*) AS 定制用户数 FROM TM_SMCUSERS WHERE ACTIONID IN ('