mysql 透视动态表并仅在 count > 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 的案例。对于第一部分，我尝试使用此 pivot dynamic tables调整代码但没有成功。第二部分我尝试设置 HAVING Count(CASE item_sizes.id WHEN '1' THEN item_sizes.id ELSE NULL END) AS '1' > 0 (等等)，但也没有成功。

Answer 1

您可以使用分组依据+计数

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