时间戳

首页 > 解决方案 > 计算每次旅行的预订 - 如果没有，则返回 0

mysql - 计算每次旅行的预订 - 如果没有，则返回 0

问题描述

我有两张桌子，

CREATE TABLE `voyages` (
`voyage_id` int(11) NOT NULL,
`voyage_type` int(11) NOT NULL,
`voyage_groupBooking` tinyint(4) NOT NULL DEFAULT 0,
`voyage_live` tinyint(4) NOT NULL DEFAULT 0,
`voyage_featured` tinyint(4) NOT NULL DEFAULT 0,
`voyage_name` varchar(60) NOT NULL,
`voyage_slug` varchar(60) NOT NULL,
`voyage_shortDescription` varchar(150) NOT NULL,
`voyage_shortPageDescription` text NOT NULL,
`voyage_tag` varchar(20) DEFAULT NULL,
`voyage_detail` text NOT NULL,
`voyage_ageBracket` text NOT NULL DEFAULT '14-18',
`voyage_included` varchar(150) NOT NULL,
`voyage_image` text DEFAULT NULL,
`voyage_startDate` date NOT NULL,
`voyage_startTime` time NOT NULL,
`voyage_endDate` date NOT NULL,
`voyage_cost` decimal(11,2) NOT NULL,
`voyage_miles` int(11) DEFAULT NULL,
`voyage_hours` int(11) DEFAULT NULL,
`voyage_ports` int(11) DEFAULT NULL,
`voyage_deposit` int(2) NOT NULL DEFAULT 0,
`voyage_crewBerth` tinyint(2) NOT NULL,
`voyage_Afterguard` tinyint(2) NOT NULL,
`voyage_map` text DEFAULT NULL,
`voyage_mapZoom` tinyint(4) NOT NULL DEFAULT 8,
`voyage_addressName` varchar(150) NOT NULL,
`voyage_streetAddress` varchar(150) NOT NULL,
`voyage_locality` varchar(150) NOT NULL,
`voyage_postalCode` varchar(150) NOT NULL,
`voyage_region` varchar(150) NOT NULL,
`voyage_country` varchar(150) NOT NULL,
`voyage_gallery` text DEFAULT NULL,
`voyage_deleted` tinyint(4) NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

和

CREATE TABLE `bookings` (
`booking_id` int(11) NOT NULL,
`booking_status` tinyint(4) NOT NULL DEFAULT 0,
`booking_reference` varchar(60) NOT NULL,
`booking_stripeCustomerReference` varchar(150) NOT NULL,
`booking_stripeDepositInvoice` varchar(150) DEFAULT NULL,
`booking_stripeBalanceInvoice` varchar(150) DEFAULT NULL,
`booking_depositCharged` decimal(10,2) NOT NULL DEFAULT 0.00,
`booking_balanceCharged` decimal(10,2) NOT NULL DEFAULT 0.00,
`booking_totalPaid` decimal(10,2) NOT NULL DEFAULT 0.00,
`booking_voyageID` int(11) NOT NULL,
`booking_firstName` varchar(60) NOT NULL,
`booking_lastName` varchar(60) NOT NULL,
`booking_dob` date NOT NULL,
`booking_gender` varchar(15) NOT NULL,
`booking_nationality` varchar(60) NOT NULL,
`booking_passport` varchar(25) DEFAULT NULL,
`booking_email` varchar(150) NOT NULL,
`booking_mobile` varchar(60) DEFAULT NULL,
`booking_house` varchar(150) NOT NULL,
`booking_street` varchar(150) NOT NULL,
`booking_city` varchar(60) NOT NULL,
`booking_county` varchar(60) DEFAULT NULL,
`booking_postcode` varchar(20) NOT NULL,
`booking_medical` text NOT NULL,
`booking_allergies` text NOT NULL,
`booking_swim` tinyint(4) NOT NULL,
`booking_diet` tinyint(4) NOT NULL,
`booking_emergFirstName` varchar(60) NOT NULL,
`booking_emergLastName` varchar(60) NOT NULL,
`booking_emergHouse` varchar(150) NOT NULL,
`booking_emergStreet` varchar(150) NOT NULL,
`booking_emergCity` varchar(60) NOT NULL,
`booking_emergCounty` varchar(60) DEFAULT NULL,
`booking_emergPostCode` varchar(20) NOT NULL,
`booking_emergMobile` varchar(60) NOT NULL,
`booking_emergPhone` varchar(60) DEFAULT NULL,
`booking_emergRelationship` varchar(150) NOT NULL,
`booking_dec1` tinyint(4) NOT NULL,
`booking_dec2` tinyint(4) NOT NULL,
`booking_dec3` tinyint(4) NOT NULL,
`booking_dec4` tinyint(4) NOT NULL,
`booking_media1` tinyint(4) DEFAULT 0,
`booking_media2` int(11) DEFAULT 0,
`booking_contractEmail` varchar(150) NOT NULL,
`booking_contractName` varchar(60) NOT NULL,
`booking_contractDate` date NOT NULL,
`booking_adminNotes` text DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

我正在尝试根据每个航程计算有多少预订，如果没有则返回 0。

例如：

Voyage 1 = 0, 
Voyage 2 = 3, 
Voyage 3 = 5

等等。

在他们那一刻，我有以下内容，但它似乎不起作用。我目前在 bookings 表中只有一行测试数据，在 voyage 表中只有 17 个航次。

SELECT voyage_name, voyage_id, bookings.booking_voyageID, 
COUNT(bookings.booking_voyageID) AS bookingcount
FROM voyages
LEFT JOIN bookings ON voyages.voyage_id = bookings.booking_voyageID
ORDER BY voyage_name asc

如果没有预订，我需要我的 SQL 查询返回计数 0。

标签： mysqlsql

解决方案

我认为计数不足以解决您的问题，因为如果航程有一行，它将返回 1 而不会返回 0。

SUM是您想要的自定义列。

尝试这个

SELECT 
  voyage_id, 
  bookings.booking_voyageID,
  SUM(CASE WHEN bookings.booking_voyageID IS NULL THEN 0 ELSE 1 END) as 
bookingcount
FROM 
  voyages
LEFT JOIN 
  bookings 
ON 
  voyages.voyage_id = bookings.booking_voyageID
GROUP BY
  voyage_id
ORDER BY
  voyage_id;

SQL 小提琴

推荐阅读