排序MYSQL标记表 [英] Sorting MYSQL Tag table

问题描述

我有以下表格，

 标签
 ------- 
 id | title 
  

。

 code> tagged 
 ------ 
 tag_id | post_id 
  

和以下SQL查询

  SELECT标记。*，COUNT（td.tag_ID）AS标记
 FROM标记td 
 LEFT JOIN标记标记ON td.tag_ID = tag.tag_ID 
 BY td.tag_ID 
 ORDER BY tagcount DESC，tag.tag_Title ASC 
  

任何想法？ / p>

提前感谢

---

>

查询工作原理，我没有添加LIMIT 10，由于

<$>

p $ p> tag_ID tag_Title tagcount
1科学3
3化学2
4杂项1
5数学1
2运动1


我想要化学来到科学以上。

ie按字母顺序排列。

---

结果

这是一个工作示例

 （
 SELECT t。*，COUNT（*）AS tagcount 
 FROM tagged td 
 LEFT JOIN标签t ON（t.id = td。 tag_id）
 GROUP BY td.tag_id 
 ORDER BY tagcount DESC，t.title ASC 
 LIMIT 3 
）ORDER BY title ASC; 
  

解决方案

更新： p>

继续以下新评论：

  t。*，COUNT（*）AS tagcount 
 FROM tagged td 
 LEFT JOIN标签t ON（t.id = td.tag_id）
 GROUP BY td.tag_id 
 ORDER BY tagcount DESC，t.title ASC 
 LIMIT 3 
）ORDER BY title ASC; 
  

结果：

 code> + ------ + ------------ + ---------- + 
 | id |标题| tagcount | 
 + ------ + ------------ + ---------- + 
 | 3 | javascript | 2 | 
 | 1 | mysql | 2 | 
 | 2 | php | 3 | 
 + ------ + ------------ + ---------- + 
集合中的3行（0.00秒）
  

只需将 LIMIT 3 更改为 LIMIT 10 获得前10名，而不是前3名。

---

上一个答案：

为什么不在您的查询中添加 LIMIT 10 / p>

  SELECT t。*，COUNT（*）AS标记
从标记td 
 LEFT JOIN标记t ON （t.id = td.tag_id）
 GROUP BY td.tag_id 
 ORDER BY tagcount DESC，t.title ASC 
 LIMIT 10; 
  

测试用例：

  CREATE TABLE tags（id int，title varchar（20））; 
 CREATE TABLE tagged（tag_id int，post_id int）; 
 
 INSERT INTO tags VALUES（1，'mysql'）; 
 INSERT INTO tags VALUES（2，'php'）; 
 INSERT INTO tags VALUES（3，'javascript'）; 
 INSERT INTO tags VALUES（4，'c'）; 
 
 INSERT INTO标记值VALUES（1，1）; 
 INSERT INTO tagged VALUES（2，1）; 
 INSERT INTO tagged VALUES（1，2）; 
 INSERT INTO tagged VALUES（2，2）; 
 INSERT INTO tagged VALUES（3，3）; 
 INSERT INTO tagged VALUES（2，4）; 
 INSERT INTO tagged VALUES（3，4）; 
 INSERT INTO tagged VALUES（4，5）; 
  

结果（使用 LIMIT 3 ）： / p>

  + ------ + ------------ + ------ ---- + 
 | id |标题| tagcount | 
 + ------ + ------------ + ---------- + 
 | 2 | php | 3 | 
 | 3 | javascript | 2 | 
 | 1 | mysql | 2 | 
 + ------ + ------------ + ---------- + 
集合中的3行（0.00秒）
  

请注意 [c] 前3个结果和行按字母顺序排列，如果是领带。

just wondering if it is possible to get the top 10 COUNT results and ordering by COUNT and alphabetically?

I have the following tables,

tags
-------
id | title

.

tagged
------
tag_id | post_id

And the following SQL query

SELECT tag.*, COUNT(td.tag_ID) AS tagcount
FROM Tagged td
LEFT JOIN Tags tag ON td.tag_ID = tag.tag_ID
GROUP BY td.tag_ID
ORDER BY tagcount DESC, tag.tag_Title ASC

Any ideas?

Thanks in advance

---

Edit

Sorry if I didnt explain it properly.

The query works and I didnt add LIMIT 10 due to wanting to see the entire result set first.

The query I have works, however at the following example result

tag_ID  tag_Title  tagcount
1          Science  3
3          Chemistry 2
4          Misc      1
5          Maths       1
2          Sport       1

I would want Chemistry to come above Science though.

i.e. top ten highest counts.. sorted alphabetically

---

Result

Thanks to you both.. Daniel and Sled.

Here is a working example

( 
   SELECT     t.*, COUNT(*) AS tagcount
   FROM       tagged td
   LEFT JOIN  tags t ON (t.id = td.tag_id)
   GROUP BY   td.tag_id
   ORDER BY   tagcount DESC, t.title ASC
   LIMIT      3
) ORDER BY title ASC;

解决方案

UPDATE:

Further to the new comment below:

( 
   SELECT     t.*, COUNT(*) AS tagcount
   FROM       tagged td
   LEFT JOIN  tags t ON (t.id = td.tag_id)
   GROUP BY   td.tag_id
   ORDER BY   tagcount DESC, t.title ASC
   LIMIT      3
) ORDER BY title ASC;

Result:

+------+------------+----------+
| id   | title      | tagcount |
+------+------------+----------+
|    3 | javascript |        2 |
|    1 | mysql      |        2 |
|    2 | php        |        3 |
+------+------------+----------+
3 rows in set (0.00 sec)

Simply change the LIMIT 3 to LIMIT 10 to get the top 10 instead of the top 3.

---

Previous Answer:

Why don't you add a LIMIT 10 to your query?

SELECT     t.*, COUNT(*) AS tagcount
FROM       tagged td
LEFT JOIN  tags t ON (t.id = td.tag_id)
GROUP BY   td.tag_id
ORDER BY   tagcount DESC, t.title ASC
LIMIT      10;

Test case:

CREATE TABLE tags (id int, title varchar(20));
CREATE TABLE tagged (tag_id int, post_id int);

INSERT INTO tags VALUES (1, 'mysql');
INSERT INTO tags VALUES (2, 'php');
INSERT INTO tags VALUES (3, 'javascript');
INSERT INTO tags VALUES (4, 'c');

INSERT INTO tagged VALUES (1, 1);
INSERT INTO tagged VALUES (2, 1);
INSERT INTO tagged VALUES (1, 2);
INSERT INTO tagged VALUES (2, 2);
INSERT INTO tagged VALUES (3, 3);
INSERT INTO tagged VALUES (2, 4);
INSERT INTO tagged VALUES (3, 4);
INSERT INTO tagged VALUES (4, 5);

Result (using LIMIT 3):

+------+------------+----------+
| id   | title      | tagcount |
+------+------------+----------+
|    2 | php        |        3 |
|    3 | javascript |        2 |
|    1 | mysql      |        2 |
+------+------------+----------+
3 rows in set (0.00 sec)

Note how the [c] tag fell out of the top 3 results, and rows are ordered alphabetically in case of a tie.

这篇关于排序MYSQL标记表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！