如何在这么多表上做SQL？里面的细节

I have a school project that uses MS Visual Studio 2010 C#. The project is a project management software. Basically managed lots of project where allowance is given to employee depending on the shift you work on. I have total 4 table to my project and they are;

我有一个使用MS Visual Studio 2010 C#的学校项目。该项目是一个项目管理软件。基本上管理很多项目,根据您的工作班次给予员工津贴。我的项目共有4个表,他们是;

Employees: where you manage all the employees details

员工:您管理所有员工详细信息的地方

AssignProject: where it say which employee assigned to which project

AssignProject:说明哪个员工分配到哪个项目

Shift: where it say which shift the employee work on

转变:说明员工在哪个班次工作

Projects: where you manage all the project details

项目:您管理所有项目详细信息的位置

I want to calculate total allowance for employee. Eg. EmployeeA work 3 days on Project A on 3 different Shift. How to i do SQL on it?

我想计算员工的总津贴。例如。员工A在3个不同的班次上工作3天。我该怎么做SQL呢?

Updates: I tried the follow, it managed to get EmployeeA working days but it display the allowance catered for the project. Now, how do I filter from this code that only the shift that the employee work displayed.

更新:我尝试了以下操作,它设法获得EmployeeA工作日,但它显示了项目的配额。现在,如何从此代码中过滤出仅员工工作所显示的班次。

con = new SqlConnection(ConfigurationManager
                          .ConnectionStrings["projectshift"].ConnectionString);
da = new SqlDataAdapter(@"
        SELECT 
            Shift.date, 
            Projects.AGeneral, 
            Projects.AMorning, 
            Projects.AEvening, 
            Projects.ANight 
        FROM Projects  
        INNER JOIN Shift ON Projects.ProjectId = Shift.ProjectId 
        WHERE Shift.EmployeeId='" + Session["CurrentUser"] + "'", con);
ds = new DataSet();
da.Fill(ds, "AssignProject");
GridView1.DataSource = ds;
GridView1.DataBind();

1 个解决方案

#1

Let me first suggest that you use parameterized queries -- this is vulnerable to SQL Injection.

我首先建议您使用参数化查询 - 这很容易受到SQL注入攻击。

With that said, try something like this assuming you're using SQL Server (untested) and assuming you want the distinct days from shift.date as days worked and all the records from shift.date for shifts worked:

说到这一点,尝试这样的假设你使用的是SQL Server(未经测试)并假设你想要从shift.date开始的不同日子,因为工作天数和来自shift.date的所有记录都有效:

SELECT Count(DISTINCT CONVERT(varchar(10), Shift.Date, 111)) as DaysWorked, 
   COUNT(Shift.date) as ShiftsWorked,
   Projects.AGeneral, Projects.AMorning, Projects.AEvening, Projects.ANight 
FROM Projects 
   INNER JOIN Shift ON Projects.ProjectId = Shift.ProjectId 
WHERE ...
GROUP BY Projects.AGeneral, Projects.AMorning, Projects.AEvening, Projects.ANight

Good luck.

#1