本文介绍了如何让哪个员工在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中运行所有项目的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

09-14 00:35