sql INNER JOIN 中的Rownum？

发布于 2024-12-10 09:04:46 字数 993 浏览 0 评论 0原文

我想在下面的查询选择中使用 rownum ，我在下面的第一个查询中使用它 true 但不知道应该如何在下面的第二个查询中使用它？

第一个有效的查询：

$this -> db -> query("
SELECT @rownum := @rownum + 1 rownum, 
       t.* 
FROM   (SELECT * 
        FROM   table 
        ORDER  BY id DESC 
        LIMIT  $offset, $coun) t, 
       (SELECT @rownum := 0) r 
")

我在 foreach 上的 html 代码中回显上面的 rownum 为： echo intval($row -> rownum + $offset)

第二个查询（我想使用从这个查询中，这个查询怎么样？）：

$this -> db -> query("
SELECT tour_foreign.id, 
       tour_foreign.name, 
       tour_foreign_residence.name_re, 
       tour_foreign.service, 
       tour_foreign.date_go, 
       tour_foreign.date_back, 
       tour_foreign.term 
FROM   tour_foreign 
       INNER JOIN tour_foreign_residence 
         ON ( tour_foreign.id = tour_foreign_residence.relation ) 
WHERE  tour_foreign.name LIKE "%' . $find . '%" 
        OR tour_foreign_residence.name_re LIKE "%' . $find . '%"
")

原文

I want use rownum in following query select, i use of it true in following first query but don't know how should use from it in following second query?

First query that work true:

$this -> db -> query("
SELECT @rownum := @rownum + 1 rownum, 
       t.* 
FROM   (SELECT * 
        FROM   table 
        ORDER  BY id DESC 
        LIMIT  $offset, $coun) t, 
       (SELECT @rownum := 0) r 
")

I echo above rownum in html code on foreach as: echo intval($row -> rownum + $offset)

Second query(i want use from it in this query, how is for this query?):

$this -> db -> query("
SELECT tour_foreign.id, 
       tour_foreign.name, 
       tour_foreign_residence.name_re, 
       tour_foreign.service, 
       tour_foreign.date_go, 
       tour_foreign.date_back, 
       tour_foreign.term 
FROM   tour_foreign 
       INNER JOIN tour_foreign_residence 
         ON ( tour_foreign.id = tour_foreign_residence.relation ) 
WHERE  tour_foreign.name LIKE "%' . $find . '%" 
        OR tour_foreign_residence.name_re LIKE "%' . $find . '%"
")

分享到QQ

分享到微博

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

发布评论

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

笨死的猪 2024-12-17 09:04:46

尝试一下，只需将 @rownum 添加到 SELECT 列表的开头，并将 @rownum 重置为隐式 JOIN 到末尾。我使用类似的 JOIN 对我的一个数据库进行了测试，它似乎工作正常。

将显式 JOIN 与针对 @rownum 重置伪表的隐式连接混合起来看起来很奇怪，但我不知道执行显式 JOIN 的方法当没有公共列时。如果有人可以改善这种情况，请发表评论。

$this -> db -> query("
SELECT
       @rownum := @rownum + 1 rownum,
       tour_foreign.id, 
       tour_foreign.name, 
       tour_foreign_residence.name_re, 
       tour_foreign.service, 
       tour_foreign.date_go, 
       tour_foreign.date_back, 
       tour_foreign.term 
FROM   tour_foreign 
       INNER JOIN tour_foreign_residence 
         ON ( tour_foreign.id = tour_foreign_residence.relation ),
       (SELECT @rownum := 0) r
WHERE  tour_foreign.name LIKE "%' . $find . '%" 
        OR tour_foreign_residence.name_re LIKE "%' . $find . '%"
");

UPDATE 不带 ON 子句的显式 JOIN 也可以工作：

FROM   tour_foreign 
       INNER JOIN tour_foreign_residence 
         ON ( tour_foreign.id = tour_foreign_residence.relation )
       JOIN (SELECT @rownum := 0) r

Try this, simply adding the @rownum to the start of the SELECT list and the @rownum reset as an implicit JOIN onto the end. I tested this against one of my databases with a similar JOIN and it appears to work correctly.

It looks peculiar to mix explicit JOINs with the implicit join against the @rownum reset pseudotable, but I don't know of a way to perform an explicit JOIN there when there is no common column. If anyone can improve that situation, please comment.

$this -> db -> query("
SELECT
       @rownum := @rownum + 1 rownum,
       tour_foreign.id, 
       tour_foreign.name, 
       tour_foreign_residence.name_re, 
       tour_foreign.service, 
       tour_foreign.date_go, 
       tour_foreign.date_back, 
       tour_foreign.term 
FROM   tour_foreign 
       INNER JOIN tour_foreign_residence 
         ON ( tour_foreign.id = tour_foreign_residence.relation ),
       (SELECT @rownum := 0) r
WHERE  tour_foreign.name LIKE "%' . $find . '%" 
        OR tour_foreign_residence.name_re LIKE "%' . $find . '%"
");

UPDATE an explicit JOIN without an ON clause also works:

FROM   tour_foreign 
       INNER JOIN tour_foreign_residence 
         ON ( tour_foreign.id = tour_foreign_residence.relation )
       JOIN (SELECT @rownum := 0) r

回复收藏 0 原文

通知家属抬走 2024-12-17 09:04:46

怎么样：

$this -> db -> query("SELECT @rownum := @rownum + 1 rownum, 
       t.* 
FROM   (SELECT tour_foreign.id, 
       tour_foreign.name, 
       tour_foreign_residence.name_re, 
       tour_foreign.service, 
       tour_foreign.date_go, 
       tour_foreign.date_back, 
       tour_foreign.term 
FROM   tour_foreign 
       INNER JOIN tour_foreign_residence 
         ON ( tour_foreign.id = tour_foreign_residence.relation ) 
WHERE  tour_foreign.name LIKE "%' . $find . '%" 
        OR tour_foreign_residence.name_re LIKE "%' . $find . '%") t, 
       (SELECT @rownum := 0) r 
")

what about:

$this -> db -> query("SELECT @rownum := @rownum + 1 rownum, 
       t.* 
FROM   (SELECT tour_foreign.id, 
       tour_foreign.name, 
       tour_foreign_residence.name_re, 
       tour_foreign.service, 
       tour_foreign.date_go, 
       tour_foreign.date_back, 
       tour_foreign.term 
FROM   tour_foreign 
       INNER JOIN tour_foreign_residence 
         ON ( tour_foreign.id = tour_foreign_residence.relation ) 
WHERE  tour_foreign.name LIKE "%' . $find . '%" 
        OR tour_foreign_residence.name_re LIKE "%' . $find . '%") t, 
       (SELECT @rownum := 0) r 
")

回复收藏 0 原文

~没有更多了~