PHP MySQL 对值进行计数和排序

Question

我是新来的。如果我在错误的区域提出问题或忽略了之前搜索中的某些内容，从而违反了某种协议，我深表歉意。

我有一个名为 Art2011 的表格。

我正在三列中收集数据，并希望合并并计算类似的值。

painting1      painting2       painting3
-----------------------------------------
image6         image4          image3
image4         image1          image4
image8         image1          image3
image2         image9          image6
image6         image4          image3
image4         image1          image4
image8         image1          image3
image2         image9          image6

我如何在 php 中查询、计数和显示结果，看起来像这样？

image1 = 4
image2 = 2
image3 = 4
image4 = 5
etc...

先感谢您！ 迈克尔

Answer 1

尝试

SELECT t.image, COUNT(t.image)
FROM (
   SELECT painting1 as image FROM Art2011
  UNION ALL
   SELECT painting2 as image FROM Art2011
  UNION ALL
   SELECT painting3 as image FROM Art2011
) AS t
GROUP BY t.image

Answer 2

对于每张图像，您可以使用以下查询来获取计数：

select count(1) from Art2011 where Painting1 = 'image1' or Painting2 = 'image1' or Painting3 = 'image1'

Answer 3

添加到 @ain 的帖子：

$sql = "SELECT t.image as image, COUNT(t.image) as cnt FROM ( SELECT painting1 as image FROM Art2011 UNION ALL SELECT painting2 as image FROM Art2011 UNION ALL SELECT painting3 as image FROM Art2011 ) AS t GROUP BY t.image"; 
$result = mysql_query($sql) or die(mysql_error());
while($row = mysql_fetch_assoc($result)) {
    echo $row['image']." = ".$row['cnt']."\n";
}