对于SQL查询返回的每个结果，我想要给出一个不同的名称

问题描述：

My SQL statement looks like

SELECT beds.id,beds.name,beds.brand,beds.price,beds.disprice,feature.feature AS feature 
FROM beds,feature,bedFeatures 
WHERE bedFeatures.featureID = feature.id AND bedFeatures.bedID = beds.id

I get this result

id  name        brand   price   disprice    feature
10  Big Sleeper ZZZZ    10000   1           Oak
10  Big Sleeper ZZZZ    10000   1           Ash
10  Big Sleeper ZZZZ    10000   1           White

What I want is for the AS to give each feature a unique name such as feature1 feature2 feature3 so those 4 lines are displyaed in one. Is this possible?

The output I am looking for would look something like

id  name        brand   price   disprice    feature1 feature2 feature3
10  Big Sleeper zzzz    1000    1           Oak      Ash      White

我的SQL语句类似于 p>

  SELECT beds.id，  bed.name，beds.brand，beds.price，beds.disprice，feature.feature AS feature 
FROM beds，feature，bedFeatures 
WHERE bedFeatures.featureID = feature.id AND bedFeatures.bedID = beds.id 
  code  >  pre> 
 
 我得到了这个结果 p> 
 
 
  id name brand price disprice feature 
10 Big Sleeper ZZZZ 10000 1 Oak 
10 Big Sleeper ZZZZ 10000  1 Ash 
10 Big Sleeper ZZZZ 10000 1 White 
  code>  pre> 
 
 我想要的是AS为每个功能提供一个唯一的名称，例如feature1 feature2 feature3所以这4行 是一体的。 这可能吗？ p> 
 
 
我要找的输出看起来像 p> 
 
 
  id name brand price disprice feature1 feature2 feature3 
10 Big  Sleeper zzzz 1000 1 Oak Ash White 
  code>  pre> 
  div>

答

The exact output you request is not easily achieved, except by using GROUP_CONCAT() to list the features as a comma-separated list rather than individual columns.

Because there is not a fixed set of possible features common to all your products, you will not be able to use a pivot query.

I would recommend using GROUP_CONCAT() to retrieve the features as a comma-separated list, and then splitting them apart with PHP in your application layer.

SELECT
  beds.id,
  beds.name,
  beds.brand,
  beds.price,
  beds.disprice,
  GROUP_CONCAT(feature) AS features
FROM
  beds
  JOIN bedFeatures ON beds.id = bedFeatures.bedID
  JOIN features ON features.id = bedFeatures.featureID
GROUP BY beds.id, beds.name, beds.brand, beds.price, disprice

The output of this will look like:

d  name        brand   price   disprice    features
10  Big Sleeper zzzz    1000    1           Oak,Ash,White

In your PHP, when fetching the results, explode() the features into an array:

$resultset = array();
while ($row = mysql_fetch_assoc($result)) {
  // First append the whole row onto the result set as it is...
  $resultset[$row['id']] = $row;
  // Overwrite the `features` string with an array instead...
  $resultset[$row['id']]['features'] = explode(",", $row['features']);
}

Finally, access the features in your application as:

foreach ($resultset as $r) {
  echo $r['features'][0];
  echo $r['features'][1];
  echo $r['features'][2];
  echo $r['features'][3];
}

答

You're looking for GROUP_CONCAT.

对于SQL查询返回的每个结果，我想要给出一个不同的名称

相关推荐