没有测试,但它应该工作
select name, count(1), SUM(widget = 'red') reds, SUM(widget = 'green') greens, SUM(widget = 'blue') blues
from
(
(SELECT name, widget1 widget FROM table)
UNION
(SELECT name, widget2 widget FROM table)
UNION
(SELECT name, widget3 widget FROM table)
)q
WHERE widget <> ''
group by name;全部在一条线上
select name, count(1), SUM(widget = 'red') reds, SUM(widget = 'green') greens, SUM(widget = 'blue') blues from ((SELECT name, widget1 widget FROM table) UNION (SELECT name, widget2 widget FROM table) UNION (SELECT name, widget3 widget FROM table))q WHERE widget <> '' group by name;q是我们的标准化“表格”(不是真正的表格,但它看起来像一个)。
以这种方式查看我们的规范化表格
select * from ((SELECT name, widget1 widget FROM table) UNION (SELECT name, widget2 widget FROM table) UNION (SELECT name, widget3 widget FROM table))q;不确定你叫什么,我认为这是一个子查询。 (我已经使用MySQL多年,我仍然不知道正确的名称)