我有两个表:1 个表 wccrm_orders 存储所有订单,一个表 wccrm_kunden 存储所有用户数据。在 wccrm_orders 中,我有一个日期字段“ordered_date”,在 wccrm_kunden 中有一个日期字段“Anprobe”
当我想选择 wccrm_orders 时,我使用该代码:
Select Year(wccrm_orders.ordered_date) as Jahr,
Month(wccrm_orders.ordered_date) as Monat, round(sum(wccrm_orders.preis)) as Summe,
count(*) as Anzahl from wccrm_orders
GROUP BY YEAR(wccrm_orders.ordered_date), MONTH(wccrm_orders.ordered_date)
当我想计算(*)来自 wccrm_kunden.anprobe 的所有约会时,我使用以下代码:
Select Year(wccrm_kunden.anprobe) as Jahr, Month(wccrm_kunden.anprobe) as
Monat, count(*) as Anzahl from wccrm_kunden where wccrm_kunden.status = 1
GROUP BY YEAR(wccrm_kunden.anprobe), MONTH(wccrm_kunden.anprobe)
如何组合这些代码?我怎样才能实现它,将试验(约会)的数量放入第一个 SELECT 中?
非常感谢您的帮助!
BR,
斯特凡
几个子查询可能有效:
SELECT A.Jahr, A.Monat, A.Summe, A.Anzahl, B.AnzahlB
FROM
(SELECT Year(wccrm_orders.ordered_date) as Jahr,
Month(wccrm_orders.ordered_date) as Monat,
round(sum(wccrm_orders.preis)) as Summe,
count(*) as Anzahl
FROM wccrm_orders
GROUP BY
YEAR(wccrm_orders.ordered_date),
MONTH(wccrm_orders.ordered_date)) AS A
LEFT OUTER JOIN
(SELECT Year(wccrm_kunden.anprobe) as Jahr,
Month(wccrm_kunden.anprobe) as Monat,
count(*) as AnzahlB
FROM wccrm_kunden
WHERE wccrm_kunden.status = 1
GROUP BY
YEAR(wccrm_kunden.anprobe),
MONTH(wccrm_kunden.anprobe)) AS B
ON A.Jahr = B.Jahr AND A.Monat = B.Monat
(抱歉,没有此数据库的架构,因此此代码中可能存在语法错误(或三个!),但希望您能理解。)
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句