英文:
SQL Server : how not to display subsequent results if they are the same as the previous ones?
问题
我需要一个查询，该查询将显示来自此表的结果：
| 发票 | 位置 |
|---|---|
| 单元格1 | 单元格1 |
| 单元格1 | 单元格2 |
| 单元格2 | 单元格3 |
| 单元格1 | 单元格4 |
期望的结果如下：
| 发票 | 位置 |
|---|---|
| 单元格1 | 单元格1 |
| 单元格2 | |
| 单元格4 | |
| 单元格2 | 单元格3 |
我希望能得到一些建议。
英文:
I need a query that will display the result from this table:
| invoice | position |
|---|---|
| Cell 1 | Cell 1 |
| Cell 1 | Cell 2 |
| Cell 2 | Cell 3 |
| Cell 1 | Cell 4 |
The desired result is like this:
| invoice | position |
|---|---|
| Cell 1 | Cell 1 |
| Cell 2 | |
| Cell 4 | |
| Cell 2 | Cell 3 |
I'm hoping for some tips
答案1
得分: 1
我们可以尝试在这里使用 ROW_NUMBER() 函数以及 CASE 表达式：
SELECT
CASE WHEN ROW_NUMBER() OVER (
PARTITION BY invoice
ORDER BY CAST(SUBSTRING(position,
CHARINDEX(' ', position) + 1,
LEN(position)) AS INT)
) = 1 THEN invoice END AS invoice
FROM yourTable
ORDER BY
invoice,
CAST(SUBSTRING(position, CHARINDEX(' ', position) + 1, LEN(position)) AS INT);
英文:
We could try using the ROW_NUMBER() function here along with a CASE expression:
<!-- language: sql -->
SELECT
CASE WHEN ROW_NUMBER() OVER (
PARTITION BY invoice
ORDER BY CAST(SUBSTRING(position,
CHARINDEX(' ', position) + 1,
LEN(position)) AS INT)
) = 1 THEN invoice END AS invoice
FROM yourTable
ORDER BY
invoice,
CAST(SUBSTRING(position, CHARINDEX(' ', position) + 1, LEN(position)) AS INT);
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论