php - MySQL多个子查询，获取日期间隔内每一天特定列的第一个值

Question

我已经被以下查询困扰了一段时间，并且不知道该怎么做(解释如下):

SELECT 
a.id, a.name, t.agency_id, t.initial_amount,
DATE_FORMAT(t.created_at, "%d.%m.%Y") as date,
DATE_FORMAT(t.created_at, "%Y-%m-%d") as date_created,
t.created_at, t.collection, t.pay, (t.initial_amount + t.collection - t.pay) as total_amount
FROM (
 SELECT
    hh.agency_id, hh.amount, h0.initial_amount, hh.created_at,
    SUM(IF(hh.payment_type=0,hh.amount,0)) AS collection,
    SUM(IF(hh.payment_type=1,hh.amount,0)) AS pay
 FROM house_register AS hh
 LEFT JOIN (
    SELECT h1.initial_amount, h1.agency_id, h1.created_at 
    FROM house_register AS h1
    INNER JOIN (
        SELECT min(h2.id) AS min_id, h2.agency_id, h2.created_at
        FROM house_register AS h2 
        GROUP BY h2.agency_id
    ) AS min_id_ag
        ON h1.agency_id = min_id_ag.agency_id
        AND h1.id = min_id_ag.min_id
) AS h0 
    ON hh.agency_id = h0.agency_id
WHERE hh.created_at BETWEEN '2015-11-01' AND '2015-11-30' 
GROUP BY hh.created_at, hh.agency_id WITH ROLLUP
) AS t 
INNER JOIN agencies as a
on a.id = t.agency_id

所以，我在这里想要实现的是从单个表中获取特定时间段的报告，在这个表中我有一些具有类型状态(付款或收款)的操作，并且每个操作对应于一个agency_id (商店)。

我的报告必须是这样的:

列:

agency_id    date X  initial_amount_of_first_transaction_of_day_in_loop sum(payments) sum(collection)

行看起来像这样:

agency_1 2015-01-01 500 100 0
agency_2 2015-01-01 600 100 0
.... next date
agency_1 2015-01-02 600 0 150
agency_2 2015-01-02 450 0 150
etc

我想要的是获取按 min(id) 分组的每个日期的第一笔交易的初始金额(因此为 agency_id )和created_at 。例如，如果我在 2015 年 1 月 1 日有 50 笔交易，我想获取 initial_amount 列的值该日期的第一笔交易。

现在它只选择整个日期间隔的最小值。我对所有日期都使用相同的最小值。

希望我说得足够清楚。

谢谢您的帮助!

更新结构:

CREATE TABLE IF NOT EXISTS `house_register` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`created_at` date DEFAULT NULL,
`created_time` time DEFAULT NULL,
`initial_amount` decimal(14,2) DEFAULT NULL,
`document_type` tinyint(3) unsigned DEFAULT NULL,
`document_id` int(10) unsigned DEFAULT NULL,
`document_number` varchar(100) DEFAULT NULL,
`description` varchar(200) DEFAULT NULL,
`amount` decimal(14,2) DEFAULT NULL,
`final_amount` decimal(14,2) DEFAULT NULL,
`agency_id` int(11) DEFAULT NULL,
`user_id` int(10) DEFAULT NULL,
`payment_type` tinyint(1) DEFAULT NULL COMMENT '0 - incasare, 1 - plata',
`number` varchar(50) DEFAULT NULL,
`debit_account` varchar(45) DEFAULT NULL,
`credit_account` varchar(45) DEFAULT NULL,
`short_desc` varchar(45) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=34;

使用结果集更新查询分割:

完整的返回: Results complete sql

子查询 1:Subquery 1 results

SELECT
hh.agency_id, hh.amount, h0.initial_amount, hh.created_at,
SUM(IF(hh.payment_type=0,hh.amount,0)) AS collection,
SUM(IF(hh.payment_type=1,hh.amount,0)) AS pay
FROM house_register AS hh
LEFT JOIN (
SELECT h1.initial_amount, h1.agency_id, h1.created_at 
FROM house_register AS h1
INNER JOIN (
    SELECT min(h2.id) AS min_id, h2.agency_id, h2.created_at
    FROM house_register AS h2 
    GROUP BY h2.agency_id
) AS min_id_ag
    ON h1.agency_id = min_id_ag.agency_id
    AND h1.id = min_id_ag.min_id
) AS h0 
ON hh.agency_id = h0.agency_id
WHERE hh.created_at BETWEEN '2015-11-01' AND '2015-11-30' 
GROUP BY hh.created_at, hh.agency_id WITH ROLLUP;

子查询2:查看评论

SELECT h1.initial_amount, h1.agency_id, h1.created_at 
FROM house_register AS h1
INNER JOIN (
    SELECT min(h2.id) AS min_id, h2.agency_id, h2.created_at
    FROM house_register AS h2 
    GROUP BY h2.agency_id
) AS min_id_ag
    ON h1.agency_id = min_id_ag.agency_id
    AND h1.id = min_id_ag.min_id;

子查询3:查看评论

SELECT min(h2.id) AS min_id, h2.agency_id, h2.created_at
    FROM house_register AS h2 
    GROUP BY h2.agency_id;

Answer 1

抱歉，是的，我不得不重新阅读你的问题几次，但我想我已经解决了。这能为您提供所需的结果吗？

对于min(h2.id)选择，您还需要group byh2.created_at，而不仅仅是h2 .agency_id - 完整分组依据 = GROUP BY h2.agency_id, h2.created_at 第 18 行。

SELECT 
a.id, a.name, t.agency_id, t.initial_amount,
DATE_FORMAT(t.created_at, "%d.%m.%Y") as date,
DATE_FORMAT(t.created_at, "%Y-%m-%d") as date_created,
t.created_at, t.collection, t.pay, (t.initial_amount + t.collection - t.pay) as total_amount
FROM (
 SELECT
    hh.agency_id, hh.amount, h0.initial_amount, hh.created_at,
    SUM(IF(hh.payment_type=0,hh.amount,0)) AS collection,
    SUM(IF(hh.payment_type=1,hh.amount,0)) AS pay
 FROM house_register AS hh
 LEFT JOIN (
    SELECT h1.initial_amount, h1.agency_id, h1.created_at 
    FROM house_register AS h1
    INNER JOIN (
        SELECT min(h2.id) AS min_id, h2.agency_id, h2.created_at
        FROM house_register AS h2 
        GROUP BY h2.agency_id, h2.created_at
    ) AS min_id_ag
        ON h1.agency_id = min_id_ag.agency_id
        AND h1.id = min_id_ag.min_id
) AS h0 
    ON hh.agency_id = h0.agency_id
WHERE hh.created_at BETWEEN '2015-11-01' AND '2015-11-30' 
GROUP BY hh.created_at, hh.agency_id WITH ROLLUP
) AS t 
INNER JOIN agencies as a
on a.id = t.agency_id