问题描述
我在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中运行所有项目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！