我期待的是tmp。在移动到下一个userid
之前,为每个userid
增加1-10
的排名,但是我所得到的是它为每个记录保留在1
上,因此不限制每个userid
的10
项。
知道我做错了什么吗?很可能是一些简单而明显的事情,或者更有可能以一种意想不到的方式使用SQL。
SELECT DISTINCT
tmp.title,
tmp.content,
tmp.postid,
tmp.userid,
tmp.screenname,
tmp.email
FROM
(
SELECT
qp.title,
qp.content,
qp.postid,
ut.userid,
ut.screenname,
ut.email,
qp.created,
@rownum := IF( @prev = ut.userid, @rownum+1, 1 ) AS rank,
@prev := ut.userid
FROM
user_table AS ut JOIN (SELECT @rownum := NULL, @prev := 0) AS r ,
qa_posts AS qp,
qa_categories AS qc,
expatsblog_country AS cc
WHERE
LOWER(ut.country_of_expat) = LOWER(qc.title)
AND ut.setting_notifications IN (3)
AND ut.valid=1
AND ut.confirm_email = 1
AND qc.categoryid = qp.categoryid
AND qp.type='Q'
AND DATE(qp.created)>=DATE_SUB(NOW(), INTERVAL 24 HOUR)
ORDER BY ut.userid,qp.created ASC
) AS tmp
WHERE tmp.rank < 10
ORDER BY tmp.userid, tmp.created ASC
我过去做过很多查询,特别是在这里回应关于MySQL和@变量的帖子。我遇到的一个问题是当应用于行时数据的返回顺序。
您的原始查询确实在最内层查询中有一个顺序,但我遇到过@variable赋值不真正尊重它的情况,并重置计数器,因为它转到其他一些(在这种情况下)用户,然后稍后,遇到更多的记录,并将计数器重置回一个,即使它发生在稍后。
接下来,将DISTINCT应用到末尾。我会尝试将DISTINCT拉到最内部,这样您就不会为某些键,同一用户获得10条记录,最终只返回2条记录。
也就是说,我会将查询调整为下面的内容。最内层只是在您想要的列上获取DISTINCT,然后应用@variable赋值。
SELECT DISTINCT
tmp.title,
tmp.content,
tmp.postid,
tmp.userid,
tmp.screenname,
tmp.email,
@rownum := IF( @prev = ut.userid, @rownum+1, 1 ) AS rank,
@prev := ut.userid
FROM
( SELECT DISTINCT
qp.title,
qp.content,
qp.postid,
ut.userid,
ut.screenname,
ut.email
FROM
user_table AS ut
JOIN qa_categories AS qc
ON LOWER( ut.country_of_expat ) = LOWER( qc.title )
JOIN qa_posts AS qp
ON qc.categoryid = qp.categoryid
AND qp.type='Q'
AND DATE(qp.created)>=DATE_SUB(NOW(), INTERVAL 24 HOUR)
WHERE
ut.setting_notifications = 3
AND ut.valid = 1
AND ut.confirm_email = 1
ORDER BY
ut.userid,
qp.created ASC ) AS tmp,
( SELECT @rownum := NULL,
@prev := 0) AS r
HAVING
tmp.rank < 10
ORDER BY
tmp.userid
我没有看到任何地方有"cc"的引用生成一个笛卡尔结果,给出expatsblog_country中每个条目的记录所以我把它去掉了……如果需要,在适用的地方放置,并且也放置JOIN条件。
(删除expatsblog_country AS cc)
另外,我将WHERE子句改为HAVING子句,这样所有返回的记录都将被考虑为最终结果集。这将确保@rownum在遇到可能的条目时保持递增,但having会抛出所有大于10的条目。
最后,由于内部表已经按照用户和创建的日期进行了预先排序,因此不需要在外部表中再次显式地重新排序。也可以是UserID
我将在这里猜测问题是什么。
您正在组合两种类型的连接语法,这可能会导致您的问题。
您正在使用JOIN
和逗号在您的表之间。在user_table
和user变量之间使用JOIN
,然后在其余表之间使用逗号。
FROM user_table AS ut JOIN (SELECT @rownum := NULL, @prev := 0) AS r ,
qa_posts AS qp,
qa_categories AS qc,
expatsblog_country AS cc
而您的WHERE
子句包含大多数表要连接的列。看起来表expatsblog_country
没有连接条件。当你连接表时,你应该使用一种语法,而不是混合使用。
我的建议与此类似。我没有看到expatsblog_country
表的任何连接条件,所以我使用CROSS JOIN
将这些表连接到其他表的子查询。如果有一列要将expatsblog_country
连接到其他列,则将该查询移动到子查询中:
SELECT DISTINCT tmp.title,
tmp.content,
tmp.postid,
tmp.userid,
tmp.screenname,
tmp.email
FROM
(
SELECT src.title,
src.content,
src.postid,
src.userid,
src.screenname,
src.email,
src.created,
@rownum := IF( @prev = src.userid, @rownum+1, 1 ) AS rank,
@prev := src.userid
FROM
(
select ut.userid,
ut.screenname,
ut.email,
qp.title,
qp.content,
qp.postid,
qp.created
from user_table AS ut
join qa_categories AS qc
on LOWER(ut.country_of_expat) = LOWER(qc.title)
join qa_posts AS qp
on qc.categoryid = qp.categoryid
where ut.setting_notifications IN (3)
and ut.valid=1
and ut.confirm_email = 1
and qp.type='Q'
and DATE(qp.created)>=DATE_SUB(NOW(), INTERVAL 24 HOUR)
) src
CROSS JOIN
(
SELECT @rownum := 0, @prev := 0
) AS r
CROSS JOIN expatsblog_country AS cc
ORDER BY src.userid, src.created ASC
) AS tmp
WHERE tmp.rank < 10
ORDER BY tmp.userid, tmp.created ASC
尝试更改
( SELECT @rownum := NULL,
@prev := 0) AS r
使@rownum初始化为0,@prev初始化为NULL
知道这是什么
- expats_country是一个不应该是查询的一部分
,更重要的是:
- "DATE()"在qp左右。创建是搞砸了这一点和实际的"过去24小时"部分的查询。