如何选择加入两个日期之间的天数总和 [英] How to select Sum of Days between two dates with join
问题描述
我有两个表,一个是UserTable,另一个是Usercompanies Table
UserTable字段
1. UserId
2.用户名
3.毕业
用户公司
1. Id
2. UserId
3.公司名称
4.加入日期
5.离开日期
5目前正在工作
i希望选择那些在加入和离开日期之前有超过2年经验的用户。用户公司可能超过一个,用户目前可以在最多3家公司工作。
UserTable的UserId是用户公司表中的本地密钥
所以请建议我查询
i have two tables one is UserTable and another is Usercompanies Table
UserTable field
1. UserId
2. UserName
3. Graduation
UserCompanies
1. Id
2. UserId
3. Company Name
4. Joining Date
5. Leaving Date
5. Currently Working
i want to select those users which have to more then 2 years experience on basis of joining and leaving date. user companies may be more then one and user can currently working in maximum 3 companies.
UserId of UserTable is foreigen key in UserCompanies Table
so please suggest me query for that
推荐答案
要想出一个解决方案,你需要了解:
1 。 SQL联接的可视化表示 [ ^ ]
2. http://www.w3schools.com/sql/func_datediff.asp [ ^ ]
首先尝试自己尝试。如果您在尝试时遇到困难,请发布一个带代码的新问题以征求意见。
To come up with a solution for your question, you will need to learn about:
1. Visual Representation of SQL Joins[^]
2. http://www.w3schools.com/sql/func_datediff.asp[^]
Try to attempt it yourself first. If you are stuck while trying, then post a new question with code to ask for advice.
var query1 = UserCompanies.Where(uc => (uc.LeavingDate - uc.JoiningDate).TotalDays > 730);
var query2 =
UserTable.Join(
query1,
u => new { u.UserId },
c => new { UserId = c.Id },
(u, c) => new { u.UserName, c }).ToList();
这篇关于如何选择加入两个日期之间的天数总和的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!