复杂的SQL:如何使用查询来计算订单排名(第1类中的优秀) [英] complicated SQL:how to using a query to compute rank of order(Outstanding of Class 1)
问题描述
我已经遇到麻烦了2个星期,希望一些好心人可以帮助我。
背景:有客户在一年中提交的关于技术支持的一些技术服务数据(ServiceID,TCID,EndTime和QoS),并且有一个唯一的主键(ID名称)因为有一些重复。我必须使用一个单一的查询语句(不是插入/删除/更新,而不是存储过程)来处理计算,因为客户端不允许我在数据库中写入。而这个计算有些复杂(所以它分为4个目标,最后它应该是一个单独的SQL)。
整个目标可以分成一些目标(但我只需要最终目标),我已经开始了关于第一个的线程,我得到了正确答案。还有3个目标,所以我开始这个帖子找到一些帮助来达到最终目标。谢谢。
数据结构:
Hi,
I have be in trouble for 2 weeks, hope some nice people can help me.
Background:There are some technical service data (ServiceID, TCID, EndTime, and QoS) submitted by client in a whole year about technical support, and there are a unique primary key (ID by name) because there are some duplicates. I must use one single query statement(not insert/delete/update, and not stored procedure) to process a computation, because the client did not allow me to write in the database. And this computation is some complicated (so It is divided up 4 aims, and It should be a single SQL at last).
The whole goal can be divided up some aims(but I need just final goal), and I have started a thread about first one, and I have got correct answer. And There are 3 aims left, so I start this thread to find some help to reach final goal. thank you.
Data Structure:
推荐答案
aim2:
aim2:
谢谢你,luuk。
我注意到你的陈述几乎是正确的,但肯定有些错误,因为我想要排名而不是按顺序排序。
为例:1,1,10排名是1,1,3。否则,我不能选择具有相同数据的人。
过滤重复日期后的
,TCID和cnt(本月个人总体满意服务)和排名应该是:
Thank you, luuk.
I noticed that your statement is almost right,but there must be some mistaken, because I want rank but not rownumber by order.
as an example: 1,1,10 rank is 1,1,3. otherwise, I cann''t chose the people with the same data.
after filtering duplicate date, the TCID and cnt(Personal Total Satisfied Services this Month) and rank should be:
好的,现在对我来说太难了...
特别是如果你只想在MySQL中做这件事。
用PHP写一个简短的脚本会让事情变得更容易。
OK, that will be too hard for /me now...
Especially if you want to do it in MySQL only.
Writing a short script in i.e. PHP would make things easier.
这篇关于复杂的SQL:如何使用查询来计算订单排名(第1类中的优秀)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!