MySQL 数据透视动态表并仅在计数 > 0 时返回

Question

我有一个包含此计数案例的查询，我使用它来根据案例透视一些总数。这是一个查询：

$user_attrib = some_value;
$user_size = some_value;
$items = [218, 219];
$items = join(",", $items);

SELECT owned_items.model_id,
       Count(owned_items.model_size_id) AS total_owned,
       makes.make,
       items.model_name,
       items.picture,
       items.color,
       items.material,
       Count(CASE item_sizes.id WHEN '1' THEN item_sizes.id ELSE NULL END) AS '1',
       Count(CASE item_sizes.id WHEN '2' THEN item_sizes.id ELSE NULL END) AS '2',
       Count(CASE item_sizes.id WHEN '3' THEN item_sizes.id ELSE NULL END) AS '3',
       Count(CASE item_sizes.id WHEN '4' THEN item_sizes.id ELSE NULL END) AS '4',
       Count(CASE item_sizes.id WHEN '5' THEN item_sizes.id ELSE NULL END) AS '5',
       Count(CASE item_sizes.id WHEN '6' THEN item_sizes.id ELSE NULL END) AS '6',
       Count(CASE item_sizes.id WHEN '7' THEN item_sizes.id ELSE NULL END) AS '7',
       Count(CASE item_sizes.id WHEN '8' THEN item_sizes.id ELSE NULL END) AS '8',
       Count(CASE item_sizes.id WHEN '9' THEN item_sizes.id ELSE NULL END) AS '9',
       Count(CASE item_sizes.id WHEN '10' THEN item_sizes.id ELSE NULL END) AS '10',
       Count(CASE item_sizes.id WHEN '11' THEN item_sizes.id ELSE NULL END) AS '11',
       Count(CASE item_sizes.id WHEN '12' THEN item_sizes.id ELSE NULL END) AS '12',
       Count(CASE item_sizes.id WHEN '13' THEN item_sizes.id ELSE NULL END) AS '13',
       Count(CASE item_sizes.id WHEN '14' THEN item_sizes.id ELSE NULL END) AS '14',
       Count(CASE item_sizes.id WHEN '15' THEN item_sizes.id ELSE NULL END) AS '15'
FROM   owned_items
       INNER JOIN owned_item_user
               ON owned_items.id = owned_item_user.owned_item_id
       INNER JOIN users
               ON owned_item_user.user_id = users.id
       INNER JOIN item_sizes
               ON owned_items.model_size_id = item_sizes.id
       INNER JOIN items
               ON owned_items.model_id = items.id
       INNER JOIN makes
               ON owned_items.make_id = makes.id      
WHERE  users.attrib_id = $user_attrib
       AND users.size_id = $user_size
       AND owned_items.model_id IN ($items)
GROUP  BY owned_items.model_id

我想概括数据，这样如果我添加第16个案例，我就不必手动添加另一行，并且我只想返回结果>0的案例。在第一部分中，我尝试使用这种透视动态表来调整代码，但没有成功。第二部分尝试设置HAVING Count(CASE item_sizes.id WHEN '1' THEN item_sizes.id ELSE NULL END) AS '1' > 0（等等）也没有成功。

Answer 1

您可以使用Group By+Count

Select item_sizes.id, count(*) from item_sizes where item_sizes.id between 1 and 15 group by item_sizes.id;