我想选择某些对象的所有点击和查看。点击次数和查看次数在单独的表中进行跟踪,但我希望为包含查看次数和点击次数的对象返回 1 行。这适用于以下 SQL:
SELECT * FROM (
SELECT distinct(title)
,ROW_NUMBER() OVER (ORDER BY id ASC) AS RowNum
,SUM(vws) as vws,SUM(clicks) as clicks,id FROM(
SELECT l.id,l.title,0 as vws, COUNT(lc.id) as clicks
FROM locs l
INNER JOIN locs_clicks lc on l.id=lc.locid
GROUP BY l.title,l.id
UNION
SELECT l.id,l.title,COUNT(lv.id) as vws,0 as clicks
FROM locs l
INNER JOIN locs_views lv on l.id=lv.locid
GROUP BY l.title,l.id
)t
GROUP BY title,id
) as info
WHERE RowNum > 0 AND RowNum <= 100
我得到如下结果:
title RowNum vws clicks id
Mercedes Benz 12697 43 2 17231289
但是,当我想对视图 ( vws
) 进行排序并更改 1 行时:
`,ROW_NUMBER() OVER (ORDER BY vws ASC) AS RowNum`
我收到错误:
列“t.vws”在选择列表中无效,因为它未包含在聚合函数或 GROUP BY 子句中。
但是然后当我更改为GROUP BY title,id,vws
结束此查询时:
SELECT * FROM (
SELECT distinct(title)
,ROW_NUMBER() OVER (ORDER BY vws ASC) AS RowNum
,sum(vws) as vws,sum(clicks) as clicks,id FROM(
SELECT l.id,l.title,0 as vws, COUNT(lc.id) as clicks
FROM locs l
INNER JOIN locs_clicks lc on l.id=lc.objectid
GROUP BY l.title,l.id
UNION
SELECT l.id,l.title,COUNT(lv.id) as vws,0 as clicks
FROM locs l
INNER JOIN locs_views lv on l.id=lv.objectid
GROUP BY l.title,l.id
)t
GROUP BY title,id,vws
) as info
WHERE RowNum > 0 AND RowNum <= 100
这些行不再按不同的标题汇总为 1 行:
title RowNum vws clicks id
Mercedes Benz 699 0 2 17231289
Mercedes Benz 18102 43 0 17231289
如何选择唯一行title
并返回相同clicks
的vws
结果/行?