自定义查询结果 [英] customize query result

问题描述

我有如下的mysql表格设计：

I have mysql table design as below:

ID    name     company       size
1      A      Company A       M
2      B      Company B       M
3      C      Company C       L
4      D      Company A       L
5      E      Company B       M
6      F      Company B       S

我想要的结果如下：

I want the result as below:

 Company       S      M       L
Company A      0      1       1
Company B      1      2       0
Company C      0      0       1
(possible to add a total here?)

任何人都可以提供帮助？我应该使用哪个命令？离开加入？我坚持要显示公司和S

这是我的查询：

anyone can help? which command should i use? left join? im stuck at showing "Company" and "S"
this is my query:

SELECT company, COUNT(size) AS S FROM member WHERE size = 'S' GROUP BY size, company

----------- ---------- UPDATE ---------------------------

---------------------UPDATE---------------------------

SELECT * FROM(
SELECT company, size, '1' AS amount FROM database.member
) AS p
PIVOT
(
SUM(amount)
for size IN (S, M, L, XL)
)AS pivot

推荐答案

您可以考虑使用 PIVOT [ ^ ]。

You could consider using a PIVOT[^].

对于MySQL数据透视，请参阅以下文章：

http://www.artfulsoftware.com/ infotree / queries.php＃78 [ ^ ]

http://www.artfulsoftware.com/infotree/queries.php ＃390 [ ^ ]

http://www.artfulsoftware.com/infotree/queries.php#79 [ ^ ]

http://en.wikibooks.org/wiki/Programming:MySQL/Pivot_table [ ^ ]

For MySQL pivot, see these articles:
http://www.artfulsoftware.com/infotree/queries.php#78[^]
http://www.artfulsoftware.com/infotree/queries.php#390[^]
http://www.artfulsoftware.com/infotree/queries.php#79[^]
http://en.wikibooks.org/wiki/Programming:MySQL/Pivot_table[^]

你们真棒，我终于解决了，分享的完整查询:)

u guys are awesome, i finally solved it, a complete query for share :)

SELECT company,
    SUM(CASE size WHEN 'S' THEN amount ELSE 0 END) AS S,
    SUM(CASE size WHEN 'M' THEN amount ELSE 0 END) AS M,
    SUM(CASE size WHEN 'L' THEN amount ELSE 0 END) AS L,
    SUM(CASE size WHEN 'XL' THEN amount ELSE 0 END) AS XL,
    SUM(CASE size WHEN 'XXL' THEN amount ELSE 0 END) AS XXL,
    SUM(CASE size WHEN 'XXXL' THEN amount ELSE 0 END) AS XXXL,
    SUM(amount) as Total
FROM(
SELECT company, size, '1' AS amount FROM member
) AS S
GROUP BY company

这篇关于自定义查询结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！