如何在c#linq查询中按业务类型代码选择前(5)个贡献者组
本文关键字：选择 贡献者 代码 类型 c#linq 查询 业务 | 更新日期: 2023-09-27 18:26:23
我想选择在业务类型代码中贡献更多的公司，并按BTypeCode对其进行分组。选择顶部给定的数字（例如Select Top (5) * from transactions）。给定的数字可以是任何正整数。
表格就像这个
| Bno | BusinessName | BTypeCode | BLe | BInterest | BAdmin | BPenalty |TotalBus | TotalCollected
+-----------+--------------------------+-----------+--------+-----------+--------+----------+---------+-----------------+
| B40707194 | INDEPENDENT DEVELOPMENT | 11 | 29,92 | -284,68 | 17,94 | 0 | -236,82 | -473,64 |
| B40703250 | AQUA PLUMBING | 5 | 0 | -661,31 | 0 | 0 | -661,31 | -1322,62 |
| B30718409 | MM CONSULTING PTY LTD | 23 | -0,79 | -2,63 |-0,47 | 0 |-3,89 | -7,78 |
| B30711446 | S SULMAN & SEIDER | 27 | -83,17 | 0 | -49,9 | 0 | -133,07 | -266,14 |
| B30704656 | NAUMANN CONSTRUCTION | 23 | 0 | -1,47 | 0 | 0 | -1,47 | -2,94 |
| B30703609 | SWITCH ON MARKETING CC | 27 | -128,68| 0 | -77,2 | 0 | -205,88 | -411,76 |
| B20733582 | MAINLINE CARRIERS BPK | 11 | -19,45 | -87,5 | -11,66 | -6,24 | -124,89 | -249,78 |
| B20729994 | MIDRAND HYDRAULICS CC | 23 | -17,85 | -44,33 | -10,7 | 0 | -72,88 | -145,76 |
| B20722395 | DOLPHIN SUPERMARKET | 5 | 57,04 | -76,65 | -442,22| -54,61 | 0 | -516,44 |
| B20701738 | MEISES HALT FUELS | 12 | -88,93 | -110,28 | 41,35 | 0 |0 | -157,86 |
| B10737056 | PROTON INT REALTY | 26 | 21,61 | -31,84 | 12,96 | 0 | 2,73 | 5,46 |
| B10729301 | NED HERV GEMEENTE | 23 | 3,31 | -5,7 | 1,98 | 0 |-0,41 | -0,82 |
在下面，排名第一的假设是这样的
BTypeCode:5
| Bno | BusinessName | BTypeCode | BLe | BInterest | BAdmin | BPenalty |TotalBus | TotalCollected
+-----------+--------------------------+-----------+--------+-----------+--------+----------+---------+-----------------+
| B40703250 | AQUA PLUMBING | 5 | 0 | -661,31 | 0 | 0 | -661,31 | -1322,62 |
BTypeCode:11
| Bno | BusinessName | BTypeCode | BLe | BInterest | BAdmin | BPenalty |TotalBus | TotalCollected
+-----------+--------------------------+-----------+--------+-----------+--------+----------+---------+-----------------+
| B40707194 | INDEPENDENT DEVELOPMENT | 11 | 29,92 | -284,68 | 17,94 | 0 | -236,82 | -473,64 |
BTypeCode:12
| Bno | BusinessName | BTypeCode | BLe | BInterest | BAdmin | BPenalty |TotalBus | TotalCollected
+-----------+--------------------------+-----------+--------+-----------+--------+----------+---------+-----------------+
| B20701738 | MEISES HALT FUELS | 12 | -88,93 | -110,28 | 41,35 | 0 |0 | -157,86 |
BTypeCode:23
| Bno | BusinessName | BTypeCode | BLe | BInterest | BAdmin | BPenalty |TotalBus | TotalCollected
+-----------+--------------------------+-----------+--------+-----------+--------+----------+---------+-----------------+
| B20729994 | MIDRAND HYDRAULICS CC | 23 | -17,85 | -44,33 | -10,7 | 0 | -72,88 | -145,76 |
BTypeCode:26
| Bno | BusinessName | BTypeCode | BLe | BInterest | BAdmin | BPenalty |TotalBus | TotalCollected
+-----------+--------------------------+-----------+--------+-----------+--------+----------+---------+-----------------+
| B10737056 | PROTON INT REALTY | 26 | 21,61 | -31,84 | 12,96 | 0 | 2,73 | 5,46 |
BTypeCode:27
| Bno | BusinessName | BTypeCode | BLe | BInterest | BAdmin | BPenalty |TotalBus | TotalCollected
+-----------+--------------------------+-----------+--------+-----------+--------+----------+---------+-----------------+
| B30703609 | SWITCH ON MARKETING CC | 27 | -128,68| 0 | -77,2 | 0 | -205,88 | -411,76 |
我当前的代码看起来是这样的，但它并没有给我想要的。
DateTime startMonth, endMonth;
long BTypeCode;
int Topnum;
var query = (from _transaction in _entities.Transactions
join _cd in _entities.Organisations on _transaction.Bno equals _cd.Bno
join btypecodes in _entities.BusinessTypeCodes on _transaction.BTypeCode equals btypecodes.BTypeCode
where (_transaction.TransactionDate >= startMonth || startMonth == DateTime.MinValue)
&& (_transaction.TransactionDate <= endMonth || endMonth == DateTime.MaxValue)
&& (_transaction.BTypeCode == BTypeCode || BTypeCode < 0)
group new { _transaction,btypecodes } by new
{
_transaction.Id,
_transaction.Bno,
_cd.BusinessName,
_transaction.TransactionDate,
_transaction.BTypeCode,
btypecodes.Description
} into grp
orderby grp.Sum(e => e._transaction.TotalCollected)
select new
{
TransactionId = grp.Key.Id,
Bno = grp.Key.Bno,
BusinessName = grp.Key.BusinessName,
TransactionDate = grp.Key.TransactionDate,
BLe = grp.Sum(sum => sum._transaction.BLe),
BInterest = grp.Sum(sum => sum._transaction.BInterest),
BAdmin = grp.Sum(sum => sum._transaction.BAdmin),
BPenalty = grp.Sum(sum => sum._transaction.BPenalty),
TotalBus = grp.Sum(sum => sum._transaction.TotalBus),
TotalCollected = grp.Sum(sum => sum._transaction.TotalCollected),
TypeCode = grp.Key.BTypeCode + " - " + grp.Key.Description,
BTypeCode = grp.Key.BTypeCode,
}).Take(Topnumb);
请帮帮我。
而不是
orderby grp.Sum(e => e._transaction.TotalCollected)
尝试从每组中选择第一个项目
select grp.OrderBy(x => _transaction.TotalCollected).First()
select new {
...
}
您应该仅按BTypeCode对事务进行分组。然后，在每个组中，您将按TotalCollected排序，获得最前面的n事务。
from _transaction in _entities.Transactions
join _cd in _entities.Organisations on _transaction.Bno equals _cd.Bno
join btypecodes in _entities.BusinessTypeCodes
on _transaction.BTypeCode equals btypecodes.BTypeCode
where (_transaction.TransactionDate >= startMonth || startMonth == DateTime.MinValue)
&& (_transaction.TransactionDate <= endMonth || endMonth == DateTime.MaxValue)
&& (_transaction.BTypeCode == BTypeCode || BTypeCode < 0)
group _transaction by _transaction.BTypeCode into grp
select new
{
BTypeCode = grp.Key,
Transaction = grp.OrderBy(g => g.TotalCollected).Take(Topnumb)
}
顺便说一下，我会有条件地添加谓词（where ...）。这使查询优化器的查询更干净、更不复杂：
IQueryable<Transaction> transactions = _entities.Transactions;
if (startMonth > DateTime.MinValue)
{
transactions = transactions.Where(t => t.TransactionDate >= startMonth);
}
if (endMonth < DateTime.MaxValue)
{
transactions = transactions.Where(t => t.TransactionDate <= endMonth);
}
var query = (from _transaction in transactions
....