对在一列上获得 DISTINCT 有点模糊?
我见过这个问题的几个不同版本,但很难将其应用到我需要的地方...
MS SQL Server 2008 查询:
SELECT Receipts.ReceiptID, Receipts.UserID, Receipts.UserCardID, FolderLink.ReceiptFolderLinkID
FROM dbo.tbl_ReceiptFolderLnk AS FolderLink
INNER JOIN dbo.tbl_Receipt AS Receipts ON FolderLink.ReceiptID = Receipts.ReceiptID
**
ReceiptID UserID UserCardID ReceiptFolderLinkID
-------------------- -------------------- -------------------- --------------------
1 1 3 2
2 1 3 3
3 1 3 10
4 1 4 11
5 1 4 13
6 2 5 14
3 1 3 15
4 1 4 16
**
现在我想在 ReceiptID 上获取不同的值。使用不同的效果不会像不同的效果适用于整行一样。
如有帮助,将不胜感激。
谢谢!
I've seen a few different versions of this question but having difficulty applying it to what I need...
MS SQL Server 2008 query:
SELECT Receipts.ReceiptID, Receipts.UserID, Receipts.UserCardID, FolderLink.ReceiptFolderLinkID
FROM dbo.tbl_ReceiptFolderLnk AS FolderLink
INNER JOIN dbo.tbl_Receipt AS Receipts ON FolderLink.ReceiptID = Receipts.ReceiptID
**
ReceiptID UserID UserCardID ReceiptFolderLinkID
-------------------- -------------------- -------------------- --------------------
1 1 3 2
2 1 3 3
3 1 3 10
4 1 4 11
5 1 4 13
6 2 5 14
3 1 3 15
4 1 4 16
**
Now I would like to get distinct values on ReceiptID. Using distinct will not work as distinct works on the entire row.
Help would be appreciated.
Thanks!
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(5)
如果您希望原始查询中的所有结果列并且希望每个 ReceiptID 仅显示一次,则需要使用 GROUP BY。
喜欢:
If you want all the results columns in your original query and want each ReceiptID to only show up one time, you'll need to use GROUP BY.
Like:
请求具有相同收据 ID 的多行之一是不明确的。您想如何从其他行中选择这一行?
Asking for one of many rows with the same receiptID is ambiguous. How do you want to choose that one row from the others?
我已经使用 MIN 或 MAX 聚合函数完成了此操作,该函数似乎工作得很好,并且比我见过的大多数其他方法更容易编写。
所以它会是这样的:
在这种情况下,ColumnA 将是不同的列。
I've done this with the MIN or MAX aggregate functions which seems to work pretty well and is far simpler to write than most other methods I've seen.
So it'd be something like this:
In this case ColumnA would be the distinct column.
DISTINCT 对于整行来说是不同的。尝试
GROUP BY ReceiptID
,当然其他列的值可能没有意义,因为它只会选择一行。DISTINCT is distinct for the whole row. Try
GROUP BY ReceiptID
, of course the values for the other columns may not make sense, because it will just pick one row.这将消除对
GROUP BY
或DISTINCT
的需要,这可能非常不理想。另外,通过更改
ORDER BY
子句,您可以选择在存在多个时要返回哪个FolderLink
。This will eliminate the need for
GROUP BY
orDISTINCT
which may be quite inoptimal.Also by changing the
ORDER BY
clause you can choose whichFolderLink
you want to return if there are muptiple.