php mysql 2选择1选择

id | cid | name 
 1 | 1   | product 1 
 2 | 1   | product 2 
 3 | 1   | product 3 
 4 | 1   | product 4 
 5 | 1   | product 5 
 6 | 1   | product 6 
 7 | 2   | product 7 
 8 | 2   | product 8
 9 | 2   | product 9 
10 | 2   | product 10 
11 | 2   | product 11 
12 | 2   | product 12 
13 | 3   | product 13 
14 | 3   | product 14
15 | 3   | product 15
16 | 3   | product 16 

PHP:

$query  = "SELECT cid FROM product GROUP BY cid  ORDER by cid DESC";
$result = mysql_query($query) or die('Error : ' . mysql_error()); 

echo 'id |cid | name  <br>';

while($row = mysql_fetch_array($result, MYSQL_NUM))
{
    list($cid) = $row;

    $query2  = "SELECT id, cid, name FROM product WHERE cid  = $cid ORDER by id DESC LIMIT 3";
    $result2 = mysql_query($query2) or die('Error : ' . mysql_error());

    while($row2 = mysql_fetch_array($result2, MYSQL_NUM)){
        list($id2, $cid2, $name2) = $row2;
        echo ''.$id2.' | '.$cid2.' | '.$name2.'  <br>';
    }   

}

return ==>>

id | cid | name 
16 | 3   | product 16 
15 | 3   | product 15 
14 | 3   | product 14 
12 | 2   | product 12 
11 | 2   | product 11 
10 | 2   | product 10 
 6 | 1   | product 6 
 5 | 1   | product 5 
 4 | 1   | product 4 

Now, i wan't 2 select to 1 select.

example:

    $query  = "SELECT cid FROM product WHERE (SELECT id, cid, name FROM product WHERE cid  = $cid ORDER by id DESC LIMIT 3) GROUP BY cid  ORDER by cid DESC";
    $result = mysql_query($query) or die('Error : ' . mysql_error());

    while($row = mysql_fetch_array($result, MYSQL_NUM)){
        list($id, $cid, $name) = $row;
        echo ''.$id.' | '.$cid.' | '.$name.'  <br>';
    }

return ==>>

id | cid | name 

16 | 3   | product 16 
15 | 3   | product 15 
14 | 3   | product 14
12 | 2   | product 12 
11 | 2   | product 11 
10 | 2   | product 10 
 6 | 1   | product 6 
 5 | 1   | product 5 
 4 | 1   | product 4 

help me! thanks for support

Easiest way to achieve it would be as

SELECT *
FROM   product p
WHERE (
  SELECT  COUNT(*) 
  FROM    product  p1
  WHERE p1.cid = p.cid AND 
  p.id <= p1.id
) <= 3
order by p.id desc

DEMO

I don't know if this is the most elegant solution without using procedures...

What is done here is SUBSTRING_INDEX is being used to limit the entries used by GROUP_CONCAT.

And the result of GROUP_CONCAT is used as a set to be searched by FIND_IN_SET.

SELECT
    p1.*
FROM
    products p1
    inner join (
        SELECT
            SUBSTRING_INDEX(GROUP_CONCAT(p2.id SEPARATOR ','), ',', 3) AS ids
        FROM
            products p2
        GROUP BY
            p2.cid
    ) AS ids
        ON FIND_IN_SET(p1.id, ids.ids)
ORDER BY
    p1.cid DESC,
    p1.id

DEMO

PS:

You won't u run into this now, but just be aware: GROUP_CONCAT has a maximum length setting (as in characters length of a string) that sets what the GROUP_CONCAT can return. This is 1024 characters by default, and thus is sufficient for 3 INT ids because each INT id with adjending comma is max 12 characters. If you go extreme and want more than 85 records per cid, you will need to adjust your mysql group_concat_max_len setting.