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

单表查询问题
表中有2011.04.27到2011.05.17的每天每个时段到各个店铺采购货物的表,表中有id,shopid(购货门店的id号),time(购货的时间),cash(到这个门店的开销),先在统计出,每天到各个们店的次数的linq语句

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

//假设你的表名为table,与之关联的店铺表名为shop
//如下语句经过测试通过
var tmp = from p in db.table 
         group p by p.shopid 
          into g 
        select new { g.Key, 
                     count = g.Count(),
                     name = g.Select(q=>q.shop.name).First() 
                    };

------解决方案--------------------
SQL code

create database TestDb

use TestDb

create table shop(
[id] int identity primary key,
[name] nvarchar(20)
)

create table buy(
[id] int identity primary key,
[time] nvarchar(20),
[cash] int,
[shopid] int foreign key references shop(id)
)

insert into shop values('门店1')
insert into shop values('门店2')
insert into shop values('门店3')
insert into shop values('门店4')
insert into shop values('门店5')

insert into buy values('2010-10-1 12:22:43',20,1)
insert into buy values('2010-10-1 12:22:43',20,1)
insert into buy values('2010-10-1 12:22:43',20,2)
insert into buy values('2010-10-1 12:22:43',20,3)
insert into buy values('2010-10-1 12:22:43',20,4)
insert into buy values('2010-10-2 12:22:43',20,1)
insert into buy values('2010-10-2 12:22:43',20,1)
insert into buy values('2010-10-2 12:22:43',20,3)
insert into buy values('2010-10-4 12:22:43',20,1)

------解决方案--------------------
C# code
我觉得6楼的LINQ语句还有优化的空间:
protected void Page_Load(object sender, EventArgs e)
{
 if(!IsPostBack)
{
 DataClassesDataContext db = new DataClassesDataContext();
    GridView1.DataSource = from p in db.buy
                           group p by new
                           {
                              p.shopid,
                              time = SqlMethods.DateDiffDay(Convert.ToDateTime(p.time), new DateTime(1900, 1, 1))
                           } into g
                           orderby g.Key.time  
                           select new { time=g.Key.time, name = g.FirstOrDefault(q => q.shop.name), count = g.Count() };
    GridView1.DataBind();
}
}