A01014170
王_超
2017-11-22 09:01

mysql 对两个计数结果求和

  • mysql
  • sql

现在要完成一个任务,需要通过一条sql语句实现对两种情况的计数,写了如下的sql语句

 SELECT t0.id,
(SELECT COUNT(cnt) FROM (
SELECT COUNT(DISTINCT(ip)) AS cnt FROM im_video_view WHERE user_id=0 AND video_id IN (SELECT video_id FROM imagingly.`im_video_material` WHERE material_id=t0.id)
UNION ALL
SELECT COUNT(DISTINCT(user_id)) AS cnt FROM im_video_view WHERE user_id !=0  AND video_id IN (SELECT video_id FROM imagingly.`im_video_material` WHERE material_id=t0.id)
) AS countdata)c
FROM theme_package.tp_ar t0

放在mysql里单独执行的时候,报如下错误

 1 queries executed, 0 success, 1 errors, 0 warnings

查询:SELECT t0.id, (SELECT COUNT(cnt) FROM ( SELECT COUNT(DISTINCT(ip)) AS cnt FROM im_video_view WHERE user_id=0 AND video_id IN (SE...

错误代码: 1054
Unknown column 't0.id' in 'where clause'

执行耗时   : 0 sec
传送时间   : 0 sec
总耗时      : 0.003 sec

如果把sql里的t0.id替换成某个指定值,则能成功查询

 SELECT COUNT(cnt) FROM (
SELECT COUNT(DISTINCT(ip)) AS cnt FROM im_video_view WHERE user_id=0 AND video_id IN (SELECT video_id FROM imagingly.`im_video_material` WHERE material_id=1)
UNION ALL
SELECT COUNT(DISTINCT(user_id)) AS cnt FROM im_video_view WHERE user_id !=0  AND video_id IN (SELECT video_id FROM imagingly.`im_video_material` WHERE material_id=1)
) AS countdata

执行结果

 COUNT(cnt)  
------------
           2
  • 点赞
  • 回答
  • 收藏
  • 复制链接分享

5条回答