Geek Answers Handbook

How can I make this request?

This is a table for testing.

---------------------------------------------- id | username | point | level | created_date ---------------------------------------------- 1 | name_a | 1 | 1 | 2011-08-01 2 | name_a | 2 | 2 | 2011-08-02 3 | name_b | 5 | 1 | 2011-08-02 3 | name_c | 6 | 1 | 2011-08-02 4 | name_d | 1 | 1 | 2011-08-01 5 | name_d | 3 | 1 | 2011-08-02 5 | name_d | 5 | 2 | 2011-08-03 4 | name_e | 5 | 1 | 2011-08-01 5 | name_e | 5 | 2 | 2011-08-02 5 | name_e | 5 | 3 | 2011-08-03 ----------------------------------------------

The requirement for a query is to query (as much as possible in a single query) the username, table point.

  • Sort by the total score of the user at each level.
  • If the user has 2 points at the same level, he will receive only the last result.
  • Group by username
  • total score must be less than 10
  • Maximum score for level 5

Output Sample:

 -------------------- username | tpoint| -------------------- name_d | 8 | name_b | 5 | name_a | 3 | --------------------

name_e and name_c were ignored.

+4
source share
4 answers

Sounds like a funny request!

 SELECT username, SUM(point) AS points FROM (SELECT username, level, point FROM (SELECT username, level, LEAST(point, 5) AS point FROM table WHERE points <= 5 ORDER BY created_date DESC) AS h GROUP BY username, level) AS h2 GROUP BY username HAVING points < 10 ORDER BY points DESC

It should be! Just replace the "table".

EDIT:

Do you want to exclude rows that have a score of more than 5, or a value of 5? Just remove the WHERE points <= 5, if any.

+7
source 
 SELECT SUM(t3.point) AS tpoint, t3.username FROM ( SELECT t1.level, t1.username, t1.created_date, t1.point FROM testing AS t1 INNER JOIN (SELECT level, username, MAX(created_date) AS MaxDate FROM testing) AS t2 ON (t1.level=t2.level AND t1.username=t2.username AND t1.created_date = t2.MaxDate) WHERE t1.point <= 5 ) AS t3 GROUP BY t3.username HAVING tpoint < 10 ORDER BY tpoint DESC

I don’t know if I used the aliases correctly, I hope this works!

An internal query with a join is to get the last username, a combination of levels where the number of points of one level is> 5. Then it is used to get the total amount for one username and drop them with more than 10 points.

+2
source 
 SELECT Query2.username , Sum(Query2.SomVanpoint) AS point FROM (SELECT test.username , test.level , Sum(test.point) AS SomVanpoint FROM test INNER JOIN (SELECT test.username , test.level , Max(test.created_date) AS MaxVancreated_date FROM test GROUP BY test.username , test.level ) AS Query1 ON (test.username = Query1.username) AND (test.level = Query1.level) AND (test.created_date = Query1.MaxVancreated_date) GROUP BY test.username , test.level HAVING (((Sum(test.point))<= 5)) ) AS Query2 GROUP BY Query2.username HAVING (((Sum(Query2.SomVanpoint))< 10)) ORDER BY Sum(Query2.SomVanpoint) DESC;

=== output:

 username | point ----------+------ name_d | 8 name_b | 5 name_a | 3
+2
source

OK, get involved first 2.

 SELECT * FROM table a WHERE NOT EXISTS ( SELECT 1 FROM table b WHERE b.username=a.username AND a.created_date>b.created_date )

But mysql doesn’t do very well with push predicates, hence the max-concat trick, but it will make the query very difficult - it's worth revising if you have performance problems. Now add to another material .... parts 1,3 and 5

 SELECT username, level, SUM(point) FROM (SELECT * FROM table a WHERE NOT EXISTS ( SELECT 1 FROM table b WHERE b.username=a.username AND a.created_date>b.created_date ) ) ilv GROUP BY username, level HAVING SUM(point) <= 5;

How you implement 4 depends on the exact sequence in which this restriction applies to other constraints (in particular, 2 and 5). The following should produce a conclusion from the declared input ...

 SELECT username, level, SUM(point) FROM (SELECT * FROM table a WHERE NOT EXISTS ( SELECT 1 FROM table b WHERE b.username=a.username AND a.created_date>b.created_date ) ) ilv, (SELECT username, SUM(point) as totpoint FROM table c GROUP BY username HAVING SUM(point)<=10) ilv2 WHERE ilv.username=ilv2.username GROUP BY username, level HAVING SUM(point) <= 5;

Oops - just read it again and see that you are not interested in seeing a breakdown of the level in the output data set - in this case Robin's answer is best.

+1
source

More articles:


All Articles