我是一个完整的MySQL新手,我试图检索两组结果显示在同一页面上,一个在另一个之上。我已经设法得到下面的工作与UNION函数,它"几乎"做了我需要的,除了它结合在一个表的结果。我希望第二个SELECT命令在第一个SELECT命令下面显示为一个不同的表。这可能吗?下面是我到目前为止的代码:
SELECT wp_wpdatatable_1.season,
wp_wpdatatable_1.date,
wp_wpdatatable_1.ha,
wp_wpdatatable_1.competition,
wp_wpdatatable_1.result,
wp_wpdatatable_1.score,
wp_wpdatatable_1.scorers
FROM wp_wpdatatable_1
WHERE 1=1
AND wp_wpdatatable_1.opponents = 'Everton'
UNION
SELECT
null AS count1,
null AS count2,
null AS count3,
SUM(CASE WHEN opponents='Everton' THEN 1 ELSE 0 END) AS played,
SUM(CASE WHEN result LIKE 'W' and opponents='Everton' THEN 1 ELSE 0 END) AS won,
SUM(CASE WHEN result LIKE 'D' and opponents='Everton' THEN 1 ELSE 0 END) AS drawn,
SUM(CASE WHEN result LIKE 'L' and opponents='Everton' THEN 1 ELSE 0 END) AS lost
FROM wp_wpdatatable_1
你的意思是你想看到2个单独的列标题,在这种情况下不确定这在mySQL中是可能的吗?
但是如果你只是想要一行接一行的顺序,添加一个虚拟列,你可以按顺序排序(并不是必要的,因为顺序将使记录唯一,但在你的用例中,添加UNION ALL以避免每个文档消除重复行http://dev.mysql.com/doc/refman/5.7/en/union.html)
(SELECT wp_wpdatatable_1.season,
wp_wpdatatable_1.date,
wp_wpdatatable_1.ha,
wp_wpdatatable_1.competition,
wp_wpdatatable_1.result,
wp_wpdatatable_1.score,
wp_wpdatatable_1.scorers,
1 as table_order
FROM wp_wpdatatable_1
WHERE 1=1
AND wp_wpdatatable_1.opponents = 'Everton'
)
UNION ALL
(
SELECT
null AS count1,
null AS count2,
null AS count3,
SUM(CASE WHEN opponents='Everton' THEN 1 ELSE 0 END) AS played,
SUM(CASE WHEN result LIKE 'W' and opponents='Everton' THEN 1 ELSE 0 END) AS won,
SUM(CASE WHEN result LIKE 'D' and opponents='Everton' THEN 1 ELSE 0 END) AS drawn,
SUM(CASE WHEN result LIKE 'L' and opponents='Everton' THEN 1 ELSE 0 END) AS lost,
2 as table_order
FROM wp_wpdatatable_1
)
ORDER BY table_order