php - MySQL 工作台，如何创建日期过滤器来排列组

Question

我正在约会组工作。目前它不会返回任何结果。我尝试过不同的过滤器或没有日期的组，它很好并且运行良好，但我需要根据日期或时间戳创建一个组。

这是一个代码

SELECT 
    settlement_id,
    settlement_start_date,
    sku AS 'Settlement_sku',
        SUM(IF(transaction_type = 'order'
            AND amount_description = 'Principal'
            AND amount_type = 'ItemPrice',
        quantity_purchased,
        0)) AS 'total_QTY_Order'

FROM settlements
Where settlement_start_date ='2017-11-12'
GROUP BY sku 
HAVING Settlement_sku IS NOT NULL
    AND LENGTH(Settlement_sku) > 0
ORDER BY Settlement_sku ASC

当我得到结果时

如果我删除 where Settlement_start_date ='2017-11-12'并将其更改为 Where Settlement_id ='7135956892'

结果运行良好，请查看屏幕截图

问题是settlement_start_date是0000-00-00，那么如何在不依赖0000-00-00的情况下获得一个组。

这是表格正如您可以看到三个突出显示，因为我想要带有蓝色突出显示的黄色突出显示组并忽略所有红色突出显示

如果您不确定我在说什么，那么也许我可以解释更多

Answer 1

您可以使用自连接根据结算的最小 ID 填写开始日期和结束日期例如

select #s.minid,s.maxstdate,s.maxenddate, s2.*
        id                          ,                
        s2.settlement_id        ,    
        s.maxstdate             ,    
        s.maxenddate               ,    
        deposit_date            ,    
        total_amount            ,    
        currency                ,    
        transaction_type        ,    
        order_id                ,    
        merchant_order_id       ,    
        adjustment_id           ,    
        shipment_id             ,    
        marketplace_name        ,    
        amount_type             ,    
        amount_description      ,    
        amount                  ,    
        fulfillment_id          ,    
        posted_date             ,    
        posted_date_time        ,    
        order_item_code         ,    
        merchant_order_item_id  ,    
        merchant_adjustment_item_id ,
        sku                     ,    
        quantity_purchased      ,    
        promotion_id
from
(
select s1.settlement_id , min(id) minid,max(settlement_start_date) maxstdate,max(settlement_end_date) maxenddate 
from settlements_2_test s1
group by s1.settlement_id
) s
join settlements_2_test s2 on s2.id > s.minid and s2.settlement_id = s.settlement_id
order by id,settlement_id limit 5

结果

+----+---------------+------------+------------+--------------+--------------+----------+------------------+---------------------+---------------------+---------------+-------------+------------------+-------------+--------------------------+--------+----------------+-------------+-------------------------+-----------------+------------------------+-----------------------------+------------------+--------------------+--------------+
| id | settlement_id | maxstdate  | maxenddate | deposit_date | total_amount | currency | transaction_type | order_id            | merchant_order_id   | adjustment_id | shipment_id | marketplace_name | amount_type | amount_description       | amount | fulfillment_id | posted_date | posted_date_time        | order_item_code | merchant_order_item_id | merchant_adjustment_item_id | sku              | quantity_purchased | promotion_id |
+----+---------------+------------+------------+--------------+--------------+----------+------------------+---------------------+---------------------+---------------+-------------+------------------+-------------+--------------------------+--------+----------------+-------------+-------------------------+-----------------+------------------------+-----------------------------+------------------+--------------------+--------------+
|  2 |    7174103342 | 2017-12-24 | 2018-01-07 |              |         0.00 |          | Order            | 204-3505716-0081143 | 204-3505716-0081143 |               | DMX5Sr4rN   | Amazon.co.uk     | ItemPrice   | Principal                |  39.99 | AFN            | 26.12.2017  | 26.12.2017 15:08:49 UTC | 59733226356523  |                        |                             | UK-KC3816-0006   |                  1 |              |
|  3 |    7174103342 | 2017-12-24 | 2018-01-07 |              |         0.00 |          | Order            | 204-3505716-0081143 | 204-3505716-0081143 |               | DMX5Sr4rN   | Amazon.co.uk     | ItemFees    | FBAPerUnitFulfillmentFee |  -4.24 | AFN            | 26.12.2017  | 26.12.2017 15:08:49 UTC | 59733226356523  |                        |                             | UK-KC3816-0006   |                  1 |              |
|  4 |    7174103342 | 2017-12-24 | 2018-01-07 |              |         0.00 |          | Order            | 204-3505716-0081143 | 204-3505716-0081143 |               | DMX5Sr4rN   | Amazon.co.uk     | ItemFees    | Commission               |  -6.00 | AFN            | 26.12.2017  | 26.12.2017 15:08:49 UTC | 59733226356523  |                        |                             | UK-KC3816-0006   |                  1 |              |
|  5 |    7174103342 | 2017-12-24 | 2018-01-07 |              |         0.00 |          | Order            | 205-1808036-3091505 | 205-1808036-3091505 |               | D2RMyvqF4   | Amazon.co.uk     | ItemPrice   | Principal                |  29.99 | AFN            | 07.01.2018  | 07.01.2018 11:49:18 UTC | 25477500530227  |                        |                             | GB-KS8015-Grey-R |                  1 |              |
|  6 |    7174103342 | 2017-12-24 | 2018-01-07 |              |         0.00 |          | Order            | 205-1808036-3091505 | 205-1808036-3091505 |               | D2RMyvqF4   | Amazon.co.uk     | ItemFees    | FBAPerUnitFulfillmentFee |  -2.48 | AFN            | 07.01.2018  | 07.01.2018 11:49:18 UTC | 25477500530227  |                        |                             | GB-KS8015-Grey-R |                  1 |              |
+----+---------------+------------+------------+--------------+--------------+----------+------------------+---------------------+---------------------+---------------+-------------+------------------+-------------+--------------------------+--------+----------------+-------------+-------------------------+-----------------+------------------------+-----------------------------+------------------+--------------------+--------------+
5 rows in set (0.00 sec)