SQL 到 LINQ.左外联接并按组合在一起

本文关键字：组合 在一起 LINQ SQL | 更新日期: 2023-09-27 18:36:03

我有表"客户"和列"ID"，"国家"，"性别"。我想要的是在同一查询中获取按国家/地区字段分组的客户总数以及男性和女性总数。我已经在 sql 查询中完成了。它有效。但是找不到如何在 LINQ 中实现它。

SELECT  c.country, COUNT(c.id) AS TotalClients, 
ISNULL(max(c2.total),0) AS TotalMales,
COUNT(c.id)-ISNULL(MAX(c2.total),0) AS TotalFemails,
FROM Clients c
LEFT OUTER JOIN(
SELECT country, count(id) AS total FROM Clients 
WHERE sex='male'
GROUP BY country
) c2 
ON c2.country = c.country
GROUP BY c.country
ORDER BY c.country

有什么建议吗？

SQL 到 LINQ.左外联接并按组合在一起

var query = (from c in db.Clients
            let TotalMales = (
            from c2 in db.Clients.Where(a=>a.Sex=='male') where c.Country=c2.Country select c2).Count()
            group c by new {c.Country, TotalMales}
            into g
            select new {
                g.Key.Country,
                TotalClients = g.Count(),
                TotalMales = g.Key.TotalMales,
                TotalFemales = g.Count()-TotalMales
            }).OrderBy(s=>s.Country);

考虑使用更简单的查询：

SELECT
  c.Country,
  SUM(CASE WHEN c.Sex = 'Male' THEN 1 ELSE 0 END) as TotalMales,
  SUM(CASE WHEN c.Sex = 'Female' THEN 1 ELSE 0 END) as TotalFemales,
  COUNT(*) as TotalClients
FROM Clients c
GROUP BY c.Country
ORDER BY c.Country

大致翻译为：

from c in Clients
group c by c.Country into g
order by g.Key
select new {
  Country = g.Key,
  Males = g.Count(y => y.Sex == "Male"),
  Females = g.Count(x => x.Sex == "Female"),
  Total = g.Count()
}
相关文章：