日期:2014-05-20  浏览次数:20887 次

group by分组带where的问题
数据如下:
  var stationIDs = new List<int> { 1, 2, 3, 4 };

  var stations = new List<Station>
  {
  new Station{ StationID=1, Type=0},
  new Station{ StationID=2, Type=1},
  new Station{ StationID=3, Type=1},
  new Station{ StationID=4, Type=1},
  new Station{ StationID=2, Type=0},
  new Station{ StationID=1, Type=0}
  };

查询stations当中type=0,stationID包含stationIDs,按stationID分组,求出结果集:

StationID,Count
1 ,2
2 ,1

谁帮忙写个比较简洁又高效的语句,谢谢!


------解决方案--------------------
C# code

static void Main(string[] args)
        {
            var stationIDs = new List<int> { 1, 2, 3, 4 };

            var stations = new List<Station>{
                new Station{ StationID=1, Type=0},
                new Station{ StationID=2, Type=1},
                new Station{ StationID=3, Type=1},
                new Station{ StationID=4, Type=1},
                new Station{ StationID=2, Type=0},
                new Station{ StationID=1, Type=0}
            };

            var result = from c in stationIDs
                          join o in
                              from c in stations
                              where c.Type == 0
                              select c on c equals o.StationID into ords
                          select new
                          {
                              c,
                              count = ords.Count()
                          };
                         
            foreach (var item in result)
            {
                Console.WriteLine("{0}", item);
            }

            Console.Read();
        }