现在要完成一个任务,需要通过一条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