-
Notifications
You must be signed in to change notification settings - Fork 849
嵌套查询
2881099 edited this page Jul 25, 2022
·
39 revisions
意见往集:https://github.com/dotnetcore/FreeSql/discussions/1192
需求版本:v3.2.666-preview20220725 (以上)
fsql.Select<User1>()
.Where(a => a.Id < 1000)
.WithTempQuery(a => new
{
item = a,
rownum = SqlExt.RowNumber().Over().PartitionBy(a.Nickname).OrderBy(a.Id).ToValue()
})
.Where(a => a.rownum == 1)
.ToList();
提示:支持多表嵌套查询,fsql.Select<User1, UserGroup1>()
SELECT *
FROM (
SELECT a.[Id], a.[Nickname], row_number() over( partition by a.[Nickname] order by a.[Id]) [rownum]
FROM [User1] a
WHERE a.[Id] < 1000
) a
WHERE (a.[rownum] = 1)
WithTempQuery + From<T2> 或 FromQuery(ISelect<T2>) 可实现无限联表
fsql.Select<User1>()
.Where(a => a.Id < 1000)
.WithTempQuery(a => new
{
item = a,
rownum = SqlExt.RowNumber().Over().PartitionBy(a.Nickname).OrderBy(a.Id).ToValue()
})
.Where(a => a.rownum == 1)
//.From<UserExt>() //普通联表
.FromQuery(fsql.Select<UserExt>().Where(b => b.Id > 0)) //子查询联表
//.FromQuery(fsql.Select<UserExt, UserGroup, xxx>().WithTempQuery((a,b,c) => new { ... })) //子多表查询联表
.InnerJoin((a, b) => a.item.Id == b.UserId)
.ToList((a, b) => new
{
user = a.item,
rownum = a.rownum,
userext = b
});
SELECT ...
FROM (
SELECT a.[Id], a.[Nickname], row_number() over( partition by a.[Nickname] order by a.[Id]) [rownum]
FROM [User1] a ) a
INNER JOIN ( SELECT a.[UserId], a.[Remark]
FROM [TwoTablePartitionBy_UserExt] a
WHERE (a.[UserId] > 0) ) b ON a.[Id] = b.[UserId]
WHERE (a.[rownum] = 1)
fsql.Select<User1>()
.WithTempQuery(a => new
{
user = a,
rownum = SqlExt.RowNumber().Over().PartitionBy(a.Nickname).OrderBy(a.Id).ToValue()
})
.Where(a => a.rownum == 1)
.FromQuery(fsql.Select<UserExt>().Where(b => b.UserId > 0).GroupBy(b => new { b.UserId, b.Remark }).WithTempQuery(b => new { b.Key, sum1 = b.Sum(b.Value.UserId) }))
.InnerJoin((a, b) => a.user.Id == b.Key.UserId)
.Where((a, b) => a.user.Nickname == "name03" || a.user.Nickname == "name02")
.ToList<TwoTablePartitionBy_UserDto>();
SELECT a.[rownum] as1
FROM (
SELECT a.[Id], a.[Nickname], row_number() over( partition by a.[Nickname] order by a.[Id]) [rownum]
FROM [TwoTablePartitionBy_User] a ) a
INNER JOIN ( SELECT a.[UserId], a.[Remark], sum(a.[UserId]) [rownum]
FROM [TwoTablePartitionBy_UserExt] a
WHERE (a.[UserId] > 0)
GROUP BY a.[UserId], a.[Remark] ) b ON a.[Id] = b.[UserId]
WHERE (a.[rownum] = 1) AND ((a.[Nickname] = N'name03' OR a.[Nickname] = N'name02'))
fsql.Select<Topic>()
.Where(a => fsql.Select<Topic>().As("b").Where(b => b.Id == a.Id).Any())
.ToList();
//SELECT a.[Id], a.[Title], a.[Clicks], a.[CreateTime], a.[CategoryId]
//FROM [Topic] a
//WHERE (exists(SELECT 1
// FROM [Topic] b
// WHERE (b.[Id] = a.[Id])
// limit 0,1))
提示:由于子查询的实体类与上层相同,使用 As("b") 指明别名,以便区分
fsql.Select<Topic>()
.Where(a => fsql.Select<Topic>().As("b").ToList(b => b.Id).Contains(a.Id))
.ToList();
//SELECT a.[Id], a.[Title], a.[Clicks], a.[CreateTime], a.[CategoryId]
//FROM [Topic] a
//WHERE (((a.[Id]) in (SELECT b.[Id]
// FROM [Topic] b)))
v1.8.0+ string.Join + ToList 实现将子查询的多行结果,拼接为一个字符串,如:"1,2,3,4"
fsql.Select<Topic>().ToList(a => new {
id = a.Id,
concat = string.Join(",", fsql.Select<StringJoin01>().ToList(b => b.Id))
});
//SELECT a.[Id], (SELECT group_concat(b.[Id] separator ',')
// FROM [StringJoin01] b)
//FROM [Topic] a
fsql.Select<Category>().ToList(a => new {
all = a,
first = fsql.Select<Topic>().Where(b => b.CategoryId == a.Id).First(b => b.Id),
count = fsql.Select<Topic>().Where(b => b.CategoryId == a.Id).Count(),
sum = fsql.Select<Topic>().Where(b => b.CategoryId == a.Id).Sum(b => b.Clicks),
max = fsql.Select<Topic>().Where(b => b.CategoryId == a.Id).Max(b => b.Clicks),
min = fsql.Select<Topic>().Where(b => b.CategoryId == a.Id).Min(b => b.Clicks),
avg = fsql.Select<Topic>().Where(b => b.CategoryId == a.Id).Avg(b => b.Clicks)
});
这是早期提供的嵌套查询方法
var sql = fsql.Select<User1>()
.Where(a => a.Id < 1000)
.ToSql(a => new
{
item = a,
rownum = SqlExt.RowNumber().Over().PartitionBy(a.Nickname).OrderBy(a.Id).ToValue()
}, FieldAliasOptions.AsProperty);
fsql.Select<User1>()
.WithSql(sql)
.Where("a.rownum = 1")
.ToList();
SELECT a.[Id], a.[Nickname]
FROM (
SELECT a.[Id], a.[Nickname], row_number() over( partition by a.[Nickname] order by a.[Id]) [rownum]
FROM [User1] a
WHERE a.[Id] < 1000
) a
WHERE (a.rownum = 1)