自定义查询结果

本文介绍了自定义查询结果的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有如下的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数据透视，请参阅以下文章： www.artfulsoftware/ infotree / queries.php＃78 [ ^ ] www.artfulsoftware/infotree/queries.php ＃390 [ ^ ] www.artfulsoftware/infotree/queries.php#79 [ ^ ] en.wikibooks/wiki/Programming:MySQL/Pivot_table [ ^ ] For MySQL pivot, see these articles: www.artfulsoftware/infotree/queries.php#78[^] www.artfulsoftware/infotree/queries.php#390[^] www.artfulsoftware/infotree/queries.php#79[^] en.wikibooks/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