使用类别组查询数据 [英] Query data with group of categories

问题描述

大家好，



是否可以查询MySQL数据库中的数据并根据保存的类别对数据进行分组？假设我有类别Programmer，Accountant，Sales&营销，技术。每个类别的数据都是动态的，可以插入多少用户。所以，我想做一个查询，结论如下： -



数据库中的数据： -

ID |姓名|职位|加入日期

==================

112435 | Micheal Angelo |会计师| 01-02-2015

520643 | John Abraham |销售&营销| 15-06-2014

235648 | Jonathan James |程序员| 11-03-2013

113254 | Adam Sandler |会计| 03-02-2015

526987 | Johnny | Sales&营销| 10-07-2014

231564 | Adrian Lamo |程序员| 10-03-2013

523641 | Sarah Abraham |销售&营销| 03-07-2014

903625 | Sammuel Jackson |技术| 15-03-2015

236875 | Albert Gonzalez |程序员| 23-05-2014

232212 | Gary McKinnon |程序员| 12-01-2013

905863 | Jim Carrey |技术| 03-04-2015



需要查询按位置对所有数据进行分组，如下所示： -

会计
 112435 Micheal Angelo 01 -02-2015 
 113254 Adam Sandler 03-02-2015 
 
程序员
 232212 Gary McKinnon 12-01-2013 
 231564 Adrian Lamo 10-03-2013 
 235648 Jonathan James 11-03-2013 
 236875 Albert Gonzalez 23-05-2014 
 
 Sales&营销
 520643 John Abraham 15-06-2014 
 523641 Sarah Abraham 03-07-2014 
 526987 Johnny 10-07-2014 
 
技术
 903625 Sammuel Jackson 15-03-2015 
 905863 Jim Carrey 2015-04-04 

有人可以帮我吗？我需要一些指南才能做到这一点。

解决方案

使用mySql无法完成。您可以尝试使用LinQ to SQL。

用户是您的表的名称。



这样做

会计
 112435 Micheal Angelo 01-02-2015 
 113254 Adam Sandler 03-02-2015 

< pre lang =c＃> var grouped = users.GroupBy（g = > g.Position） 。选择（s = > new {Position = s.FirstOrDefault（）。Position，Users = s }）;

或计算

会计
 2 
程序员
 1 

  var  grouped = users.GroupBy（g = >  g.Position）。选择（s = >   new  {Position = s.FirstOrDefault（）。Position，UsersCount = s.Count（）}）。转储（）; 

我会这样做这样做。

  var 已分组= 
 users.GroupBy（用户） = >  user.Position）
。选择（selection = >   new  {Position = selection.Key，Users = selection}）
 .OrderBy（a = >  a。位置）; 
  foreach （ var   group   分组）
 {
 Console.WriteLine（ 职位：{0}员工人数{1}， group  .Position，组 .Users.Count（））; 
  foreach （ var 用户   group  .Users）
 {
 Console.WriteLine（  {0} {1} {2}，user.ID，user.Name，user.DateJoined.ToShortDateString（））; 
} 
 Console.WriteLine（）; 
} 
 

Hi all,

Is it possible to query data in MySQL database and group the data based from saved categories? Let's say I have categories Programmer, Accountant, Sales & Marketing, Technical. Data on each categories are dynamic can be how many user inserted. So, I want to do a query which will conclude as below:-

Data in database:-
ID|Name|Position|Date Joined
==================
112435|Micheal Angelo|Accountant|01-02-2015
520643|John Abraham|Sales & Marketing|15-06-2014
235648|Jonathan James|Programmer|11-03-2013
113254|Adam Sandler|Accountant|03-02-2015
526987|Johnny|Sales & Marketing|10-07-2014
231564|Adrian Lamo|Programmer|10-03-2013
523641|Sarah Abraham|Sales & Marketing|03-07-2014
903625|Sammuel Jackson|Technical|15-03-2015
236875|Albert Gonzalez|Programmer|23-05-2014
232212|Gary McKinnon|Programmer|12-01-2013
905863|Jim Carrey|Technical|03-04-2015

A query is needed to group all data by Position as below:-

Accountant
112435    Micheal Angelo    01-02-2015
113254    Adam Sandler      03-02-2015

Programmer
232212    Gary McKinnon     12-01-2013
231564    Adrian Lamo       10-03-2013
235648    Jonathan James    11-03-2013
236875    Albert Gonzalez   23-05-2014

Sales & Marketing
520643    John Abraham      15-06-2014
523641    Sarah Abraham     03-07-2014
526987    Johnny            10-07-2014

Technical
903625   Sammuel Jackson    15-03-2015
905863   Jim Carrey         03-04-2015

Can someone help me on this? I need some guide to do this.

解决方案

With mySql it cannot be done. You can try LinQ to SQL instead.
users is the name of your table.

to do this

Accountant
112435 Micheal Angelo 01-02-2015
113254 Adam Sandler 03-02-2015

var grouped = users.GroupBy(g => g.Position).Select(s => new { Position =   s.FirstOrDefault().Position, Users = s  });

or to get a count

Accountant
2
Programmer
1

var grouped = users.GroupBy(g => g.Position).Select(s => new { Position = s.FirstOrDefault().Position, UsersCount = s.Count()  }).Dump();

I'd do something like this.

var grouped =
             users.GroupBy(user => user.Position)
                 .Select(selection => new { Position = selection.Key, Users = selection })
                 .OrderBy(a => a.Position);
         foreach (var group in grouped)
         {
             Console.WriteLine("Position: {0} Number of staff {1}", group.Position, group.Users.Count());
             foreach (var user in group.Users)
             {
                 Console.WriteLine("{0} {1} {2}", user.ID, user.Name, user.DateJoined.ToShortDateString());
             }
             Console.WriteLine();
         }

这篇关于使用类别组查询数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！