找到你要的答案

Q:Order table based on specific column count

Q:基于特定列计数的订单表

I have a projects table with the following columns id, project_name, remix_of
A project can be a remix of another project and the id of the project being remixed is stored in the remix_of column

id | project_name | remix_of
----------------------------
1  | 1st Project  | 0
2  | 2nd Project  | 0
3  | 3rd Project  | 2
4  | 4th Project  | 1
5  | 5th Project  | 2

I want to query all the projects, ordering by the amount of times it has been remixed, in this case it would be:

2nd Project | 2 remixes  
1st Project | 1 remix  
3rd Project | 0  
4th Project | 0  
5th Project | 0  

I'm using MySQL and have little experience with SQL overall.

I have a projects table with the following columns id, project_name, remix_of
A project can be a remix of another project and the id of the project being remixed is stored in the remix_of column

id | project_name | remix_of
----------------------------
1  | 1st Project  | 0
2  | 2nd Project  | 0
3  | 3rd Project  | 2
4  | 4th Project  | 1
5  | 5th Project  | 2

我想查询的所有项目,按次已混合的量,在这种情况下,它会:

2nd Project | 2 remixes  
1st Project | 1 remix  
3rd Project | 0  
4th Project | 0  
5th Project | 0  

我使用MySQL和SQL的总体经验少。

answer1: 回答1:

You can do this with a left join or correlated subquery:

select p.project_name,
       (select count(*) from projects pr where pr.remix_of = p.id) as remixes
from projects p 
order by remixes desc;

你可以做一个左连接或相关子查询:

select p.project_name,
       (select count(*) from projects pr where pr.remix_of = p.id) as remixes
from projects p 
order by remixes desc;
answer2: 回答2:
DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,project_name VARCHAR(20) NOT NULL
,remix_of INT NULL
);

INSERT INTO my_table VALUES
(1,'1st Project',NULL),
(2,'2nd Project',NULL),
(3,'3rd Project',2),
(4,'4th Project',1),
(5,'5th Project',2);

SELECT x.project_name
     , COUNT(y.id) remixes 
  FROM my_table x 
  LEFT 
  JOIN my_table y 
    ON y.remix_of = x.id 
 GROUP 
    BY x.project_name;
+--------------+---------+
| project_name | remixes |
+--------------+---------+
| 1st Project  |       1 |
| 2nd Project  |       2 |
| 3rd Project  |       0 |
| 4th Project  |       0 |
| 5th Project  |       0 |
+--------------+---------+
DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,project_name VARCHAR(20) NOT NULL
,remix_of INT NULL
);

INSERT INTO my_table VALUES
(1,'1st Project',NULL),
(2,'2nd Project',NULL),
(3,'3rd Project',2),
(4,'4th Project',1),
(5,'5th Project',2);

SELECT x.project_name
     , COUNT(y.id) remixes 
  FROM my_table x 
  LEFT 
  JOIN my_table y 
    ON y.remix_of = x.id 
 GROUP 
    BY x.project_name;
+--------------+---------+
| project_name | remixes |
+--------------+---------+
| 1st Project  |       1 |
| 2nd Project  |       2 |
| 3rd Project  |       0 |
| 4th Project  |       0 |
| 5th Project  |       0 |
+--------------+---------+
answer3: 回答3:

To query based on number of remixes, you can group by remix_of and get the COUNT(*):

SELECT remix_of AS project_id, COUNT(*) AS numRemixes
FROM myTable
GROUP BY project_id;

Then, if you need to get the values from the original table, just do a JOIN. You may want a left join to get 0 values:

SELECT m.id, m.project_name, COALESCE(mt.numRemixes, 0) AS numRemixes
FROM myTable m
LEFT JOIN(
   SELECT remix_of AS project_id, COUNT(*) AS numRemixes
   FROM myTable
   GROUP BY project_id) mt ON mt.project_id = m.id
ORDER BY numRemixes DESC;

基于一些混音的查询,你可以通过remix_of得到数组(*):

SELECT remix_of AS project_id, COUNT(*) AS numRemixes
FROM myTable
GROUP BY project_id;

然后,如果需要从原始表获取值,只需加入。您可能需要一个左连接获得0个值:

SELECT m.id, m.project_name, COALESCE(mt.numRemixes, 0) AS numRemixes
FROM myTable m
LEFT JOIN(
   SELECT remix_of AS project_id, COUNT(*) AS numRemixes
   FROM myTable
   GROUP BY project_id) mt ON mt.project_id = m.id
ORDER BY numRemixes DESC;
mysql  sql  laravel