query mysql grupo by and having

Question

我正在尝试创建一个查询来获取所有日期。

我需要这个返回:

商业1 - 3商业广告2 - 4商业广告5 - 2…

但是我需要从当前日期得到第三个日期。我正在播放这个:

select count(id) 
from cita 
group by id_comercial
having date('created_at') = CURDATE()

这个返回错误，如果我删除having子句，返回所有数据从我的表我需要，但我需要按当前日期筛选。

之后我需要转换这个查询在雄辩…但首先我必须在mysql中做并显示结果。

我的表是:

CREATE TABLE `cita` (
`id` int(10) UNSIGNED NOT NULL,
`nomape` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
`direccion` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
`provincia` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
`ciudad` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
`cp` char(5) COLLATE utf8mb4_unicode_ci NOT NULL,
`telefono` varchar(12) COLLATE utf8mb4_unicode_ci NOT NULL,
`movil` varchar(12) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`id_comercial` int(10) UNSIGNED DEFAULT NULL,
`id_comercial2` int(10) UNSIGNED DEFAULT NULL,
`id_llamada` int(10) UNSIGNED NOT NULL,
`id_estado` int(10) UNSIGNED NOT NULL,
`created_at` timestamp NULL DEFAULT NULL,
`updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO `cita` (`id`, `nomape`, `direccion`, `provincia`, `ciudad`, `cp`, `telefono`, `movil`, `id_comercial`, `id_comercial2`, `id_llamada`, `id_estado`, `created_at`, `updated_at`) VALUES
(1, 'ASENJO MARTIN JOSE RAFAEL', 'AV DE LA VEGA, 188', 'GRANADA', 'BELICENA', '18101', '958431322', NULL, 13, NULL, 617, 8, '2020-08-11 07:53:30', '2021-08-25 13:01:21'),
(2, 'MARTINEZ JIMENEZ MARIA LUISA', 'AVDA. ANDALUCIA, 3 BAJO F', 'JAÉN', 'TORREPEROGIL', '23320', '953776094', NULL, 44, NULL, 318, 8, '2020-08-11 09:20:55', '2021-08-24 15:41:03'),
(3, 'MARIA VIUDA DE SALVADOR LARA HURTADO', 'CALLE SANTA MARIA, 69', 'JAÉN', 'TORREPEROGIL', '23320', '953776876', NULL, 40, NULL, 578, 8, '2020-08-11 09:25:33', '2021-08-24 15:41:03'),
(4, 'MARTINEZ RUIZ EMILIO', 'CALLE OBISPO BASULTO, 97', 'JAÉN', 'TORREPEROGIL', '23320', '953776072', NULL, 42, NULL, 403, 8, '2020-08-11 09:29:02', '2021-08-24 15:41:03'),
(5, 'PATON REDONDO FERNANDO', 'CALLE DOCTOR FLEMING, 24 SIN LETRA', 'JAÉN', 'TORREPEROGIL', '23320', '953778119', NULL, 43, NULL, 438, 8, '2020-08-11 09:32:16', '2021-08-24 15:41:03'),
(6, 'VILLAR CRESPO JOSE', 'CALLE CERVANTES, 6 CASA', 'JAÉN', 'TORREPEROGIL', '23320', '953776353', NULL, 37, NULL, 769, 8, '2020-08-11 09:37:24', '2021-08-24 15:41:03'),
(7, 'MEDINA GALLEGO CONCEPCION', 'TRVA OBISPO BASULTO, 16', 'JAÉN', 'TORREPEROGIL', '23320', '953776523', NULL, 42, NULL, 404, 8, '2020-08-11 09:46:53', '2021-08-24 15:41:03'),
(8, 'BLANCO ANGUIS MARIA', 'CALLE MURILLO, 18 CASA', 'JAÉN', 'TORREPEROGIL', '23320', '953777328', NULL, 44, NULL, 526, 8, '2020-08-11 09:59:39', '2021-08-24 15:41:03'),
(9, 'MEZGUA MARTINEZ MATILDE', 'B. P. IGLESIAS, 4   2 D', 'JAÉN', 'TORREPEROGIL', '23320', '953778009', NULL, 37, NULL, 852, 8, '2020-08-11 10:00:52', '2021-08-24 15:41:04'),
(10, 'MARQUEZ MUÑOZ ANTONIO BLAS,ROSARIO', 'CALLE REAL DE BELICENA, 9 CASA', 'GRANADA', 'BELICENA', '18101', '958432077', NULL, 35, NULL, 952, 8, '2020-08-11 10:11:16', '2021-08-24 15:41:04'),
(11, 'BLAZQUEZ DELGADO ANTONIO Y EVA', 'CALLE JUAN MANUEL SERRAT, 9 CASA', 'GRANADA', 'BELICENA', '18101', '958431493', NULL, 32, NULL, 692, 2, '2020-08-11 10:13:31', '2020-08-11 13:10:49'),
(12, 'ALANIS GARCIA ANTONIO', 'CALLE RAMÓN Y CAJAL NUMERO 1', 'GRANADA', 'BELICENA', '18101', '958432049', NULL, 36, NULL, 723, 4, '2020-08-11 10:16:47', '2020-08-11 10:33:23'),

谢谢你的帮助，很抱歉我的英语不好，如果我的问题很糟糕

Answer 1

having子句在聚合后执行。而且您只有一列——计数，而不是创建的。

你想要一个where条款:

select count(id) 
from cita 
where date(created_at) = CURDATE()
group by id_comercial;

还请注意，我删除了单引号。我猜这是你写问题时的抄写错误。

同样，where子句可以更有效地写成:

where created_at >= CURDATE() and
created_at < CURDATE() + interval 1 day

这有助于优化器，因为在列上没有函数调用。