Attendance Management System 的疑问 （紧急）

TheRocker

我能够将一个list的学生和status（absent/present）加进database
database是visual studio 的 local sql database server (dbo.StudentAttendance)
database 里面 一个studentID 可以 有很多其他的 entry

example：
studentID        classID          Status
WAR0001        CLS0001       Present
WAR0001        CLS0020       Absent
WAR0001        CLS0009       Present


我的疑问是：
要怎样算student WAR0001 total  Present 几次， Absent 几次？

rcyaw2

group by classID where studentID  = WAR0001 and Status = Present

xtjj

簡短的作法:

1. select status,count(status) from
   
2. (select * from students where studentid='war0001') s
   
3. group by status

复制代码

xtjj

sample 在這

http://sqlfiddle.com/#!2/29f99/4

TheRocker

xtjj 发表于 1-8-2014 04:45 PM
sample 在這

http://sqlfiddle.com/#!2/29f99/4

你好，谢谢你的回答

请问你，
如果我要全部的student 的 absent count 能不能做到？

例如：
insert into students values('war0001', 'cls0001', 'present');
insert into students values('war0001', 'cls0009', 'absent');
insert into students values('war0002', 'cls0009', 'absent');
insert into students values('war0002', 'cls0011', 'absent');
insert into students values('war0002', 'cls0014', 'present');


我需要
WAR0001 absent count = 1
WAR0002 absent count = 2

如果用LINQ 能做到吗？

xtjj

1.         List<Student> students = new List<Student>();
   
2.        
   
3.         var sub = from p in students
   
4.         where p.Status == "absent"
   
5.         select new { p.StudentID, p.Status };
   
6.        
   
7.         var absence = from p in sub
   
8.         group p by p.StudentID into k
   
9.         select new { ID = k.Key, Count = k.Count() };
   
10.        
    
11.         foreach (var p in absence)
    
12.         {
    
13.                 Console.WriteLine("{0} absent count = {1}", p.ID, p.Count);
    
14.         }

复制代码

xtjj

同學?

http://cforum.cari.com.my/forum. ... id=3465140#lastpost

TheRocker

xtjj 发表于 1-8-2014 05:04 PM

这位大大，
请问 VB LINQ 做得到吗？

我需要LINQ statement 来 set DataGridView 的
datasource

谢谢。。 感激不尽

TheRocker

xtjj 发表于 1-8-2014 05:10 PM
同學?

http://cforum.cari.com.my/forum.php?mod=viewthread&tid=3465140#lastpost

是的，是groupmate。 haha

xtjj

1. Dim sss = From p In students Where p.Status = "absent" New From { _
   
2.         p.StudentID, _
   
3.         p.Status _
   
4. }
   
5. 
   
6. Dim absence = From k In From p In [sub]Group p By p.StudentID New With { _
   
7.         Key .ID = k.Key, _
   
8.         Key .Count = k.Count() _
   
9. }

复制代码

dgv1.Datasource = absence

b8b8b8b8

现在是 New 显示着 syntax error

xtjj

try

1. Dim sss = From p In students Where p.Status = "absent" Select New With {p.StudentID, p.Status}       
   
2. Dim absence = From p in sss Group By StudentID = p.StudentID Into g = Group Select StudentID, g.Count()

复制代码

b8b8b8b8

这次没有syntax error 但是 datagridview还是显示 duplicate data

xtjj

你把所有 code 貼上來吧

b8b8b8b8

xtjj

1. Dim students as new List(of Student)() From {
   
2.         New Student with {.StudentID = "war0001", .ClassID="cls0009", .Status="present"}, _
   
3.         New Student with {.StudentID = "war0001", .ClassID="cls0001", .Status="absent"}, _
   
4.         New Student with {.StudentID = "war0002", .ClassID="cls0009", .Status="absent"}, _
   
5.         New Student with {.StudentID = "war0002", .ClassID="cls0011", .Status="absent"}, _
   
6.         New Student with {.StudentID = "war0002", .ClassID="cls0014", .Status="present"} _
   
7.         }
   
8. 
   
9.         Dim sss = From p In students Where p.Status = "absent" Select New With {p.StudentID, p.Status}       
   
10.         Dim absence = From p in sss Group By StudentID = p.StudentID Into g = Group Select StudentID, g.Count()
    

复制代码

xtjj

我們老師按照你的資料, 和一樣的 code, 答案如上.

b8b8b8b8

我的结果是这样

xtjj

你把 solution 和 database 上傳, 然後我叫老師下載了幫你看下.

b8b8b8b8

https://www.dropbox.com/sh/1chbv ... -Lm4DywFXlygwB1HNga