-
Notifications
You must be signed in to change notification settings - Fork 849
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
UNION ALL考虑一下呗 #213
Comments
如果是所有字段查询,这样是可以。 如果 ToList(a=>) 指定字段查询,这样就不行了啊。因为两个 ISelect 查询的实体可能不是同一个,字段名可能有差异。 |
嗯,ISelect 不行, 要 Expression<Action<ISelect<...>>> 这种表达式,可以解析的才行。 |
感觉FreeSql 没有 那种 |
提供了也不行,Select里面是固定的类型 |
ISelect<T> Select<T,TResult>(
this ISelect<T> select,
Expression<Func<T, TResult>> selector)
// 通过解析selector来确定select 字段 这样是否可行 |
Select里面是固定的类型,如果是两个相同的实体类就没问题。可以用一个Select表示两个查询的字段确定。 |
子语句用一个新的ISelect<> 是否可行 // union 设想
fsql.Select<SongA>()
.Union<SongA, SongB>(
fsql.Select<SongB>().SelectFields(s => s.NameB)) // Union<T0, T1>(ISelect<T1>) : ISelect<T0>
.Union<SongA, SongC, AuthorC>(
fsql.Select<SongC>().From<AuthorC>((a,b)=>a)
.SelectFields(s => s.NameC)) // Union<T0, T1, T2>(ISelect<T1, T2>) : ISelect<T0>
.ToList(a => a.NameA); // a是 SongA类型
// join子语句 设想
fsql.Select<Song>()
.Join<Song, Author>(fsql.Select<Author>().SelectFields(s => s.AuthorName),
(a,b)=>a.Id == b.SongId, "INNER JOIN") //返回 ISelect<Song,Author>
.ToList((a, b) => new // a是 Song, b是Author
{
SongName = a.Name,
AuthorName = b.Name
}); |
@2881099 Union 也考虑一下呗,搞个Union(string sql, object parms = null) |
union 和 union all 有区别,每个数据库有点差异,这个有空了再研究 |
好的,就是想到既然可以在join的位置加raw sql,那何不提供个方法在最后加raw sql呢 上面说错了,union 不是最后,当我没说吧 |
var testUnionAll = select
.AsTable((_, old) => "(SELECT * as1 FROM [tb_topic22] where id = 10)")
.AsTable((_, old) => "(SELECT * as1 FROM [tb_topic22] where id = 11)")
.ToSql(a => new
{
a.Id,
a.Clicks
});
//或者
var testUnionAll = select
.WithSql("SELECT * as1 FROM [tb_topic22] where id = 10")
.WithSql("SELECT * as1 FROM [tb_topic22] where id = 11")
.ToSql(a => new
{
a.Id,
a.Clicks
}); 上面的代码得到如下SQL:不知道算不算已经有 union all 功能?之前没想到这样用 SELECT * from (SELECT a.[Id] as1, a.[Clicks] as2
FROM (SELECT * as1 FROM [tb_topic22] where id = 10) a) ftb
UNION ALL
SELECT * from (SELECT a.[Id] as1, a.[Clicks] as2
FROM (SELECT * as1 FROM [tb_topic22] where id = 11) a) ftb |
虽然感觉有点抽象,不过这个确实可以解决一部分问题, var testUnionAll = fsql.Select<Edi>()
.AsTable((_, old) => "(SELECT EDI_ID FROM [EDI] where EDI_ID = 101)")
.AsTable((_, old) => "(SELECT EDI_ID FROM [EDI] where EDI_ID = 21701)")
.OrderBy(a=>a.Id)
.ToSql(a => new
{
a.Id,
}); 生成sql SELECT * from (SELECT a.[EDI_ID] as1
FROM (SELECT EDI_ID FROM [EDI] where EDI_ID = 101) a
ORDER BY a.[EDI_ID]) ftb
UNION ALL
SELECT * from (SELECT a.[EDI_ID] as1
FROM (SELECT EDI_ID FROM [EDI] where EDI_ID = 21701) a
ORDER BY a.[EDI_ID]) ftb 运行会报 SELECT * from (SELECT a.[EDI_ID] as1
FROM (SELECT EDI_ID FROM [EDI] where EDI_ID = 101) a) ftb
UNION ALL
SELECT * from (SELECT a.[EDI_ID] as1
FROM (SELECT EDI_ID FROM [EDI] where EDI_ID = 21701) a) ftb
ORDER BY ftb.as1 这就是我上面说的,union不是最后,order by 比它要更下面 |
OrderBy Take 这些是统一每个子查询都生效,例如下面的场景: SELECT * from (SELECT a.[EDI_ID] as1
FROM (SELECT TOP 10 EDI_ID FROM [EDI] where EDI_ID = 101) a) ftb
UNION ALL
SELECT * from (SELECT a.[EDI_ID] as1
FROM (SELECT TOP 10 EDI_ID FROM [EDI] where EDI_ID = 21701) a) ftb 如果是先 UNION ALL 再 排序 + TOP,就要再套一层了 |
ISelect<> 能否增加Union(string sql, string joinKey = "UNION ALL")方法,
直接在生成的SQL 后面 插入
UNION ALL {sql}
可重复调用
The text was updated successfully, but these errors were encountered: