MySQL 复杂查询

发布于 2024-10-18 03:45:08 字数 482 浏览 1 评论 0原文

我有一个包含 keywords_id、site_id、百分比的表。

现在我使用：

SELECT   *
FROM     keyword_relations
WHERE    keyword_id = "game"
ORDER BY percentage DESC,
LIMIT    {$page}, {$limitperpage}

现在假设根据此 SQL 在第 26 页上出现一个名为“example.com”的网站。

现在假设我想获取 example.com 上关键字所在的行号，我该如何做到这一点。

例如，如果 example.com 在关键字“examples”的第 6 页上排名第 5，那么当我拥有网站“example.com”和来自不同页面的关键字“examples”时，如何获取页码。

我试图通过将关键字网站排名的行数（按关键字出现次数排序）除以 limit_per_page 来找到网站应出现的页码。

原文

I have a table that contains a keyword_id, site_id, percentage.

Now I use:

SELECT   *
FROM     keyword_relations
WHERE    keyword_id = "game"
ORDER BY percentage DESC,
LIMIT    {$page}, {$limitperpage}

Now lets say on page 26 according to this SQL a website called "example.com" appears.

Now lets say that I want to get the row number where a keyword on example.com, how can I do that.

For example, if example.com is ranked 5th on page 6 for keyword "examples" then how do I get the page number when I have the website "example.com" and the keyword "examples" from a different page.

I am trying to find the page number where the website should appear by dividing the the row number of the ranking of a website for a keyword (sorted by keywords occurrence) by the limit_per_page.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

浅语花开 2024-10-25 03:45:08

SELECT   *, @row:=@row+1 AS Row_Number
FROM     keyword_relations, (SELECT @row:=0) r
WHERE    keyword_id = "game"
ORDER BY percentage DESC,
LIMIT    {$page}, {$limitperpage}

我认为，鉴于我能找到什么

注意：更改将 :=0 赋值给 {$page}*{$limitperpage}

EDIT

如果您对 page 感兴趣，请尝试下列的：

SELECT    *, FLOOR((@row/5)+1) AS Page_Number, @row:=@row+1 AS Row_Number
FROM      keyword_relations,
          (SELECT @row:={$page}*{$limitperpage}) r
WHERE     keyword_id = "game"
ORDER BY  percentage DESC
LIMIT     {$page},{$limitperpage}

SELECT   *, @row:=@row+1 AS Row_Number
FROM     keyword_relations, (SELECT @row:=0) r
WHERE    keyword_id = "game"
ORDER BY percentage DESC,
LIMIT    {$page}, {$limitperpage}

I think, given what I could find

Note: Change the :=0 assignment to the value of {$page}*{$limitperpage}

EDIT

If you're interested in page, try the following:

SELECT    *, FLOOR((@row/5)+1) AS Page_Number, @row:=@row+1 AS Row_Number
FROM      keyword_relations,
          (SELECT @row:={$page}*{$limitperpage}) r
WHERE     keyword_id = "game"
ORDER BY  percentage DESC
LIMIT     {$page},{$limitperpage}

回复收藏 0 原文

菊凝晚露 2024-10-25 03:45:08

$per_page_limit = 20;
$sql = "SELECT count(k_id) as k_id FROM keywords_relations WHERE k_id = '{$row['k_id']}' AND percent >= '{$row['percent']}'";
$data = mysql_fetch_array(mysql_query($sql));
$k_ids = $data['k_id'] + 1;
//is_int($k_page) ? floor($k_page) : floor($k_page) + 1;
if($k_ids % $per_page_limit ){
    $k_page = floor($k_ids/$per_page_limit) + 1;         
} else {
    $k_page = $k_ids/$per_page_limit;

$per_page_limit = 20;
$sql = "SELECT count(k_id) as k_id FROM keywords_relations WHERE k_id = '{$row['k_id']}' AND percent >= '{$row['percent']}'";
$data = mysql_fetch_array(mysql_query($sql));
$k_ids = $data['k_id'] + 1;
//is_int($k_page) ? floor($k_page) : floor($k_page) + 1;
if($k_ids % $per_page_limit ){
    $k_page = floor($k_ids/$per_page_limit) + 1;         
} else {
    $k_page = $k_ids/$per_page_limit;

回复收藏 0 原文

~没有更多了~