SQL查询帮助，保留系统查询 [英] SQL Query Help, Leave System Query

问题描述

下午好.

我有两个表需要用于查询.

查询必须获取每个活动用户.
然后从离开"表中获得所有请假，将其拆分为
离开类型的休假类型，并对
的类型进行内部求和 离开，以便最终结果显示:

每个用户以及他们当年剩余的休假类型数.

相关表/列
表1-user_detail

user_name(字符串)< ----主键
dept_code(int)
活动(真/假)


TAble2-休假(所有用户的休假条目的合并)

Leave_id(字符串)< ----外键
no_of_working_days(int)
假类型(字符串)< -----(``病态''，``休假''，``研究''等)


典型结果集如下所示:

用户-部门-空缺TOTAL ----病态TOTAL ---- StudyTOTAL
鲍勃------ 9 ----------------- 12 ---------------- 7 ------- --------- 3

我是一个使用SQL的菜鸟，我的所有努力(其中两个)都失败了.
如果有人能让我走上正确的路，那将是非常棒的.

像这样的东西，只显示一个人，带他/她的所有假期，但也吐出不同种类的假期，并使用no_of_working_days来获取总计.


谢谢.

Good Afternoon.

I have Two Tables that I need to use for my Query.

The Query Must get Each active user.
And From the ''Leave'' table get all their leave, split it into the
types of leave from the leave_type column and do an internal sum with the types of
leave so that the end result shows:

Each User and how many leave types they have left for the year.

Relevant Tables/Columns
Table1 - user_detail

user_name (string) <---- Primary key
dept_code (int)
active (true/false)


TAble2 - leave (Cosists of leave entries by all users)

leave_id (string) <---- Foreign key
no_of_working_days (int)
leave_type (string) <----- (''Sick'', Vacational'', ''Study'' etc)


Typical result set would look like:

User---Department---VacationalTOTAL---SickTOTAL---StudyTOTAL
Bob ------9 ----------------- 12 ---------------- 7 ---------------- 3

I am fairly a Noob with hardcore SQL, all my efforts (two of them) have failed.
If somebody could just get me on the right track that would be awesome.

Stuff like, only showing the one person, with all his/her leave but leave also spit into different kinds of leave, and using the no_of_working_days to get the totals.


Thank You.

推荐答案

检查此内容，

餐桌假
*************
Leave_id no_of_working_days假期类型

鲍勃5生病

Bob 2 Vacational

Bob 1 Study

表user_detail
*****************

user_name部门代码有效
鲍勃9 True

查询
*****

Check this,

table leave
*************
leave_id no_of_working_days leave_type

Bob 5 Sick

Bob 2 Vacational

Bob 1 Study

table user_detail
*****************

user_name dept_code active
Bob 9 True

Query
*****

Select leave_id as Name,(select dept_code from user_detail where USER_NAME=''Bob'') as Department,(select SUM(no_of_working_days) from leave where leave_type=''Vacational'' and leave_id=''Bob'')  as  VacationalTOTAL,(select SUM(no_of_working_days) from leave where leave_type=''Sick'' and leave_id=''Bob'')  as  SickTOTAL,(select SUM(no_of_working_days) from leave where leave_type=''Study'' and leave_id=''Bob'')  as  StudyTOTAL  from leave where leave_id=''Bob'' group by leave_id

您的问题中有一个问题.您将如何加入user_detail
离开桌子?由于您已经说过上述答案可以得出正确的结果，所以我假设请假代码id =用户名.我正在发布一个新的答案，因为上述方法不是最好的方法，如果您必须为多个用户提取结果，则将不起作用.

下面的示例使用PIVOTS并执行得更快.

There is one issue in your question. How will you join user_detail
and leave tables? Since you have said that the above answer pulls correct results I assume leave_id ~= user_name. I am posting a new answer since the above approach is not the best way and will not work if you have to pull results for multiple users.

The below example uses PIVOTS and executes much faster.

--Temp tables
DECLARE @user_detail TABLE (user_name VARCHAR(50), dept_code INT, active BIT)
DECLARE @leave TABLE (user_name VARCHAR(50), no_of_working_days INT, leave_type VARCHAR(50)) -- note that leave_id is changed to user_name.

--Let's have three users
INSERT INTO @user_detail VALUES ('Bob', 9 , 1)
INSERT INTO @user_detail VALUES ('Tracy', 9 , 1)
INSERT INTO @user_detail VALUES ('Maria', 9 , 1)

--Their leaves
INSERT INTO @leave VALUES ('Bob', 5, 'Sick')
INSERT INTO @leave VALUES ('Bob', 2, 'Vacational')
INSERT INTO @leave VALUES ('Bob', 1, 'Study')
INSERT INTO @leave VALUES ('Tracy', 2, 'Study')
INSERT INTO @leave VALUES ('Maria', 1, 'Sick')
INSERT INTO @leave VALUES ('Maria', 6, 'Vacational')
INSERT INTO @leave VALUES ('Maria', 9, 'Study')
INSERT INTO @leave VALUES ('Tracy', 8, 'Vacational')


--Query to pull results using PIVOT
SELECT user_name, dept_code,[Sick], [Vacational], [Study]
FROM
(SELECT l.user_name, dept_code, no_of_working_days, leave_type
FROM @leave l
JOIN @user_detail ud ON l.user_name = ud.user_name) AS Source
PIVOT (MAX(no_of_working_days) FOR leave_type IN ([Sick], [Vacational], [Study])) as p

结果

Result

user_name dept_code Sick  Vacational  Study
--------- --------- ----- ----------- -----
Bob        9         5    2           1
Maria      9         1    6           9
Tracy      9         NULL 8           2

这篇关于SQL查询帮助，保留系统查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！