我有3个表用户、产品和销售
user
UID | NAME
1 | agent1
2 | agent2
3 | agent3
4 | agent4
5 | agent5
products
PID | PNAME
1 | P1
2 | P2
3 | P3
sales
SID | UID | PID | SALES_CREATED
1 | 3 | 1 | 2013-07-13 01:15:04
2 | 1 | 1 | 2013-07-13 01:25:34
3 | 3 | 1 | 2013-07-13 02:01:34
4 | 3 | 1 | 2013-07-13 02:45:34
5 | 5 | 1 | 2013-07-13 02:56:34
6 | 5 | 1 | 2013-07-13 03:21:34
7 | 2 | 3 | 2013-07-13 03:38:34
8 | 3 | 2 | 2013-07-13 03:51:34
9 | 4 | 2 | 2013-07-13 04:25:34
10 | 3 | 1 | 2013-07-13 04:45:04
11 | 1 | 3 | 2013-07-13 04:55:34
12 | 2 | 2 | 2013-07-13 05:01:34
13 | 1 | 3 | 2013-07-13 05:15:34
14 | 5 | 3 | 2013-07-13 05:36:34
15 | 5 | 3 | 2013-07-13 06:21:34
其中,每个代理的销售结果为:(按用户UID排序)
user | P1 | P2 | P3 |
agent1 | 1 | 0 | 2 |
agent2 | 0 | 1 | 1 |
agent3 | 4 | 1 | 0 |
agent4 | 0 | 1 | 0 |
agent5 | 2 | 0 | 2 |
现在,我想要一个结果来按P1对销售额最高的用户进行排序,并且只有3个结果,结果将像这个一样
user | P1 | P2 | P3 |
agent3 | 4 | 1 | 0 |
agent5 | 2 | 0 | 2 |
agent1 | 1 | 0 | 2 |
你们能给我一个最好的mysqlquery来显示结果吗?
哦,像这样的。。。
SELECT uid
, SUM(pid=1) p1
, SUM(pid=2) p2
, SUM(pid=3) p3
FROM sales
GROUP
BY uid
ORDER
BY p1 DESC
, p2 DESC
, p3 DESC;
+-----+------+------+------+
| uid | p1 | p2 | p3 |
+-----+------+------+------+
| 3 | 4 | 1 | 0 |
| 5 | 2 | 0 | 2 |
| 1 | 1 | 0 | 2 |
| 2 | 0 | 1 | 1 |
| 4 | 0 | 1 | 0 |
+-----+------+------+------+
如果在应用程序级别处理显示逻辑,那么您的查询可以简化为更具可扩展性的查询,比如。。。
SELECT uid
, pid
, COUNT(*)
FROM sales
GROUP
BY uid,pid;
我之所以回答,是因为(1)我很无聊,(2)我想要一个脑筋急转弯练习。我通常不喜欢在人们发布和运行时回答(不回复任何人的评论/帖子),也不喜欢提供他们尝试过的内容(没有查询)
如果你想在未来获得更好/更多关于SO的帮助,那么发布你尝试过的查询将是有益的,当使用像这样复杂/多样化的数据来创建sqlfiddle-http://sqlfiddle.com/
因此,有了这个警告,以下是如何获得您想要的结果
SELECT
user.NAME as user,
SUM(PID=1) as P1,
SUM(PID=2) P2,
SUM(PID=3) P3
FROM
sales
LEFT JOIN
user
ON
user.UID = sales.UID
GROUP BY
sales.UID
HAVING
P1+P2+P3 >= 3
ORDER BY
P1 DESC, P2 DESC, P3 DESC;
下面是sqlfiddle示例,它展示了它的实际操作——http://sqlfiddle.com/#!2/d3405/8