mysql - 多个查询合而为一(报告)？

Question

如何将多个查询合并为一个？

例如:

//Successful Sales:
SELECT username, count(*) as TotalSales, sum(point) as Points FROM sales where submit_date >= 1301612400 AND submit_date <= 1304204400 AND status = 1 group by username

/Return Sales:
SELECT username, count(*) as Return FROM sales where submit_date >= 1301612400 AND submit_date <= 1304204400 AND status = 2 group by username

//Unsuccessful Sales:
SELECT username, count(*) as UnsuccessfulSales FROM sales where submit_date >= 1301612400 AND submit_date <= 1304204400  AND (status = 3 OR status = 6) group by username

所以报告看起来像这样:

另外，我如何添加返回百分比？

注意:修复了 SQL 查询

我尝试过这样做，但无法让它发挥作用？

SELECT username,  TotalSales, Points, Return
     FROM (
      SELECT username, count(*) as TotalSales, sum(point) as Points FROM sales where submit_date >= 1301612400 AND submit_date <= 1304204400 AND status = 1 group by username
      UNION
      SELECT count(*) as Return FROM sales where submit_date >= 1301612400 AND submit_date <= 1304204400 AND status = 4 group by username
    )

..

  //  Example Data Structure 

        CREATE TABLE IF NOT EXISTS `sales2` (
          `salesid` int(11) NOT NULL AUTO_INCREMENT,
          `username` varchar(50) NOT NULL,
          `point` int(11) NOT NULL,
          `status` int(11) NOT NULL,
          PRIMARY KEY (`salesid`)
        ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

        INSERT INTO `sales2` (`salesid`, `username`, `point`, `status`) VALUES
        (1, 'User1', 2, 1),
        (2, 'User1', 2, 1),
        (3, 'User2', 11, 1),
        (4, 'User2', 1, 2),
        (5, 'User3', 5, 6);

字段状态 = 1，成功销售并展示点

状态 2 - 返回销售

状态 3/6 - 销售不成功:

Answer 1

更新:对于你的第一个问题，我认为这会做你想要的(但请注意，这个查询非常慢，充满了表扫描......你应该要求更有经验的堆栈溢出用户来为你优化):

SELECT 
   distinct(outer_sales.username), 
   (SELECT count(*) as Points FROM sales where status = 1 AND username = outer_sales.username) as TotalSales,
   (SELECT sum(point) as Points FROM sales where status = 1 AND username = outer_sales.username) as Points,
   (SELECT count(*) FROM sales where status = 2 AND username = outer_sales.username) as Return,
   (SELECT count(*) FROM sales where (status = 3 OR status = 6) AND username = outer_sales.username) as UnsuccessfulSales
FROM 
   sales outer_sales
ORDER BY
   outer_sales.username;

对于第二个问题，如果您只想在 Return 列中添加百分号，则可以使用 CONCAT 函数:http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_concat .

尝试:

SELECT CONCAT(CAST(COUNT(*) AS CHAR), '%') AS Return ...