如何计算具有特定ID的联接表中的所有行

问题描述：

I have two tables, one is a list of songs. The other is a list of projects.

They are joined by the songsID. My query currently looks like this:-

$sQuery = "
        SELECT SQL_CALC_FOUND_ROWS ".str_replace(" , ", " ", implode(", ", $aColumns))."
        FROM   $sTable
        LEFT JOIN
            $sTable2
            ON ($sTable2.songs_id = $sTable.songsID)
        $sWhere
        $sOrder
        $sLimit
    ";
    $rResult = mysql_query( $sQuery, $gaSql['link'] ) or die(mysql_error());

The $sTable and $sTable2 variables are the two tables clearly.

This works fine and lists all the rows I have in '$sTable'. The JOIN that you see above is not necassary to list the songs but is as far as I am able to get with my limited MySQL ability.

What I would like to do is have another column returned in my JSON data that displays the total COUNT of all projects (in $sTable2) for EACH song in '$sTable'. Therefore counting each project which has a specific 'songsID'.

$aColumns is the following:-

$aColumns = array( 'song_name', 'artist_band_name', 'author', 'song_artwork', 'song_file', 'genre', 'song_description', 'uploaded_time', 'emotion', 'tempo', 'songsID', 'user', 'happiness', 'instruments', 'similar_artists', 'play_count' );

These are the columns in $sTable, with 'songsID' being the auto increment primary key which is also stored in '$sTable2' to link the songs to their projects.

I need to be able to add 'projects_count' into the $aColumns array above.

Hopefully I have explained myself better this time. Apologies that my SQL experience is absolutely minimal.

我有两个表，一个是歌曲列表。另一个是项目列表。 p>

它们由songsID加入。我的查询目前看起来像这样： - p>

  $ sQuery =“
 SELECT SQL_CALC_FOUND_ROWS”.str_replace（“，”，“”，implode（“，”，$ aColumns）  ）。“
 FROM $ sTable 
 LEFT JOIN 
 $ sTable2 
 ON（$ sTable2.songs_id = $ sTable.songsID）
 $ sWhere 
 $ sOrder 
 $ sLimit 
”; 
 $ rResult  = mysql_query（$ sQuery，$ gaSql ['link']）或die（mysql_error（））;  
  code>  pre> 
 
  $ sTable和$ sTable2变量显然是两个表。 p> 
 
 
这很好用并列出了所有的行我 有'$ sTable'。 您在上面看到的JOIN并不是列出歌曲所必需的，但就我能够利用我有限的MySQL能力而言。   p> 
 
 
我想要做的是在我的JSON数据中返回另一列，显示'$ sTable'中每首歌曲的所有项目的总COUNT（在$ sTable2中）。 因此，计算具有特定'songsID'的每个项目。 p> 
 
 
 $ aColumns如下： -   p> 
 
 
  $ aColumns = array（'  song_name'，'artist_band_name'，'author'，'song_artwork'，'song_file'，'genre'，'song_description'，'uploaded_time'，'emotion'，'tempo'，'songsID'，'user'，'happiness'  ，'instruments'，'similar_artists'，'play_count'）; 
  code>  pre> 
 
 这些是$ sTable中的列，其中'songsID'是自动增量主键， 也存储在'$ sTable2'中以将歌曲链接到他们的项目。 p> 
 
 
我需要能够将'projects_count'添加到上面的$ aColumns数组中。 p> 
  
 
希望这次我能更好地解释自己。 抱歉我的SQL体验非常小。 p> 
  div>

答

select count(*) as projects_count, a.songs_id
from Table2 a
group by a.songs_id

This will give you the number of projects per songs_id.

You can also query for specific song_id with:

select count(*) as projects_count
from Table2 where Table2.songs_id = <your_song_id>

And this:

select b.*, bb.projects_count from Table b 
left join (
   select count(*) as projects_count, a.songs_id
   from Table2 a
   group by a.songs_id
) bb on bb.songs_id = b.songsID

如何计算具有特定ID的联接表中的所有行

相关推荐