如何让哪个员工在sql server中运行所有项目 [英] how to get which employee is working all project in sql server

问题描述

我在sql server中有一个疑问 

如何根据两个表获取所有项目中的empid。

这里empid 1适用于所有项目所以我需要得到那个empid 
$
empid 2只用于两个项目所以我不需要那些记录

类似于其他记录



CREATE TABLE [dbo]。[Employee]（

[Empid] [int] NULL，

[Projectid] [int] NULL，

[WorkingDate] [date] NULL

） 

CREATE TABLE [dbo]。[ProjectDetails]（

[Projectid] [int] NULL，

[ProjectName ] [varchar]（50）NULL

） 

INSERT [dbo]。[Employee]（[Empid]，[Projectid]，[WorkingDate]）VALUES（ 1,1，CAST（N'2019-01-01'AS Date））

GO

INSERT [dbo]。[Employee]（[Empid]，[Projectid] ]，[WorkingDate]）VALUES（1,2，CAST（N'2019-01-02'AS Date））

GO

INSERT [dbo]。[员工]（[Empid]，[Projectid]，[WorkingDate]）VALUES（1,1，CAST（N'2019-01-03'AS Date））

GO

INSERT [dbo]。[Employee]（[Empid]，[Projectid]，[WorkingDate]）VALUES（1,3，CAST（N'2019-01-04'AS Date））

GO

INSERT [dbo]。[Employee]（[Empid]，[Projectid]，[WorkingDate]）VALUES（1,4，CAST（N'2019-01-08'AS Date））

GO

INSERT [dbo]。[Employee]（[Empid]，[Projectid]，[WorkingDate]）VALUES（2,1，CAST（N'2019-） 02-01'AS Date））

GO

INSERT [dbo]。[Employee]（[Empid]，[Projectid]，[WorkingDate]）VALUES（2， 1，CAST（N'2019-02-02'AS Date））

GO

INSERT [dbo]。[Employee]（[Empid]，[Projectid]， [WorkingDate]）VALUES（2,1，CAST（N'2019-02-03'AS Date））

GO

INSERT [dbo]。[Employee]（ [Empid]，[Projectid]，[WorkingDate]）VALUES（2,2，CAST（N'2019-02-04'AS Date））

G O $
INSERT [dbo]。[Employee]（[Empid]，[Projectid]，[WorkingDate]）VALUES（1,1，CAST（N'2019-02-03'AS Date））

GO

INSERT [dbo]。[Employee]（[Empid]，[Projectid]，[WorkingDate]）VALUES（3,1，CAST（N'2019-） 02-01'AS Date））

GO

INSERT [dbo]。[Employee]（[Empid]，[Projectid]，[WorkingDate]）VALUES（3， 1，CAST（N'2019-02-02'AS Date））

GO

INSERT [dbo]。[Employee]（[Empid]，[Projectid]， [WorkingDate]）VALUES（3,1，CAST（N'2019-02-03'AS Date））

GO

INSERT [dbo]。[ProjectDetails]（ [Projectid]，[ProjectName]）VALUES（1，N'cl'）

GO

INSERT [dbo]。[ProjectDetails]（[Projectid]，[ProjectName] ）VALUES（2，N'Road'）

GO

INSERT [dbo]。[ProjectDetails]（[Projectid]，[ProjectName]）VALUES（3，N' Car'）

GO

INSERT [dbo]。[ProjectDetails]（[Projectid]，[ProjectName]）VALUES（4，N'Tvs'）



根据以上数据，我想要像bel这样的输出ow：

Empid 

1



我试过如下：



select * from 员工e 加入ProjectDetails p

on e.Projectid = p.Projectid

其中  e.Projectid in（从ProjectDetails中选择p.Projectid）



以上查询未给出预期结果。



你能告诉我如何编写查询以在sql server中执行此任务 



$

I have one doubt in sql server 
how to get which empid is working in all projects based on two tables .
here empid 1 is worked on all projects so I need to get that empid 
empid 2 is wored on only two project so I donot need that records
similary to other records

CREATE TABLE [dbo].[Employee](
[Empid] [int] NULL,
[Projectid] [int] NULL,
[WorkingDate] [date] NULL
) 
CREATE TABLE [dbo].[ProjectDetails](
[Projectid] [int] NULL,
[ProjectName] [varchar](50) NULL
) 
INSERT [dbo].[Employee] ([Empid], [Projectid], [WorkingDate]) VALUES (1, 1, CAST(N'2019-01-01' AS Date))
GO
INSERT [dbo].[Employee] ([Empid], [Projectid], [WorkingDate]) VALUES (1, 2, CAST(N'2019-01-02' AS Date))
GO
INSERT [dbo].[Employee] ([Empid], [Projectid], [WorkingDate]) VALUES (1, 1, CAST(N'2019-01-03' AS Date))
GO
INSERT [dbo].[Employee] ([Empid], [Projectid], [WorkingDate]) VALUES (1, 3, CAST(N'2019-01-04' AS Date))
GO
INSERT [dbo].[Employee] ([Empid], [Projectid], [WorkingDate]) VALUES (1, 4, CAST(N'2019-01-08' AS Date))
GO
INSERT [dbo].[Employee] ([Empid], [Projectid], [WorkingDate]) VALUES (2, 1, CAST(N'2019-02-01' AS Date))
GO
INSERT [dbo].[Employee] ([Empid], [Projectid], [WorkingDate]) VALUES (2, 1, CAST(N'2019-02-02' AS Date))
GO
INSERT [dbo].[Employee] ([Empid], [Projectid], [WorkingDate]) VALUES (2, 1, CAST(N'2019-02-03' AS Date))
GO
INSERT [dbo].[Employee] ([Empid], [Projectid], [WorkingDate]) VALUES (2, 2, CAST(N'2019-02-04' AS Date))
GO
INSERT [dbo].[Employee] ([Empid], [Projectid], [WorkingDate]) VALUES (1, 1, CAST(N'2019-02-03' AS Date))
GO
INSERT [dbo].[Employee] ([Empid], [Projectid], [WorkingDate]) VALUES (3, 1, CAST(N'2019-02-01' AS Date))
GO
INSERT [dbo].[Employee] ([Empid], [Projectid], [WorkingDate]) VALUES (3, 1, CAST(N'2019-02-02' AS Date))
GO
INSERT [dbo].[Employee] ([Empid], [Projectid], [WorkingDate]) VALUES (3, 1, CAST(N'2019-02-03' AS Date))
GO
INSERT [dbo].[ProjectDetails] ([Projectid], [ProjectName]) VALUES (1, N'cl')
GO
INSERT [dbo].[ProjectDetails] ([Projectid], [ProjectName]) VALUES (2, N'Road')
GO
INSERT [dbo].[ProjectDetails] ([Projectid], [ProjectName]) VALUES (3, N'Car')
GO
INSERT [dbo].[ProjectDetails] ([Projectid], [ProjectName]) VALUES (4, N'Tvs')

Based on above data I want output like below :
Empid 
1

I tried like below :

select * from  Employee e  join ProjectDetails p
on e.Projectid=p.Projectid
where  e.Projectid in ( select p.Projectid from ProjectDetails )

above query is not given expected result.

can you please tell me how to write query to achive this task in sql server 

推荐答案

Select e.Empid
From dbo.Employee e
Group By e.Empid
Having Count(Distinct e.Projectid) = (Select Count(*) As ProjectCount From dbo.ProjectDetails)
Order By e.Empid; 

Tom

Tom

这篇关于如何让哪个员工在sql server中运行所有项目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！