使用join计算值

dm7nw8vv 于 2021-08-13 发布在 Java

关注(0)|答案(1)|浏览(289)

我有两张table，一张table工作，一张table雇主。
我在计算每天每一份工作的就业人数。例如：

- job 1 has 5 new employee in day 2020-04-28 14:51:28.463
- job 2 has 3 employee in day 2020-04-28 18:11:23.111
- job 3 has 1 employee in day 2020-04-27 11:31:13.845

好吧，所以我应该有下一个信息：

Day 28: 8 employee
Day 27: 1 employee

我正在尝试这样做，但不起作用，因为它给了我双倍的回报，因为对于每个新员工，都会创建一个新行：

Select COUNT(convert(date, job.dateCreat ,103)) as jobsPerDay from jobTable job join jobEmpl jobEmp 
on job.Id = jobEmp.Id
Group by convert(date, job.dateCreat ,103) 
order by convert(date, job.dateCreat, 103) desc;

我没有使用子查询而不是联接，因为我需要更多的列来支持它。如果我使用它，则可以工作，但无法从第二个表中获取另一个列值：

Select COUNT(convert(date, job.dateCreat ,103)) as jobsPerDay from jobTable job join (Select distinct jobEmpl_id from jobEmp) as jobEmp
on job.Id = jobEmp.Id
Group by convert(date, job.dateCreat ,103) 
order by convert(date, job.dateCreat, 103) desc;

样本数据：

sql DateTime sql-server tsql

来源：https://stackoverflow.com/questions/62044198/count-values-using-join

1条答案

按热度按时间

mutmk8jj1#

希望这对你有帮助

create table jobTable(id int, JobName varchar(200), dateCreat DateTime)

create table jobEmpl(jobEmpl_id int, id int, dateCreat DateTime)

insert into jobTable values(1, 'Police', '2020-04-28 14:51:28.463')
insert into jobTable values(2, 'Medic', '2020-04-28 14:51:28.463')
insert into jobTable values(3, 'FootBaller', '2020-04-28 14:51:28.463')

insert into jobEmpl values(5, 1, '2020-04-28 14:51:28.463')
insert into jobEmpl values(6, 1, '2020-04-28 14:52:28.463')
insert into jobEmpl values(7, 1, '2020-04-28 14:53:28.463')
insert into jobEmpl values(8, 1, '2020-04-28 14:54:28.463')
insert into jobEmpl values(9, 1, '2020-04-28 14:55:28.463')
insert into jobEmpl values(16, 1, '2020-04-29 14:55:28.463')
insert into jobEmpl values(17, 1, '2020-05-29 14:55:28.463')
insert into jobEmpl values(10, 2, '2020-04-28 16:51:28.463')
insert into jobEmpl values(11, 2, '2020-04-28 16:53:28.463')
insert into jobEmpl values(12, 2, '2020-04-28 16:55:28.463')
insert into jobEmpl values(15, 3, '2020-04-27 18:12:18.263')

select j.id as JobId, count(je.jobEmpl_id) as NoOfEmployees, convert(date, je.dateCreat ,103) dateOfJoining
from jobEmpl je 
inner join jobTable j on j.id = je.id
group by j.id, convert(date, je.dateCreat ,103) 
order by j.id

select day(je.dateCreat) dayOfJoining,month(je.dateCreat) monthOfJoining,year(je.dateCreat) yearsOfJoining, count(je.jobEmpl_id) as NoOfEmployees
from jobEmpl je 
inner join jobTable j on j.id = je.id
group by day(je.dateCreat),month(je.dateCreat),year(je.dateCreat)

赞(0）回复(0）举报 2021-08-13

我来回答

使用join计算值

1条答案

相关问题

热门标签

最新问答