c# - 是否可以通过 PostgreSQL 的某些 IF 条件使用 WHERE?
问题描述
我对报告有以下 SQL 请求。
select customers."AppId", second_dep "SecondDeps", first_dep "FirstDeps",
customers_count "Customers", registrations "Registrations"
From (select Count("AppId") as customers_count, "AppId"
FROM "Customers"
join "Advertisers" A on "Customers"."AdvertiserId" = A."AdvertiserId"
join "Categories" C2 on "Customers"."CategoryId" = C2."CategoryId"
where A."Name" in (:AdvertiserNames)
AND C2."Name" = :CategoryName
GROUP BY "AppId"
) as customers
left join
(select C."AppId", count(CE.*) as second_dep
from "CustomerEvents" as CE
inner join "Customers" C on CE."CustomerId" = C."CustomerId"
WHERE "EventType" = 'deposit'
and "Again" = TRUE
GROUP BY C."AppId") as dep2 on customers."AppId" = dep2."AppId"
left join
(select C."AppId", count(CE.*) as first_dep
from "CustomerEvents" as CE
inner join "Customers" C on CE."CustomerId" = C."CustomerId"
WHERE "EventType" = 'deposit'
and "Again" = false
GROUP BY C."AppId") as dep on customers."AppId" = dep."AppId"
left join
(select C."AppId", count(CE.*) as registrations
from "CustomerEvents" as CE
inner join "Customers" C on CE."CustomerId" = C."CustomerId"
WHERE "EventType" = 'registration'
GROUP BY C."AppId") as regs on regs."AppId" = customers."AppId";
有问题的字符串是
where A."Name" in (:AdvertiserNames)
AdvertiserNames
如果为空,我想跳过它。可能吗?好的,我可以在代码端检查它,但是这种方式会导致我复制整个请求,但会有一些小的差异(我的意思是如果AdvertiserNames
是空运行 SQL,没有where A."Name" in (:AdvertiserNames)
)。或者我可以使用连接来获得合适的 SQL。我也不喜欢这种方式。
关于我的技术栈。它是带有 PostgreSQL 的 .NET Core 2.2。这是整个报告方法的代码:
public IQueryable<ByApplicationsReportModel> ByApplications(string category, List<string> advertisers)
{
var rawSql = new RawSqlString(@"
select customers.""AppId"", second_dep ""SecondDeps"", first_dep ""FirstDeps"",
customers_count ""Customers"", registrations ""Registrations""
From (select Count(""AppId"") as customers_count, ""AppId""
FROM ""Customers""
join ""Advertisers"" A on ""Customers"".""AdvertiserId"" = A.""AdvertiserId""
join ""Categories"" C2 on ""Customers"".""CategoryId"" = C2.""CategoryId""
where A.""Name"" in (@AdvertiserNames)
AND C2.""Name"" = @CategoryName
GROUP BY ""AppId""
) as customers
left join
(select C.""AppId"", count(CE.*) as second_dep
from ""CustomerEvents"" as CE
inner join ""Customers"" C on CE.""CustomerId"" = C.""CustomerId""
WHERE ""EventType"" = 'deposit'
and ""Again"" = TRUE
GROUP BY C.""AppId"") as dep2 on customers.""AppId"" = dep2.""AppId""
left join
(select C.""AppId"", count(CE.*) as first_dep
from ""CustomerEvents"" as CE
inner join ""Customers"" C on CE.""CustomerId"" = C.""CustomerId""
WHERE ""EventType"" = 'deposit'
and ""Again"" = false
GROUP BY C.""AppId"") as dep on customers.""AppId"" = dep.""AppId""
left join
(select C.""AppId"", count(CE.*) as registrations
from ""CustomerEvents"" as CE
inner join ""Customers"" C on CE.""CustomerId"" = C.""CustomerId""
WHERE ""EventType"" = 'registration'
GROUP BY C.""AppId"") as regs on regs.""AppId"" = customers.""AppId""");
var advertisersParam = new NpgsqlParameter("AdvertiserNames",
string.Join(",", advertisers) );
var categoryParam = new NpgsqlParameter("CategoryName", category);
return _context.ByApplicationsReportModels
.FromSql(rawSql, categoryParam, advertisersParam);
}
有任何想法吗?
解决方案
您可以尝试更改where A.""Name"" in (@AdvertiserNames)
为where (A.""Name"" in (@AdvertiserNames) or @AdvertiserNames = '')
.
推荐阅读
- javascript - 在 JS 中显示用户输入的文本
- angular - 在prime ng列表框中添加虚拟滚动
- sql - 插入行时有没有办法避免“此处不允许列”错误
- php - Ckeditor textarea 的值在第一次提交时未发布,但它适用于第二次提交,其他字段(如图像属性等)是不可编辑的
- reactjs - 使用 React 和 Firestore 数据库的无限循环图像 - 如何获取我的数据?
- next.js - Next JS nextjs 重写未按预期工作
- filepond - 对 Filepond 实例进行编辑后,如何绕过跳过还原和上传?
- ios - 一个模型如何通知另一个模型特定属性发生了变化?
- py2app - 在 py2app 中隐藏默认脚本
- python - pandas 比应用 lambda 在每一行中应用逻辑更快的方法?