MySQL帐户老化报告

问题描述：

我试图得到一个在MySQL 5.0.81中工作的查询，它将提取尚未支付的发票上的到期金额。MySQL帐户老化报告

我有一个看起来像这样三个表：没有支付基于CLIENT_ID（invoices_client_id）拉发票（invoices_status = 0）：

CREATE TABLE `CLMS_invoices` (
    `invoices_id` mediumint(8) NOT NULL auto_increment, 
    `invoices_client_id` mediumint(8) NOT NULL default '0', 
    `invoices_datetimestamp` varchar(50) collate latin1_german2_ci NOT NULL default '', 
    `invoices_description` varchar(100) collate latin1_german2_ci NOT NULL default '', 
    `invoices_discount` decimal(12,2) NOT NULL default '0.00', 
    `invoices_note` text collate latin1_german2_ci NOT NULL, 
    `invoices_status` tinyint(1) NOT NULL default '0', 
    PRIMARY KEY (`invoices_id`) 
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci AUTO_INCREMENT=5 ; 

CREATE TABLE `CLMS_invoices_payments` (
    `invoices_payments_id` mediumint(8) NOT NULL auto_increment, 
    `invoices_payments_invoice_id` mediumint(8) NOT NULL default '0', 
    `invoices_payments_datetimestamp` varchar(50) collate latin1_german2_ci NOT NULL, 
    `invoices_payments_type` mediumint(8) NOT NULL default '0', 
    `invoices_payments_paid` decimal(12,2) NOT NULL default '0.00', 
    PRIMARY KEY (`invoices_payments_id`) 
) ENGINE=MyISAM AUTO_INCREMENT=10 DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci AUTO_INCREMENT=10 ; 

CREATE TABLE `CLMS_invoices_products` (
    `invoices_products_id` mediumint(8) NOT NULL auto_increment, 
    `invoices_products_invoice_id` mediumint(8) NOT NULL default '0', 
    `invoices_products_name` varchar(50) collate latin1_german2_ci NOT NULL default '', 
    `invoices_products_price` decimal(12,2) NOT NULL default '0.00', 
    `invoices_products_profit` decimal(12,2) NOT NULL default '0.00', 
    `invoices_products_qty` mediumint(8) NOT NULL default '0', 
    `invoices_products_shipping` decimal(12,2) NOT NULL default '0.00', 
    PRIMARY KEY (`invoices_products_id`) 
) ENGINE=MyISAM AUTO_INCREMENT=12 DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci AUTO_INCREMENT=12 ;

我需要查询做到以下几点。

老化应该如下：不到30天 31 - 60天 61 - 90天超过90天

大

它也需要返回与该下降的数额相关联的客户端id进入老龄化群体。

在组中的美元价值的计算方法如下：发票产品成本 - 发票贴现 - 已付金额至今

发票产品成本的计算公式为

SUM（（invoices_products_price + invoices_products_profit + invoices_products_shipping）* invoices_products_qty）WHERE invoices_products_invoice_id = invoices_id

发票折扣存储在invoices_discount

金额到目前为止付费被计算为

SUM（invoices_payments_paid）WHERE invoices_payments_invoice_id = invoices_id

最终的结果看起来应该像这样：

client_id pastDue1 pastDue2 pastDue3 pastDue4 
1 12.00 0.00 0.00 15.00 
5 2.00 60.00 80.00 32.00 
etc

有人可以帮我建立这个查询吗？

此列命名风格让我感到恶心。 – cherouvim 2009-12-08 22:26:48

我很乐意帮忙！我应该在哪里发送我的咨询服务建议？ – JohnFx 2009-12-08 22:32:00

@cherouvim：等到你看到一个3表连接完全合格的字段名称... – gahooa 2009-12-08 22:34:13

答

如果我理解正确 - 这里的关键问题是将所有用户分成不同的年龄段。

这可以通过IF(condition, then, else)声明在MySQL如下实现：

把第一次查询到一个临时表 -

CREATE TABLE `tmeporary_invoices` 
SELECT invoices.`invoices_client_id`, 
     SUM((products.`invoices_products_price` + products.`invoices_products_profit` + products.`invoices_products_shipping`) * products.`invoices_products_qty`) AS invoice_product_cost, 
     SUM(payments.`invoices_payments_paid`) AS amount_paid_so_far, 
     IF(DATEDIFF(NOW(), invoices.`invoices_datetimestamp`) <= 30 , 'pastDue1', IF(DATEDIFF(NOW(),invoices.`invoices_datetimestamp`) <= 60, 'pastDue2', 'pastDue2')) AS age 
FROM `CLMS_invoices` invoices 
    LEFT JOIN `CLMS_invoices_payments` payments 
      ON payments.`invoices_payments_invoice_id` = invoices.`invoices_id` 
    LEFT JOIN `CLMS_invoices_products` products 
      ON products.`invoices_products_invoice_id` = invoices.`invoices_id` 
WHERE invoices.`invoices_status = 0

事后从临时表中选择上面如下 -

SELECT `invoices_client_id`, 
     SUM(`invoice_product_cost`) AS invoice_product_cost, 
     SUM(`amount_paid_so_far`) AS amount_paid_so_far, 
     SUM(IF(`age` = 'pastDue1',1,0) AS `pastDue1`, 
     SUM(IF(`age` = 'pastDue2',1,0) AS `pastDue2`, 
     SUM(IF(`age` = 'pastDue3',1,0) AS `pastDue3`, 
FROM `tmeporary_invoices` 
GROUP BY `invoices_client_id`

该查询应该作为一般指导方针来说明如何完成所需的结果（只需复制/粘贴它们就可以了在命名约定/语法方面存在一些错误，因为我目前无法测试它们。

祝你好运！

MySQL帐户老化报告

相关推荐