基于 ROW_Number 的 SQL 查询不起作用

发布于 2024-10-02 20:07:30 字数 574 浏览 8 评论 0原文

基本上我正在尝试对仅包含图像的页面进行查询。我需要提供 tripID 号，然后提供 ROWID（因为可能有多个图像）才能接收单个图像。我将循环直到每个图像都位于 html 中相应的图像框中。

这段代码似乎不起作用（我得到无效的列名“ROWID”），但如果我删除 AND ROWID='1'，它会返回所有图像及其行 ID，如下所示：

ROWID      PHOTO
    1      32jjr3h2jh23hj4h32jh42ll23j42 
    2       HU8308DJAOID9ASIDJI32C89EE29

Select ROW_NUMBER() OVER (ORDER BY Photo ASC) AS ROWID, TBL_Photo.Photo
        From TBL_Photo
        left join TBL_TripDetails
        ON TBL_Photo.TripID=TBL_TripDetails.pkiTripID
        Where pkiTripID = '121' AND ROWID = '1'

原文

Basically I am trying to do a query to a page that holds just an image. I need to provide the tripID number and then the ROWID (as there could be multiple images) to receive a single image. I will be looping until each image is in its corresponding image box in html.

This code doesn't seem to work(I get Invalid column name 'ROWID'), but if I remove the AND ROWID='1' it returns all the images and its row id like this:

ROWID      PHOTO
    1      32jjr3h2jh23hj4h32jh42ll23j42 
    2       HU8308DJAOID9ASIDJI32C89EE29

Select ROW_NUMBER() OVER (ORDER BY Photo ASC) AS ROWID, TBL_Photo.Photo
        From TBL_Photo
        left join TBL_TripDetails
        ON TBL_Photo.TripID=TBL_TripDetails.pkiTripID
        Where pkiTripID = '121' AND ROWID = '1'

分享到QQ

分享到微博

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

发布评论

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

小霸王臭丫头 2024-10-09 20:07:30

您不能在 WHERE 子句中引用列别名 - 您需要使用子查询或 CTE：

子查询示例：

SELECT x.rowid,
       x.photo
  FROM (SELECT ROW_NUMBER() OVER (ORDER BY p.photo) AS ROWID, 
               p.photo
          FROM TBL_PHOTO p
     LEFT JOIN TBL_TRIPDETAILS td ON td.pkitripid = p.tripid 
         WHERE td.pkiTripID = '121') x
 WHERE x.rowid = 1

CTE 示例：

WITH example AS (
        SELECT ROW_NUMBER() OVER (ORDER BY p.photo) AS ROWID, 
               p.photo
          FROM TBL_PHOTO p
     LEFT JOIN TBL_TRIPDETAILS td ON td.pkitripid = p.tripid 
         WHERE td.pkiTripID = '121')
SELECT x.rowid,
       x.photo
  FROM example x
 WHERE x.rowid = 1

性能

这两个选项之间没有性能差异，但并非所有选项都支持 WITH 语法数据库。

You can't reference a column alias in the WHERE clause -- you need to use a subquery or a CTE:

Subquery Example:

SELECT x.rowid,
       x.photo
  FROM (SELECT ROW_NUMBER() OVER (ORDER BY p.photo) AS ROWID, 
               p.photo
          FROM TBL_PHOTO p
     LEFT JOIN TBL_TRIPDETAILS td ON td.pkitripid = p.tripid 
         WHERE td.pkiTripID = '121') x
 WHERE x.rowid = 1

CTE example:

WITH example AS (
        SELECT ROW_NUMBER() OVER (ORDER BY p.photo) AS ROWID, 
               p.photo
          FROM TBL_PHOTO p
     LEFT JOIN TBL_TRIPDETAILS td ON td.pkitripid = p.tripid 
         WHERE td.pkiTripID = '121')
SELECT x.rowid,
       x.photo
  FROM example x
 WHERE x.rowid = 1