mysql - 复杂的SQL : How to filtrate duplicate data about technical support and compute rank of order

Question

我有一个大问题。谁能帮帮我？

我的第一个目标是:用query statment(select not delete)筛选重复的数据，即由于重复反馈导致的同一个ServiceID的EndTime和Qos的各种条目。规则是只保留同一 ServiceID 具有最低 Qos 的记录。如果有几条记录具有最低的 Qos，则只保留这些记录中的任何一条。客户接受并喜欢这个方案。在这个例子中，对于 ID=6,7, 和 8，只保留 6 或 7。我在这里得到了这个目标的答案:

SELECT DISTINCT serviceid,tcid,endtime,qos 
FROM (SELECT * 
      FROM service 
      ORDER BY serviceid, qos, id) AS base 
GROUP BY serviceid

aim2:Qos=1 表示服务满意。每个技术支持工程师每月获得的总满意服务称为“本月个人总满意服务”。将“本月个人服务总满意度”排名当月前2名的技术支持工程师称为“本月以来前2名”。如果表现出色，每个月都能进入“本月以来前2”，则可以称为“一级优秀”。总而言之，这一步就是计算“一级优秀”。在此示例中，1 月份的“本月前 2 名”是 Andrew 和 Jacob，2 月份的“前 2 名”是 Andrew、Dlyan 和 Jacob。因此，“一级优秀”的荣誉称号授予了Andrew和Jacob(仅此2位记录员)。

请帮我完成aim2(最终我应该达到aim 4，最终目标)，aim 2的结果应该是

TCIDAndrewJacob

explain:There are some technical service data (ServiceID, TCID, EndTime, and QoS) in a whole year, and ID is a unique primary key because there are some duplicates.

Almost forget to say that, just a reminder, the client only accept the SQL instead of stored procedure in database to implement it. And I only need query statement because customer did not allow us to write database.

 some data:

ServiceID   ID  TCID    EndTime Qos2000    2   Jacob   2011/1/1    22000    3   Jacob   2011/1/1    22001    4   Jacob   2011/1/1    22002    5   Jacob   2011/2/3    12003    6   Tyler   2011/1/4    1

Data Structure:

• ID: Unique primary key of record
• ServiceID: ID of a certain service
• TCID: ID of a technical support engineer
• EndTime: Ending Time of aservice
• Qos：Quality of service (1 Satisfied; 2 Average; 3Unsatisfactory/Dissatisfied).

DDL and insert SQL(mysql5):

CREATE TABLE `service` (
  `ServiceID` INTEGER(11) NOT NULL,
  `ID` INTEGER(11) NOT NULL ,
  `TCID` VARCHAR(40)  NOT NULL,
  `EndTime` DATE NOT NULL,
  `Qos` CHAR(1)  NOT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `ID` (`ID`)
);
COMMIT;
INSERT INTO `service` (`ServiceID`, `ID`, `TCID`, `EndTime`, `Qos`) VALUES
  (2004, 9, 'Jacob', '2011-02-04', '1'),
  (2000, 2, 'Jacob', '2011-01-01', '2'),
  (2000, 3, 'Jacob', '2011-01-01', '2'),
  (2001, 4, 'Jacob', '2011-01-01', '2'),
  (2002, 5, 'Jacob', '2011-02-03', '1'),
  (2003, 6, 'Tyler', '2011-01-04', '1'),
  (2003, 7, 'Tyler', '2011-01-04', '1'),
  (2003, 8, 'Tyler', '2011-01-03', '2'),
  (2005, 10, 'Jacob', '2011-02-05', '1'),
  (2006, 11, 'Jacob', '2011-02-04', '2'),
  (2007, 12, 'Jacob', '2011-01-08', '1'),
  (2008, 13, 'Tyler', '2011-02-06', '1'),
  (2009, 14, 'Dylan', '2011-02-08', '1'),
  (2010, 15, 'Dylan', '2011-02-09', '1'),
  (2014, 16, 'Andrew', '2011-01-01', '1'),
  (2013, 17, 'Andrew', '2011-01-01', '1'),
  (2012, 18, 'Andrew', '2011-02-19', '1'),
  (2011, 19, 'Andrew', '2011-02-02', '1'),
  (2015, 20, 'Andrew', '2011-02-01', '1'),
  (2016, 21, 'Andrew', '2011-01-19', '1'),
  (2017, 22, 'Jacob', '2011-01-01', '1'),
  (2018, 23, 'Dylan', '2011-02-03', '1'),
  (2019, 24, 'Dylan', '2011-01-09', '1'),
  (2020, 25, 'Dylan', '2011-01-01', '1'),
  (2021, 26, 'Andrew', '2011-01-03', '1'),
  (2021, 27, 'Dylan', '2011-01-11', '1'),
  (2022, 28, 'Jacob', '2011-01-09', '1'),
  (2023, 29, 'Tyler', '2011-01-19', '1'),
  (2024, 30, 'Andrew', '2011-02-01', '1'),
  (2025, 31, 'Dylan', '2011-02-03', '1'),
  (2026, 32, 'Jacob', '2011-02-04', '1'),
  (2027, 33, 'Tyler', '2011-02-09', '1'),
  (2028, 34, 'Daniel', '2011-01-06', '1'),
  (2029, 35, 'Daniel', '2011-02-01', '1');
 COMMIT;

