如何筛选有关技术支持和计算订单等级的重复数据 [英] How to filtrate duplicate data about technical support and compute rank of order

问题描述

我对这个复杂的SQL有一个大问题。有人能帮我一把吗？


我的第一个目标（不是最终目标）是：使用查询语句筛选重复数据（选择不删除），即同一ServiceID的EndTime和Qos的各种条目由于反复的反馈。规则是仅保留具有相同ServiceID的最低Qos的记录。如果有几个记录具有最低的Qos，那么只保留这些记录中的任何一个。客户接受并优先选择此方案。在这个例子中，对于ID = 6,7和8，只需保留6或7.我在这里得到了这个目的的答案：

I’ve got a big problem with this complicated SQL. Could anyone give me a hand?

My first aim(not the final goal) is:fliter the duplicate data with query statment( select not delete) , that is, the various entries of EndTime and Qos for a same ServiceID due to the repeated feedback. The rule is to only keep the records with the lowest Qos for a same ServiceID. If there are several records having the lowest Qos, then only keep whatever one of these records. The client accepts and prefers to this scheme. In this example, for the ID=6,7, and 8, just keep 6 or 7.I have got answer of this aim here:

展开 | < span class =codeLinkonclick =selectAll（this）;>选择 | 换行 | 行号

推荐答案

相反，您应该对服务ID设置一个唯一约束，以便永远不会有多个约束。然后你可以在需要时更新qos。然后你不会有这个问题。


至于你的其他问题，请为每个问题创建一个不同的主题。

Instead, you should put a unique constraint on the service ID so that there can never be more than one. And then you can just update the qos when needed. Then you won''t have this problem.

As for your other questions, please create a different thread for each question.

兔子

谢谢你的回答。但我只需要查询语句，因为客户不允许我们编写数据库。实际上，我建议添加一个字段ID，这对客户来说是一个很大的让步。我们是BPO并且正在开发业务在线数据库（所以我无法在数据库中添加存储过程）。


我认为正确的答案如下（但我不能用SQL编写它） ）：

Rabbit
thank you for your answer. but I only need query statement because customer did not allow us to write database. actually, I had suggested to add a field "ID", It is a big concession to the client. We are BPO and working on the business online database(so I cant add a stored procedure to the database).

I think the correct answer is below (but I cant write it with SQL):

展开 | 选择 | Wrap | < span class =codeLinkonclick =LineNumbers（this）;>行号

创建一个聚合子查询，通过加入到服务ID的服务ID返回MIN（）qos表。获取该查询并使用它以服务ID返回MIN（）id。然后将其加入表格。

Create an aggregate subquery that returns the MIN() qos by service id joined to the table. Take that query and use it to return the MIN() id by service id. Then join that back to the table.

这篇关于如何筛选有关技术支持和计算订单等级的重复数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！