时间戳

首页 > 解决方案 > 如何根据sql server中的多个条件获取位置

sql-server-2008 - 如何根据sql server中的多个条件获取位置

问题描述

如何根据 sql server 中的两个表获得所需的输出：

CREATE TABLE [dbo].[Goal](
    [Location] [varchar](50) NULL,
    [Goal] [int] NULL,
    [Month] [date] NULL
)
CREATE TABLE [dbo].[Master](
    [Date] [date] NULL,
    [Employee] [varchar](50) NULL,
    [GP] [int] NULL,
    [Location] [varchar](50) NULL
)
INSERT [dbo].[Goal] ([Location], [Goal], [Month]) VALUES (N'MG Road', 50000, CAST(0xB93D0B00 AS Date))
GO
INSERT [dbo].[Goal] ([Location], [Goal], [Month]) VALUES (N'Madiwala', 60000, CAST(0xB93D0B00 AS Date))
GO
INSERT [dbo].[Goal] ([Location], [Goal], [Month]) VALUES (N'Silk Board', 30000, CAST(0xB93D0B00 AS Date))
GO
INSERT [dbo].[Goal] ([Location], [Goal], [Month]) VALUES (N'BTM', 35000, CAST(0xB93D0B00 AS Date))
GO
INSERT [dbo].[Goal] ([Location], [Goal], [Month]) VALUES (N'MG Road', 55000, CAST(0xBA3D0B00 AS Date))
GO
INSERT [dbo].[Goal] ([Location], [Goal], [Month]) VALUES (N'Madiwala', 65000, CAST(0xBA3D0B00 AS Date))
GO
INSERT [dbo].[Goal] ([Location], [Goal], [Month]) VALUES (N'Silk Board', 35000, CAST(0xBA3D0B00 AS Date))
GO
INSERT [dbo].[Goal] ([Location], [Goal], [Month]) VALUES (N'BTM', 35000, CAST(0xBA3D0B00 AS Date))
GO
INSERT [dbo].[Master] ([Date], [Employee], [GP], [Location]) VALUES (CAST(0x283E0B00 AS Date), N'Ram', 2000, N'MG Road')
GO
INSERT [dbo].[Master] ([Date], [Employee], [GP], [Location]) VALUES (CAST(0x283E0B00 AS Date), N'Ram', 1800, N'Silk Board')
GO
INSERT [dbo].[Master] ([Date], [Employee], [GP], [Location]) VALUES (CAST(0x293E0B00 AS Date), N'Sami', 15000, N'BTM')
GO
INSERT [dbo].[Master] ([Date], [Employee], [GP], [Location]) VALUES (CAST(0x2A3E0B00 AS Date), N'Ram', 2500, N'Silk Board')
GO
INSERT [dbo].[Master] ([Date], [Employee], [GP], [Location]) VALUES (CAST(0x2A3E0B00 AS Date), N'Ram', 2500, N'MG Road')
GO
INSERT [dbo].[Master] ([Date], [Employee], [GP], [Location]) VALUES (CAST(0x293E0B00 AS Date), N'Sami', 2000, N'BTM')
GO
INSERT [dbo].[Master] ([Date], [Employee], [GP], [Location]) VALUES (CAST(0x2A3E0B00 AS Date), N'Sami', 19000, N'Madiwala')
GO
INSERT [dbo].[Master] ([Date], [Employee], [GP], [Location]) VALUES (CAST(0x2E3E0B00 AS Date), N'Ram', 30000, N'MG Road')
GO
INSERT [dbo].[Master] ([Date], [Employee], [GP], [Location]) VALUES (CAST(0x2E3E0B00 AS Date), N'Ram', 30000, N'Madiwala')
GO
INSERT [dbo].[Master] ([Date], [Employee], [GP], [Location]) VALUES (CAST(0x2F3E0B00 AS Date), N'Ram', 25000, N'Madiwala')
GO
INSERT [dbo].[Master] ([Date], [Employee], [GP], [Location]) VALUES (CAST(0x2E3E0B00 AS Date), N'Sami', 20000, N'BTM')
GO
INSERT [dbo].[Master] ([Date], [Employee], [GP], [Location]) VALUES (CAST(0x2E3E0B00 AS Date), N'Sami', 15000, N'Silk Board')
GO
INSERT [dbo].[Master] ([Date], [Employee], [GP], [Location]) VALUES (CAST(0x2F3E0B00 AS Date), N'Sami', 15000, N'Silk Board')

基于上述数据，我希望输出如下：

Employe |Location   |TotalGP|Month Goal |Goal %
Ram |MG Road    |3800   |50000          |7.6
Ram |MG Road    |5000   |50000          |10
Ram |Madiwala   |60000  |65000          |92.30769231
Sami    |Madiwala   |17000  |60000          |28.33333333
Sami    |Madiwala   |19000  |60000          |31.66666667
Sami    |Silk board |35000  |35000          |100
sami    |Silk board |15000  |35000          |42.85714286
Ram |Madiwala   |25000  |65000          |38.46153846

我试过这个：

select employee,date,sum(gp)totalgp 
from master 
group by  employee,date 



select * from 
(select date,employee,location,gp,ROW_NUMBER() over (partition by employee,date order by gp desc ) as rn 
from master 
) a
where rn=1

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

目标百分比：totalgp/monthgoal * 100

月目标：月目标应从月目标表中选择，并应显示员工已产生最大 GP（GP 总和）的位置的目标，例如：Ram 在 MG Road 和 madiwala 都工作过，但是他的 GP 总和在 MG Road 中更多，因此 MG road'Goal 在 4 月对 Ram 显示，但对于 5 月 Ram's Goal

GP 总定义：

例如 25-04-18 的 Ram 仅在 MG 路 获得 2000 , 但 在 同一 天 在 其他 位置 他 获得 1800 额外 , 所以 我们 显示 Ram 的 3800 与 位置 无关

位置列定义

地点 - 员工可能在一两个地点工作，但在这里您应该显示他获得最高 Gp 的地点（例如：Ram 在 MG Road 和 madiwala 都工作过，但他的 GP 总和）在 MG 路更多，所以 MG 路在 4 月显示，但在 5 月，Ram 位置是 madiwala

标签： sql-server-2008sql-server-2012

解决方案

使用此查询

select date,employee,sum(gp)as totalgp,max(goal) as monthgoal,cast(sum(gp)* 100.0/max(goal) as float) as 'Goal%'
from goal 
inner join master on master.location=goal.location 
where month(goal.month)=month(master.date)
group by date,employee

输出是这样的

date       employee                                           totalgp     monthgoal   Goal%
---------- -------------------------------------------------- ----------- ----------- ----------------------
2018-04-25 Ram                                                3800        50000       7.6
2018-04-26 Sami                                               17000       35000       48.571428571428
2018-04-27 Ram                                                5000        50000       10
2018-04-27 Sami                                               19000       60000       31.666666666666
2018-05-01 Ram                                                60000       65000       92.307692307692
2018-05-01 Sami                                               35000       35000       100
2018-05-02 Ram                                                25000       65000       38.461538461538
2018-05-02 Sami                                               15000       35000       42.857142857142

注意：您必须使两个表格的日期格式相同

推荐阅读