主页 / dba / 问题 / 112583
Accepted
Human_AfterAll
Asked: 2015-08-29 12:53:57 +0800 CST

按日期分组时甚至返回空组

  • 772

我有以下查询：

SELECT DISTINCT
           COUNT(CD_BarCode)
           ,CD_Carrier
           ,SUBSTRING(CONVERT(VARCHAR,DT_Arriving,103),1,11) Date
FROM TB_AGIL 
WHERE 
           DT_Arriving >= @date
           AND DT_Arriving < DATEADD(MONTH,+1,@date)
           AND CD_TRACKING = 14
GROUP BY CD_Carrier, SUBSTRING(CONVERT(VARCHAR,DT_Arriving,103),1,11)
ORDER BY Date

我用它来查看上个月货物运动的结果。结果是按日期分组的cd_tracking数量列表，如下所示：

| Amount | Carrier |    Date    |
|--------|---------|------------|
|  2599  |   44    | 01/08/2015 |
|  2504  |   44    | 03/08/2015 |
|  4597  |   44    | 04/08/2015 |
|  5058  |   44    | 05/08/2015 |
|  2413  |   44    | 06/08/2015 |
|  4853  |   44    | 07/08/2015 |

我的问题是，如果某一天没有cd_tracking = 14，那一天将不会显示在结果列表中。如您所见，列表中没有8 月 2日。

我想知道是否有办法将每一天都带上结果列表，即使那天没有cd_tracking = 14。像这样：

| Amount | Carrier |    Date    |
|--------|---------|------------|
|  2599  |   44    | 01/08/2015 |
|  NULL  |   44    | 02/08/2015 |
|  2504  |   44    | 03/08/2015 |
|  4597  |   44    | 04/08/2015 |
|  5058  |   44    | 05/08/2015 |
|  2413  |   44    | 06/08/2015 |
|  4853  |   44    | 07/08/2015 |
sql-server t-sql

3 个回答

  1. Best Answer

    将条件从WHERE子句移到条件计数：

    SELECT
    COUNT(CASE WHEN CD_TRACKING = 14 THEN CD_BarCode ELSE NULL END),
    CD_Carrier,
    DATEPART(DAY, DT_Arriving) as [Date]
FROM TB_AGIL 
WHERE
    DT_Arriving >= @date
    AND DT_Arriving < DATEADD(MONTH,+1,@date)
GROUP BY 
    CD_Carrier, 
    DATEPART(DAY, DT_Arriving)
ORDER BY 
    [Date];

    可以在此处找到实现已弃用 GROUP BY ALL 语法的其他方法。

    • 4

  2. 对于这类报告，我更喜欢使用数字表或日历表。数字表只是一个表，其中有一列包含从 1 到某个足够大的数字（例如 100,000）的整数。我个人使用带有 100K 数字的表格。Aaron Bertrand 写了一篇很好的文章来解释如何生成这样的表。日历表只是一个表，其中有一列的日期覆盖了足够大的范围。通常从 1900 年到 2200 年的所有日期就足够了。

    因此，一旦您有一个Calendar带有 column的表dt，该表具有date值并且是主键，我们就可以在您的查询中使用它。

    CREATE TABLE [dbo].[Calendar](
    [dt] [date] NOT NULL,
CONSTRAINT [PK_Calendar] PRIMARY KEY CLUSTERED 
(
    [dt] ASC
))

    DISTINCT在您的 中没有做任何事情SELECT，所以我将其删除。要分组，date我将类型转换为DT_Arrivingto date，而不是varchar. 我认为DT_Arriving不是date类型。如果是，则不需要强制转换。如果您确实需要将日期返回给客户varchar，请在最后执行SELECT。

    CTE_Main是您的原始查询。CD_Carrier对于出现在结果中的每个日期，我们需要整月的日期CTE_Main。所以，CTE_Carriers给我们一个所有CD_Carrier值的列表。我们CROSS JOIN使用Calendar表格来获取每个月中所有日期的列表CD_Carrier。然后我们LEFT JOIN CTE_Main以这个结果来得到Amount。

    WITH
CTE_Main
AS
(
    SELECT
        COUNT(CD_BarCode) AS Amount
        ,CD_Carrier
        ,CAST(DT_Arriving AS date) AS dt
    FROM TB_AGIL 
    WHERE 
        DT_Arriving >= @date
        AND DT_Arriving < DATEADD(MONTH,+1,@date)
        AND CD_TRACKING = 14
    GROUP BY CD_Carrier, CAST(DT_Arriving AS date)
)
,CTE_Carriers
AS
(
    SELECT CD_Carrier
    FROM CTE_Main
    GROUP BY CD_Carrier
)
SELECT
    CTE_Main.Amount
    ,CTE_Carriers.CD_Carrier
    ,Calendar.dt
FROM
    CTE_Carriers
    CROSS JOIN Calendar
    LEFT JOIN CTE_Main ON 
        CTE_Main.CD_Carrier = CTE_Carriers.CD_Carrier
        AND CTE_Main.dt = Calendar.dt
WHERE
    Calendar.dt >= @date
    AND Calendar.dt < DATEADD(MONTH,+1,@date)
ORDER BY dt;
    • 2

  3. 如果日期不在您的结果集中，那么您需要从某个地方获取它们吗？您可以使用包含日期的派生表创建连接。

    • 1

相关问题