MySQL 查询返回它不应该返回的行

Question

我遇到了查询问题。

我正在使用 phpMyAdmin 和 MySQL。

我正在尝试报告我所有的活跃客户(in ___Kardex where KDX_Status='active')和:

• 计算他们的预订次数(在 ___Bookings where BOO_Status!='cancel')。
• 计算他们度过的夜晚数(在 ___Bookings where BOO_Status!='cancel')。

为清楚起见，这里是一个示例数据集

CREATE TABLE `___Bookings` (
  `BOO_Id` int(10) NOT NULL AUTO_INCREMENT,
  `BOO_HotelId` varchar(20) NOT NULL,
  `BOO_ClientId` int(10) NOT NULL,
  `BOO_CompanyId` int(10) NOT NULL,
  `BOO_BillingId` int(10) NOT NULL,
  `BOO_DateCI` date NOT NULL,
  `BOO_DateCO` date NOT NULL,
  `BOO_Status` enum('confirmed','notconfirmed','option','cancel','checkin','checkout') NOT NULL,
  UNIQUE KEY `BOO_Id` (`BOO_Id`),
  KEY `id` (`BOO_Id`)
) ENGINE=MyISAM AUTO_INCREMENT=73 DEFAULT CHARSET=utf8;

INSERT INTO `___Bookings` VALUES 
(70,'cus_CNHLMiMOzP5cuM',18,0,30,'2018-03-07','2018-03-12','confirmed'),
(71,'cus_CNHLMiMOzP5cuM',61,62,0,'2018-03-01','2018-03-02','cancel'),
(72,'cus_CNHLMiMOzP5cuM',19,0,0,'2018-03-04','2018-03-06','confirmed'),
(73,'cus_CNHLMiMOzP5cuM',61,0,0,'2018-03-01','2018-03-09','notconfirmed'),
(74,'cus_CNHLMiMOzP5cuM',61,0,0,'2018-03-10','2018-03-11','notconfirmed'),
 (75,'cus_CNHLMiMOzP5cuM',19,62,63,'2018-03-10','2018-03-21','option');

