求一linq语句

[xuxiaomei2707]

有两个表

总运单表A
ANo总运单编号 CreateTime创建时间
zong001 2014-01-01
zong002 2014-01-01
zong003 2014-01-01

分运单表B
ID主键 ANo总运单 BagNo袋号 BNo分运单号
1 zong001 N1234 fen001
2 zong001 N1234 fen002
3 zong001 N0435 fen003
4 zong002 N0567 fen004

想要得到的数据
总运单号 所含袋数 所含分运单数
zong001 2 3
zong002 1 1
zong003 0 0

总运单中 有多个袋子，每个袋子中有多个分运单

[xuxiaomei2707]

怎么没有人啊，sql也行，lamda表达式也可以，多谢了。

[wangmoxhn]

 class A
        {
            public string No;
            public string Ct;
        }

        class B
        {
            public int Id;
            public string aNo;
            public string bNo;
            public string bfNo;
        }
        static void Main(string[] args)
        {

            A[] a = new A[] { new A{No = "zong001",Ct="2014-01-01"},
            new A{No = "zong002",Ct="2014-01-01"},new A{No = "zong003",Ct="2014-01-01"}};

            B[] b = new B[] { new B{Id =1, aNo = "zong001",bNo = "N1234",bfNo="fen001"},
            new B{Id =1, aNo = "zong001",bNo = "N1234",bfNo="fen002"},new B{Id =1, aNo = "zong001",bNo = "N0435",bfNo="fen003"},
            new B{Id =1, aNo = "zong002",bNo = "N0567",bfNo="fen004"}};

            var c = from _a in a join _b in b on _a.No equals _b.aNo into ts from t in ts.DefaultIfEmpty() select new {ano = _a.No,bno=t!=null?t.bNo:"",fno=t!=null?t.bfNo:""};
            var d = from t in c group t by t.ano into g select new { ano = g.Key, bnum = (from x in g where x.bno != "" select new { bno = x.bno }).Distinct().Count(), fnum = (from x in g where x.fno != "" select new { fno = x.fno }).Distinct().Count(), };
            foreach (var t in d)
            {
                Console.WriteLine("{0},{1},{2}",t.ano,t.bnum,t.fnum);  
            }
            Console.ReadKey();    
        } 

[q107770540]

要会提问哦，参考此帖的提问方式，给出些测试代码： http://bbs.csdn.net/topics/380250812

[exception92]

谁没事闲的 去敲 创建sql表数据的语句或者创建datatable数据的C#代码。

[娃都会打酱油了]

DECLARE @TableA TABLE (Ano VARCHAR(20))
INSERT INTO @TableA
        ( Ano )
VALUES  ( 'zong001'  -- Ano - varchar(20)
          )
          INSERT INTO @TableA
        ( Ano )
VALUES  ( 'zong002'  -- Ano - varchar(20)
          )
          INSERT INTO @TableA
        ( Ano )
VALUES  ( 'zong003'  -- Ano - varchar(20)
          )
          
DECLARE @TableB TABLE (ID int,Ano VARCHAR(20),BagNo VARCHAR(20),BNo VARCHAR(20))
INSERT INTO @TableB
        ( ID, Ano, BagNo, BNo )
VALUES  ( 1, -- ID - int
          'zong001', -- Ano - varchar(20)
          'N1234', -- BagNo - varchar(20)
          'fen001'  -- BNo - varchar(20)
          )
          INSERT INTO @TableB
        ( ID, Ano, BagNo, BNo )
VALUES  ( 2, -- ID - int
          'zong001', -- Ano - varchar(20)
          'N1234', -- BagNo - varchar(20)
          'fen002'  -- BNo - varchar(20)
          )
          INSERT INTO @TableB
        ( ID, Ano, BagNo, BNo )
VALUES  ( 3, -- ID - int
          'zong001', -- Ano - varchar(20)
          'N1435', -- BagNo - varchar(20)
          'fen003'  -- BNo - varchar(20)
          )
          INSERT INTO @TableB
        ( ID, Ano, BagNo, BNo )
VALUES  ( 4, -- ID - int
          'zong002', -- Ano - varchar(20)
          'N1567', -- BagNo - varchar(20)
          'fen004'  -- BNo - varchar(20)
          )
         
SELECT [@TableA].Ano,ISNULL(BagNos,0) AS BagNos,ISNULL(Bnos,0) AS Bnos FROM @TableA
LEFT JOIN 
(SELECT Ano,COUNT(0) AS BagNos FROM (SELECT DISTINCT Ano,BagNo FROM @TableB) x1 GROUP BY ano) t1 ON [@TableA].Ano = t1.Ano
LEFT JOIN
(SELECT ano,COUNT(0) AS Bnos FROM (SELECT DISTINCT Ano,BNo FROM @TableB) x2 GROUP BY ano) t2 ON [@TableA].Ano = t2.Ano

[娃都会打酱油了]

DECLARE @TableA TABLE (Ano VARCHAR(20))
INSERT INTO @TableA
        ( Ano )
VALUES  ( 'zong001'  -- Ano - varchar(20)
          )
          INSERT INTO @TableA
        ( Ano )
VALUES  ( 'zong002'  -- Ano - varchar(20)
          )
          INSERT INTO @TableA
        ( Ano )
VALUES  ( 'zong003'  -- Ano - varchar(20)
          )
          
DECLARE @TableB TABLE (ID int,Ano VARCHAR(20),BagNo VARCHAR(20),BNo VARCHAR(20))
INSERT INTO @TableB
        ( ID, Ano, BagNo, BNo )
VALUES  ( 1, -- ID - int
          'zong001', -- Ano - varchar(20)
          'N1234', -- BagNo - varchar(20)
          'fen001'  -- BNo - varchar(20)
          )
          INSERT INTO @TableB
        ( ID, Ano, BagNo, BNo )
VALUES  ( 2, -- ID - int
          'zong001', -- Ano - varchar(20)
          'N1234', -- BagNo - varchar(20)
          'fen002'  -- BNo - varchar(20)
          )
          INSERT INTO @TableB
        ( ID, Ano, BagNo, BNo )
VALUES  ( 3, -- ID - int
          'zong001', -- Ano - varchar(20)
          'N1435', -- BagNo - varchar(20)
          'fen003'  -- BNo - varchar(20)
          )
          INSERT INTO @TableB
        ( ID, Ano, BagNo, BNo )
VALUES  ( 4, -- ID - int
          'zong002', -- Ano - varchar(20)
          'N1567', -- BagNo - varchar(20)
          'fen004'  -- BNo - varchar(20)
          )
         
SELECT [@TableA].Ano,ISNULL(BagNos,0) AS BagNos,ISNULL(Bnos,0) AS Bnos FROM @TableA
LEFT JOIN 
(SELECT Ano,COUNT(0) AS BagNos FROM (SELECT DISTINCT Ano,BagNo FROM @TableB) x1 GROUP BY ano) t1 ON [@TableA].Ano = t1.Ano
LEFT JOIN
(SELECT ano,COUNT(0) AS Bnos FROM (SELECT DISTINCT Ano,BNo FROM @TableB) x2 GROUP BY ano) t2 ON [@TableA].Ano = t2.Ano

[xuxiaomei2707]

自己解决了，谢谢大家