代码之家 › 专栏 › 技术社区 › aanders77

去掉mysql查询中的空值

null database mysql

aanders77 · 技术社区 · 7 年前

我有一个存储钓鱼许可证的数据库。许可证有起止日期,我使用日历表来计算每天售出的许可证数量。每个许可证都有一个定义的许可证类型。许可证类型可以对一个或多个渔区有效。

我正在尝试构建一个查询,该查询显示Zones表中的一些列,以及按天和渔区分组并按licenseType筛选已售出的许可证数量。我已经让它在许可证销售的日子里发挥作用了。但是,没有销售许可证的日期只显示空值。

我已经为此绞尽脑汁8个小时了。我相信有一个简单的解决办法,我就是看不见。 SQL Fiddle here .

我认为这个模式是非常不言自明的,所以我不会在这里包括它。如果需要,请看小提琴。

SET @licensetype = 1,
    @fromdate = '2019-01-01',
    @todate = '2019-01-10';

SELECT zoneID,
   dy,
   seasonmax,
   daymax,
   COUNT(lID) AS sold
FROM
( SELECT z.zoneID,
       z.seasonmax,
       z.daymax,
       l.ID AS lID,
       l.From,
       l.To,
       lt.ValidForZone
FROM zones z
LEFT JOIN licensetypes lt ON z.zoneID IN(lt.ValidForZone)
LEFT JOIN licenses l ON lt.ID = l.TypeID
WHERE FIND_IN_SET( z.zoneID,
                   ( SELECT lt2.ValidForZone
                    FROM licensetypes lt2
                    WHERE lt2.ID = @licensetype ) ) ) derived
RIGHT JOIN calendar ON calendar.dy >= DATE_FORMAT(derived.From, '%Y-%m-%d')
AND calendar.dy < DATE_FORMAT(derived.To, '%Y-%m-%d')
WHERE calendar.dy >= @fromdate
AND calendar.dy <= @todate
GROUP BY dy,
     zoneID
ORDER BY dy

3 回复 | 直到 7 年前

Nick SamSmith1986 7 年前

我认为这个查询应该给您期望的结果(3/1为1,4/1和5/1为2)。你必须从一开始 CROSS JOIN 天和区域,然后 LEFT JOIN 到 licensetypes 和 licenses 在相关许可证类型和日期上:

SELECT z.zoneID, DATE(c.dy) AS dy, z.seasonmax, z.daymax, COUNT(l.ID) AS sold
FROM calendar c
CROSS JOIN zones z
LEFT JOIN licensetypes t ON t.ID = @licensetype AND FIND_IN_SET(z.zoneID, t.ValidForZone)
LEFT JOIN licenses l ON l.TypeID = t.ID AND DATE(c.dy) >= DATE(l.From) AND DATE(c.dy) < DATE(l.To)
WHERE DATE(c.dy) BETWEEN @fromdate AND @todate
GROUP BY dy, z.zoneID

Demo on SQLFiddle

Barmar 7 年前

如果你想在每一个区域都排一排,即使是那天没卖的区域,你也可以选择 zones 表在子查询之外。你想从一开始 CROSS JOIN 之间 地带 和 calendar 表获取区域和日期的所有组合。然后 LEFT JOIN 与许可证信息一起获取许可证计数。

此外,要使用逗号分隔的列表进行联接,需要使用 FIND_IN_SET 不是 IN() . 见 Search with comma-separated value mysql .

SELECT z.zoneID, c.dy, z.seasonmax, z.daymax, IFNULL(COUNT(l.ID), 0) AS sold
FROM zones AS z
CROSS JOIN calendar AS c
JOIN licensetypes AS lt ON FIND_IN_SET(z.zoneID, lt.ValidForZone)
LEFT JOIN licenses AS l ON lt.ID = l.typeID AND c.dy >= DATE_FORMAT(l.From, '%Y-%m-%d') AND c.dy < DATE_FORMAT(l.To, '%Y-%m-%d')
WHERE c.dy BETWEEN @fromdate AND @todate
    AND lt.ID = @licensetype
GROUP BY z.zoneID, c.dy
ORDER BY dy

GMB 7 年前

谢谢你的小提琴。我已根据以下准则对您的查询进行了大量修改:

子查询似乎没有必要:计算可以在一个 GROUP BY 查询
您需要笛卡尔积来生成所有区域的所有日期列表,因此 FROM calendar CROSS JOIN zones ;来自参数的常规过滤器,适用于 calendar 和 zones ,并且可以放置在 WHERE 条款
上的每个筛选条件 LEFT JOIN 应将ED表移动到 ON 有关联接的条款
使用 DATE 函数比 DATE_FORMAT 将日期时间转换为最新日期

新查询(见) the db fiddle ):

SELECT z.zoneID, calendar.dy, z.seasonmax, z.daymax, COUNT(DISTINCT l.ID) Asold
FROM 
    calendar
    CROSS JOIN zones z
    LEFT JOIN licensetypes lt ON z.zoneID IN(lt.ValidForZone)
    LEFT JOIN licenses l 
        ON  lt.ID = l.TypeID 
        AND calendar.dy BETWEEN DATE(l.From) AND DATE(l.To)
WHERE
    FIND_IN_SET( z.zoneID, ( SELECT lt2.ValidForZone FROM licensetypes lt2 WHERE lt2.ID = @licensetype ) )
    AND calendar.dy >= @fromdate
    AND calendar.dy <= @todate
GROUP BY z.zoneID, calendar.dy, z.seasonmax, z.daymax
ORDER BY dy

注意:此查询没有正确考虑多个逗号分隔值存储在 licensetypes.ValidForZone :当多个许可证类型匹配时,只计算第一个许可证类型(与原始查询相同)。最好是规范化数据库并使用bridge表来存储许可证类型和区域之间的1-n关系,而不是在单个字段中填充n个值…