如何使用SELECT
语句获取基于FromLinkID
和ToLinkID
组合的DISTINCT
结果?
如果我有一个包含以下数据的List
,我该如何在LINQ中获取我想要的内容?
SELECT DISTINCT FromLinkId, ToLinkId
FROM mytable
为了选择每个(FromLinkId, ToLinkId)
的一条记录(使用最小的id
),假设使用SQL Server
:
SELECT *
FROM (
SELECT *, ROW_NUMBER() OVER (PARTITION BY FromLinkId, ToLinkId ORDER BY id) AS rn
FROM mytable
) q
WHERE rn = 1
WHERE rn = 1
。 - Quassnoi试试这个:
Select Distinct FromLinkId LinkId From MyTable
Union
Select Distinct ToLinkId LinkId From MyTable
SELECT Name, FromLinkId, ToLinkId FROM MyTable GROUP BY FromLinkId, ToLinkId -- if there are more than one record with the same (FromLinkId, ToLinkId) pair, only one of them is displayed.
你可以这样做:
SELECT MAX(ID), MAX(Name), FromLinkID, ToLinkID FROM MyTable
GROUP BY FromLinkID, ToLinkID
SELECT t.* FROM
(SELECT MAX(ID) as MaxID, FromLinkID, ToLinkID FROM MyTable
GROUP BY FromLinkID, ToLinkID) d
LEFT JOIN MyTable t ON d.MaxID=t.ID
LINQ解决方案:
List<MyObj> distinctResults = allResults
.GroupBy(p => new {p.FromLinkID, p.ToLinkID} )
.Select(g => g.First())
.ToList();
列表 distinctResults = allResults .Select(p => new {p.FromLinkID, p.ToLinkID} ) .Distinct() .ToList();
sql
和linq
:你想要一个 LINQ 查询还是 SQL 语句? - p.campbell