Question

如果账单与账单相加,如何对数据进行分类?

i 表格:

meetingId | bill
------------------
     a    |     6
     b    |     7
     c    |     1
     a    |     5
     a    |     3
     b    |     4
     g    |     2

expected results : a = 6+5+3 = 14 limit is 10 --> 10 and 4
b = 7+4 = 11 so limit is 10 --> 10 and 1
c and g not over the limit.

meetingId | bill
------------------
     a    |     10
     a    |     4
     b    |     10
     b    |     1
     c    |     1
     g    |     2

i 审判,原因为何,但若情况如此,我就坐下来。

我:

 SELECT NO_ORDRE
      ,ORDRE.CODE_CLIENT As CodeCl 
      ,[CODE_DEST]  
      ,ORDRE.RS_NOM As OrdreRS
      ,ORDRE.ADRESSE As OrdreAdr
      ,ORDRE.CP As OrdreCP
      ,ORDRE.VILLE As OrdreVille
      ,ENLEV_CREMB
      ,ENLEV_DECL
      ,MODAL_MODE
      ,[PAYS]
      ,[INSEE]
      ,[SIRET]
      ,ORDRE.TEL As OrdreTel
      ,ORDRE.FAX As OrdreFax
      ,[EMAIL]     
      ,[NBR_COLIS]
      ,[POID]   
      ,[OBS]
      ,[DATE_CREE]
      ,[DATE_MODIF]
      ,[REF_EXPED]
      ,[AUTRE_REF]
      ,[AGENCE]
      ,[TRANSPORTEUR]
      ,NOM
      ,CAPITAL
      ,LIBELLE
      ,T_LOGO.IMG As FaImg
      ,T_LOGO.ADRESSE As FaAdr
      ,T_LOGO.CP As FaCp
      ,T_LOGO.VILLE As FaVille
      ,T_LOGO.TEL As FaTel
      ,T_LOGO.FAX As FaFax
      ,FAWEB_CLIENT.RS_NOM As CliRsNom
      ,FAWEB_CLIENT.ADRESSE As CliAdr
      ,FAWEB_CLIENT.CP As CliCp
      ,FAWEB_CLIENT.VILLE As CliVille
  FROM [ORDRE]
  LEFT JOIN T_LOGO ON ORDRE.TRANSPORTEUR = T_LOGO.NOID
  LEFT JOIN FAWEB_CLIENT ON ORDRE.CODE_CLIENT = FAWEB_CLIENT.CODE_CLIENT
 WHERE (STATUT_ORDRE = 2) AND (TRANSPORTEUR IN (SELECT ParsedString From dbo.ParseStringList(@Trans)))

C#

List<Pers_Ordre> oListOrdre = new List<Pers_Ordre>();
                while (readerOne.Read())
                {
                   Pers_Ordre oPerOrdr = new Pers_Ordre();
                   Pers_Ordre test =  (from t in oListOrdre where t.DestId == readerOne["CODE_DEST"].ToString() select t).FirstOrDefault();


                    oPerOrdr.OrdreId = Convert.ToInt32(readerOne["NO_ORDRE"]);
                    oPerOrdr.DestId = readerOne["CODE_DEST"].ToString();

                    if (test == null)
                    {
                        oListOrdre.Add(oPerOrdr);
                    }
                    else
                    {
                        int NbrColis = (from t in oListOrdre where t.DestId == readerOne["CODE_DEST"].ToString() select t.NbrColis).FirstOrDefault();
                        if (NbrColis < 5)
                        {
                            test.NbrColis += NbrColis;
                        }
                    }
                }

它不从事预期的工作。

感谢你们的帮助!

Answer 1

^{(Not really an answer, but this doesn t fit in a comment.)}
Here s a LINQ-to-Objects query that groups items by meetingId and creates new items such that there is one item with bill less than 10 and as many items as needed with bill equalling 10 to keep the sum:

https://i.stack.imgur.com/tdYut.png” alt=“Screenshot”/>

你们是否回想?

var list = new List<Tuple<char, int>>
{
    Tuple.Create( a , 6),
    Tuple.Create( b , 7),
    Tuple.Create( c , 1),
    Tuple.Create( a , 5),
    Tuple.Create( a , 3),
    Tuple.Create( b , 4),
    Tuple.Create( g , 2),
};

var result = list
    .GroupBy(x => x.Item1)
    .Select(g => new
    {
        Key = g.Key,
        Sum = g.Sum(x => x.Item2)
    })
    .Select(p => new
    {
        Key = p.Key,
        Items = Enumerable.Repeat(10, p.Sum / 10)
                          .Concat(Enumerable.Repeat(p.Sum % 10, 1))
    })
    .SelectMany(p => p.Items.Select(i => Tuple.Create(p.Key, i)))
    .ToList();

Answer 2

卡列斯将归还希望取得的成果:

SELECT meetingId, SUM(bill) as bill_total
FROM table
GROUP BY meetingId
HAVING SUM(bill) < 10

Answer 3

您不应在客户方面这样做,因为它能够得到相当密集的、简单的<代码>。以<<<>条/代码>为标题的组别,应给您以预期结果:

抽样数据:

“entergraph

问:

SELECT 
    MeetingID, 
    SUM(bill) AS Total
FROM 
    Table_1 
GROUP BY 
    MeetingID 
HAVING 
    SUM(bill) < 10

查询结果:

“entergraph

Answer 4

table.GroupBy(p => p.meetingId).Where(p => p.Sum(q => q.bill) < 10)
.Select(p => new
{
    meetingId= p.Key,
    bill= p.Sum(q => q.bill)
});

友情链接