在SQLite3中，是否可以列出特定行？

发布于 2025-01-13 21:09:45 字数 1787 浏览 4 评论 0原文

假设我有一个表格：

+--------------+--------------+------+-----+
| ID        | Score   | email           | add | 
+--------------+--------------+------+-----+
| 123       | 88      | [email protected]   | somewhere | 
| 456       | 77      | [email protected]   |  somewhere   | 
| 789       | 88      | [email protected]   |   somewhere  |     
| 111       | 77      |[email protected]    |   somewhere  |    
| 555       | 77      | [email protected]   |   somewhere  |   
|444        | 88      | [email protected]   |    somewhere
| 222       | 77      | [email protected]   |  somewhere |
| 333       | 88     |[email protected]     | somewhere    |

我的问题是可以选择 Score 列并仅打印前 3 个 88 和 77 分数吗？

我试过了，但似乎只给了我 3 88 分

SELECT Score 
FROM Table_Name
WHERE Score = '88' OR Score = '77'
LIMIT 3

原文

Let's say I have a table:

+--------------+--------------+------+-----+
| ID        | Score   | email           | add | 
+--------------+--------------+------+-----+
| 123       | 88      | [email protected]   | somewhere | 
| 456       | 77      | [email protected]   |  somewhere   | 
| 789       | 88      | [email protected]   |   somewhere  |     
| 111       | 77      |[email protected]    |   somewhere  |    
| 555       | 77      | [email protected]   |   somewhere  |   
|444        | 88      | [email protected]   |    somewhere
| 222       | 77      | [email protected]   |  somewhere |
| 333       | 88     |[email protected]     | somewhere    |

My question is it possible to select Score column and ONLY print out first 3 88 and 77 Score?

I tried but it seems only give me 3 88 scores only

SELECT Score 
FROM Table_Name
WHERE Score = '88' OR Score = '77'
LIMIT 3

分享到QQ

分享到微博

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

发布评论

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

就此别过 2025-01-20 21:09:45

首先过滤表格，以便仅返回具有所需分数的行，然后使用 ROW_NUMBER() 窗口函数根据 Score 对每个 Score 的行进行排名>ID，以便您可以过滤掉超出每组前 3 行的行：

SELECT ID, Score, email, add
FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY Score ORDER BY ID) rn
  FROM Table_Name
  WHERE Score = '88' OR Score = '77'
)
WHERE rn <= 3;

对于 3.25.0 之前不支持窗口函数的 SQLite 版本，请使用相关子查询：

SELECT t1.*
FROM Table_Name t1
WHERE t1.Score = '88' OR t1.Score = '77'
  AND (SELECT COUNT(*) FROM Table_Name t2 WHERE t2.Score = t1.Score AND t2.ID <= t1.ID) <= 3;

First filter the table so that only the rows with the scores that you want are returned and then use ROW_NUMBER() window function to rank the rows of each Score based on ID so that you can filter out the rows that exceed the first 3 of each group:

SELECT ID, Score, email, add
FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY Score ORDER BY ID) rn
  FROM Table_Name
  WHERE Score = '88' OR Score = '77'
)
WHERE rn <= 3;

For versions of SQLite prior to 3.25.0 that do not support window functions use a correlated subquery:

SELECT t1.*
FROM Table_Name t1
WHERE t1.Score = '88' OR t1.Score = '77'
  AND (SELECT COUNT(*) FROM Table_Name t2 WHERE t2.Score = t1.Score AND t2.ID <= t1.ID) <= 3;

回复收藏 0 原文

~没有更多了~