如何在表中获取仅与一个“B”属性相关的“A”属性?(SQL/LINQ)

zrfyljdw  于 2022-12-06  发布在  其他
关注(0)|答案(2)|浏览(139)

假设我有这个表:
| 图像|周长|
| - -|- -|
| 一种|一个|
| B|一个|
| B| 2个|
| 日|三个|
| 电子|一个|
我想返回只与一个周长有关的图像。
预期结果将是图像“a、d、e,”因为图像“b”与周边“1”和“2”有关系。
目的是在删除周边时删除相关的图像,但如果它链接到另一个周边,我就不能删除它。
如何使用LINQ编写此查询?
我想应该是这样的:

SELECT "ImageId" 
WHERE "PerimeterId" = PerimeterId IN 
(
SELECT "ImageId"
GROUP BY "ImageId"
HAVING COUNT("PerimeterId") = 1
)

但我不知道如何将其转换为LINQ。

tcomlyy6

tcomlyy61#

您可以使用NOT EXISTS

var query = dbo.Table
    .Where(t => !dbo.Table.Any(t2 => t.Image = t.Image && t.Perimeter != t2.Perimeter));
bvjxkvbb

bvjxkvbb2#

您可以很容易地将其修改为只选择图像部分。()”组计算,那么您将希望查看.SelectMany()LINQ方法.这允许您“将划分到组中的数据重新组合在一起.”虽然您的需要是只返回“每个组中的一个,”可以在SSDT 2015的“C#交互式窗口”中运行该程序:

struct imagePerimeter { //this might be whatever object type it is for you...
    public string Image { get; set; } //a,b,b,d,e
    public int Perimeter { get; set; } //1,1,2,3,1
}
Func<string, int, imagePerimeter> newIP = (i, p) => new imagePerimeter() { Image = i, Perimeter = p };
List<imagePerimeter> results = new List<imagePerimeter>() { {newIP("a",1) }
    ,{newIP("b",1) }
    ,{newIP("b",2) }
    ,{newIP("d",3) }
    ,{newIP("e",1) } };

Func<imagePerimeter, string> ipImage = (ip) => ip.Image; //the Func's "ipImage" and "newIP" could just be inlined into LINQ, but it helps to see and debug at times IMO. 
var imagesWithOnePerimeter = results.GroupBy<imagePerimeter, string>(ipImage) //even in SQL, the "GROUP BY" conceptually comes first, in LINQ, it comes first in code too!
    .Select(grp => new { Image = grp.Key, PerimeterCount = grp.Count(), Details = grp }) //there's probably a more technical term, but notice how we "carry forward" the original reference to [grp]
    .Where(subTotals => subTotals.PerimeterCount == 1)
    .SelectMany(filtered => filtered.Details.AsEnumerable())
    .ToList();

相关问题