CREATE TABLE `___Hotels` (
  `HOT_HotelId` varchar(20) NOT NULL,
  `HOT_AutoLabel_VIP_Bookings` tinyint(4) NOT NULL,
  `HOT_AutoLabel_VIP_Nights` tinyint(4) NOT NULL,
  `HOT_AutoLabel_Regular_Bookings` tinyint(4) NOT NULL,
  `HOT_AutoLabel_Regular_Nights` tinyint(4) NOT NULL,
  `HOT_Status` enum('active','inactive','pending') NOT NULL,
  PRIMARY KEY (`HOT_HotelId`),
  UNIQUE KEY `HOT_HotelId` (`HOT_HotelId`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `___Hotels` VALUES 
('cus_CNHLMiMOzP5cuM', 10, 15, 20, 25, 'active');

CREATE TABLE `___Kardex` (
  `KDX_Id` int(10) NOT NULL AUTO_INCREMENT,
  `KDX_HotelId` varchar(20) NOT NULL,
  `KDX_Type` enum('client','company','billing') NOT NULL,
  `KDX_Status` enum('active','inactive') NOT NULL,
  UNIQUE KEY `KDX_Id` (`KDX_Id`),
  KEY `id` (`KDX_Id`)
) ENGINE=MyISAM AUTO_INCREMENT=63 DEFAULT CHARSET=utf8;

INSERT INTO `___Kardex` VALUES 
(18,'cus_CNHLMiMOzP5cuM','client','active'),
(19,'cus_CNHLMiMOzP5cuM','client','active'),
(30,'cus_CNHLMiMOzP5cuM','billing','active'),
(61,'cus_CNHLMiMOzP5cuM','client','active'),
(62,'cus_CNHLMiMOzP5cuM','company','inactive'),
(63,'cus_CNHLMiMOzP5cuM','company','active'),
(91,'cus_CNHLMiMOzP5cuM','company','active'),
(92,'cus_CNHLMiMOzP5cuM','company','active');

...以及我迄今为止的最大努力...

SELECT KDX_Id, KDX_Type,
  (
    SELECT COUNT(BOO_Id) 
    FROM ___Bookings 
    WHERE BOO_Status!='cancel' 
    AND (
      KDX_Id = ___Bookings.BOO_ClientId 
      OR KDX_Id = ___Bookings.BOO_CompanyId 
      OR KDX_Id = ___Bookings.BOO_BillingId 
        )
  ) AS nb_bookings,
  ( 
    SELECT SUM(DATEDIFF(___Bookings.BOO_DateCO, ___Bookings.BOO_DateCI)) 
    FROM ___Bookings 
    WHERE BOO_Status!='cancel' 
    AND (
        KDX_Id = ___Bookings.BOO_ClientId 
        OR KDX_Id = ___Bookings.BOO_CompanyId 
        OR KDX_Id = ___Bookings.BOO_BillingId
        )
  ) AS nb_nights, 
  HOT_HotelId, 
  HOT_AutoLabel_VIP_Bookings, 
  HOT_AutoLabel_VIP_Nights, 
  HOT_AutoLabel_Regular_Bookings, 
  HOT_AutoLabel_Regular_Nights 
FROM ___Kardex 
JOIN ___Hotels 
ON ___Kardex.KDX_HotelId = ___Hotels.HOT_HotelId 
JOIN ___Bookings 
ON ___Kardex.KDX_HotelId = ___Bookings.BOO_HotelId 
WHERE KDX_Status='active' 
AND HOT_Status='active' 
GROUP BY KDX_Id

和SQLFiddle的一样:

http://sqlfiddle.com/#!9/67775f/1

除了第 91 行和第 92 行之外，所需的输出应该与上面的 SQLFiddle 中的一样，因为我没有任何预订或夜数可显示这两个条目。

实际上，查询返回这些带有 NULL 或 0 条目的行。

预期结果

| KDX_Id | KDX_Type | nb_bookings | nb_nights |        HOT_HotelId | HOT_AutoLabel_VIP_Bookings | HOT_AutoLabel_VIP_Nights | HOT_AutoLabel_Regular_Bookings | HOT_AutoLabel_Regular_Nights |
|--------|----------|-------------|-----------|--------------------|----------------------------|--------------------------|--------------------------------|------------------------------|
|     18 |   client |           1 |         5 | cus_CNHLMiMOzP5cuM |                         10 |                       15 |                             20 |                           25 |
|     19 |   client |           2 |        13 | cus_CNHLMiMOzP5cuM |                         10 |                       15 |                             20 |                           25 |
|     30 |  billing |           1 |         5 | cus_CNHLMiMOzP5cuM |                         10 |                       15 |                             20 |                           25 |
|     61 |   client |           2 |         9 | cus_CNHLMiMOzP5cuM |                         10 |                       15 |                             20 |                           25 |
|     63 |  company |           1 |        11 | cus_CNHLMiMOzP5cuM |                         10 |                       15 |                             20 |                           25 | 

我们将不胜感激。

谢谢。

Answer 1

我重写了查询，因此您不需要 GROUP BY 来过滤掉重复项。

查询

SELECT 
   kardex_bookings.KDX_id
 , kardex_bookings.KDX_type
 , kardex_bookings.nb_bookings
 , kardex_bookings.nb_nights
 , hotels.HOT_HotelId
 , hotels.HOT_AutoLabel_VIP_Bookings
 , hotels.HOT_AutoLabel_VIP_Nights
 , hotels.HOT_AutoLabel_Regular_Bookings
 , hotels.HOT_AutoLabel_Regular_Nights

FROM ( 

  SELECT 
      kardex.KDX_id    
    , kardex.KDX_HotelId
    , kardex.KDX_type
    , kardex.KDX_Status
    , (
        SELECT
          COUNT(bookings.BOO_Id)
        FROM 
          ___Bookings bookings
        WHERE
            bookings.BOO_Status != 'cancel'
          AND (
              kardex.KDX_Id = bookings.BOO_ClientId 
            OR
              kardex.KDX_Id = bookings.BOO_CompanyId 
            OR
              kardex.KDX_Id = bookings.BOO_BillingId 
          )

      ) AS nb_bookings

    , ( 
        SELECT 
          SUM(DATEDIFF(bookings.BOO_DateCO, bookings.BOO_DateCI)) 
        FROM 
          ___Bookings bookings
        WHERE
          bookings.BOO_Status != 'cancel' 
          AND (
              kardex.KDX_Id = bookings.BOO_ClientId 
            OR
              kardex.KDX_Id = bookings.BOO_CompanyId 
            OR
              kardex.KDX_Id = bookings.BOO_BillingId 
          )    
      ) AS nb_nights    
  FROM 
    ___Kardex kardex     
) 
 AS kardex_bookings
INNER JOIN 
 ___Hotels hotels
ON 
 kardex_bookings.KDX_HotelId = hotels.HOT_HotelId

WHERE 
   kardex_bookings.KDX_Status = 'active'
 AND  
 # filter out non-bookings
   kardex_bookings.nb_bookings != 0
 AND
   kardex_bookings.nb_nights IS NOT NULL
 AND
   hotels.HOT_Status = 'active'

ORDER BY 
 kardex_bookings.KDX_Id ASC

结果

| KDX_id | KDX_type | nb_bookings | nb_nights |        HOT_HotelId | HOT_AutoLabel_VIP_Bookings | HOT_AutoLabel_VIP_Nights | HOT_AutoLabel_Regular_Bookings | HOT_AutoLabel_Regular_Nights |
|--------|----------|-------------|-----------|--------------------|----------------------------|--------------------------|--------------------------------|------------------------------|
|     18 |   client |           1 |         5 | cus_CNHLMiMOzP5cuM |                         10 |                       15 |                             20 |                           25 |
|     19 |   client |           2 |        13 | cus_CNHLMiMOzP5cuM |                         10 |                       15 |                             20 |                           25 |
|     30 |  billing |           1 |         5 | cus_CNHLMiMOzP5cuM |                         10 |                       15 |                             20 |                           25 |
|     61 |   client |           2 |         9 | cus_CNHLMiMOzP5cuM |                         10 |                       15 |                             20 |                           25 |
|     63 |  company |           1 |        11 | cus_CNHLMiMOzP5cuM |                         10 |                       15 |                             20 |                           25 |

演示

http://sqlfiddle.com/#!9/67775f/56