分组

1、如使用Linq  则为

var list = from c in context.MerchantDetialSummary

                           where c.TransactionDate.StartsWith("1004")

                           group c by c.MerchantID into MyGroup

                           orderby MyGroup.Key

                           select new { MyGroup.Key, totalTransAmt = MyGroup.Sum(row => row.TransactionAmt), totalTransCount = MyGroup.Sum(row => row.TransactionLots), totalFeeMoney = MyGroup.Sum(row => row.TransactionIncome) };

                foreach (var item in list)

                {

                    File.AppendAllText(@"e:/log.txt", string.Format("{0}/t{1}/t{2}/r/n", item.Key, item.totalFeeMoney, item.totalTransAmt, item.totalTransCount), Encoding.Default);

                }

2、若使用Entity Sql 则为

select s.MerchantID,sum(s.TransactionIncome) ,sum(s.TransactionLots),sum(s.TransactionAmt) from SqlBtmsModel.MerchantDetialSummary as s

where s.TransactionDate like '1004%'

group by s.MerchantID

3、Sql

 using (BtmsModels.SqlBtmsModel context = new BtmsModels.SqlBtmsModel())
            {
                var dbList = context.CreateQuery<DbDataRecord>(
                                                  @"select detail.MerchantID,
                                                SUM(detail.TransactionIncome) as a ,
                                                SUM(detail.TransactionAmt) as b,
                                                SUM(detail.TransactionLots) as c
                                                from MerchantDetialSummary as  detail
                                                where detail.TransactionDate like '" + tranMonths + "%' group by  detail.MerchantID").ToList();

                foreach (var item in dbList)
                {
                    MerchantDetialSummary detail = new MerchantDetialSummary();
                    detail.MerchantID = item[0].ToString();
                    detail.TransactionIncome = decimal.Parse(item[1].ToString());
                    detail.TransactionAmt = decimal.Parse(item[2].ToString());
                    detail.TransactionLots = int.Parse(item[3].ToString());
                    list.Add(detail);
                }
            }