这是我的前两个目标，还有两个，我打算一个接一个地完成所有这些步骤。谁能帮助实现第一个目标？我知道这很复杂，非常感谢您。

目标3:那么计算“二等优秀”(不包括“本月以来Top 2”的工程师)等于计算排名第三和第四的工程师。在这个例子中，“第 2 类杰出人物”是 Tyler。

目标4:最终目标是将“一级优秀”与“二级优秀”相结合。结果将最终传输到报告以进行渲染。我的数据集应该如下所示:

TCID       level
Andrew     top2
Jacob      top2
Tyler      top4

目标 1 的结果应该是这样的:

2000   2   Jacob   2011/1/1    22001    4   Jacob   2011/1/1    22002    5   Jacob   2011/2/3    12003    6   Tyler   2011/1/4    12004    9   Jacob   2011/2/4    12005    10  Jacob   2011/2/5    12006    11  Jacob   2011/2/4    22007    12  Jacob   2011/1/8    12008    13  Tyler   2011/2/6    12009    14  Dyla    2011/2/8    12010    15  Dyla    2011/2/9    12011    19  Andrew  2011/2/2    12012    18  Andrew  2011/2/19   12013    17  Andrew  2011/1/1    12014    16  Andrew  2011/1/1    12015    20  Andrew  2011/2/1    12016    21  Andrew  2011/1/19   12017    22  Jacob   2011/1/1    12018    23  Dyla    2011/2/3    12019    24  Dyla    2011/1/9    12020    25  Dyla    2011/1/1    12021    26  Andrew  2011/1/3    12022    28  Jacob   2011/1/9    12023    29  Tyler   2011/1/19   12024    30  Andrew  2011/2/1    12025    31  Dyla    2011/2/3    12026    32  Jacob   2011/2/4    12027    33  Tyler   2011/2/9    12028    34  Daniel  2011/1/6    12029    35  Daniel  2011/2/1    1

Answer 1

最后，我找到了一个方案，就是用集算器来解决这个问题。集算器是我找到的唯一解决方案。是的，它可以返回一个 resultSet 对象并由报告工具调用(我正在使用 Jasper)。但我仍然需要一个 SQL 解决方案。所以，请告诉我是否有任何 SQL 解决方案。感谢您的回复。

以下是有关此解决方案的一些信息:http://www.esproc.com/tour/what-is-esproc.html

或者这是一个 SQL Server 解决方案(但不适用于 mysql):http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/e11c754d-559e-4eb7-9682-e998ea3387d9

希望对遇到同样问题的人有所帮助。