我想将几个mySQL查询合并到一个结果表中。
一个例子:

+---------+--------------+--------------+
| Date    | Order number | Order status |
+---------|--------------|--------------|
| 1/1/13  | 1            | Open         |
| 1/3/13  | 1            | Confirmed    |
| 2/1/13  | 2            | Open         |
| 2/10/13 | 2            | Confirmed    |
| 2/20/13 | 1            | Shipped      |
| 3/13/13 | 2            | Shipped      |
...


我想查询给我这个结果表:

+-------+--------------+----------------+
| Month | Total orders | Shipped orders |
+-------|--------------|----------------|
| 1/13  | 1            | 0              |
| 2/13  | 2            | 1              |
| 3/13  | 1            | 1              |
+-------+--------------+----------------+


订单总数反映了具有任何活动的订单,发货订单不言自明。

我可以通过“月”和“订单总数”列获得一个结果表:


  选择date_format('Date','%c%y')作为月,选择count(distinct'Order number')作为来自表组的按月划分的总订单


以及带有“月份”和“已发货”订单列的结果表:


  从表中选择date_format('Date','%c%y')作为月份,将count(distinct'Order number')作为发货订单,其中'Order Status'='Shipped'按月份分组


如何将这两个选择查询结合起来,给我上面的一张表?我想我需要使用“月”进行某种形式的加入,但无法弄清楚。

最佳答案

这是SQLFIDDLE演示

SELECT
B.MONTH_YEAR, B.ORDER_CNTS, C.SHIPPED_ORDERS
FROM
(
SELECT MONTH_YEAR , COUNT(ORDERNO) AS ORDER_CNTS
FROM
(
SELECT DISTINCT CONCAT(CAST(MONTH(B.O_DATE) AS CHAR), '/', CAST(YEAR(B.O_DATE)  AS CHAR)) AS MONTH_YEAR , (ORDERNO) AS ORDERNO
FROM SAMPLE AS B
)
 B
GROUP BY MONTH_YEAR
) AS B
,
(
SELECT MONTH_YEAR , SUM(SHIPPED_ORDERS )AS SHIPPED_ORDERS
FROM
(
SELECT DISTINCT CONCAT(CAST(MONTH(B.O_DATE) AS CHAR), '/', CAST(YEAR(B.O_DATE)  AS CHAR)) AS MONTH_YEAR ,
  (CASE WHEN ORDER_STATUS= "Shipped" THEN 1 ELSE 0 END) AS SHIPPED_ORDERS
FROM SAMPLE AS B
)
 B
GROUP BY MONTH_YEAR
) AS C
WHERE B.MONTH_YEAR = C.MONTH_YEAR

关于mysql - 将多个MySQL查询与join结合,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/19986509/

10-12 12:55
查看更多