我有下一张桌子:
CREATE TABLE sales
( id INT ( 10) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
event_name VARCHAR( 99),
cashier_name VARCHAR( 99),
sale_date VARCHAR( 99),
sale_time VARCHAR( 99)
) ENGINE=INNODB
和下一个数据(包括供某人测试的查询):
ID EVENT_NAME CASHIER_NAME SALE_DATE SALE_TIME
----------------------------------------------------------
1 Metallico 8pm Jenny 2022-03-11 15:37
2 Metallico 8pm Sandy 2022-03-11 16:01
3 Red Puppets 10am Jenny 2022-03-08 09:40
4 Red Puppets 10am Jenny 2022-03-11 08:55
5 Wild Cats 8pm Sandy 2022-02-25 19:13
6 Wild Cats 8pm Jenny 2022-02-25 16:59
INSERT INTO sales (id, event_name, cashier_name, sale_date, sale_time)
VALUES (1, 'Metallico 8pm', 'Jenny', '2022-03-11', '15:37'),
(2, 'Metallico 8pm', 'Sandy', '2022-03-11', '16:01'),
(3, 'Red Puppets 10am','Jenny', '2022-03-08', '09:40'),
(4, 'Red Puppets 10am','Jenny', '2022-03-11', '08:55'),
(5, 'Wild Cats 8pm', 'Sandy', '2022-02-25', '19:13'),
(6, 'Wild Cats 8pm', 'Jenny', '2022-02-25', '16:59')
正如标题所说,我需要得到JENNY,因为她在same天(1,4)中有different个活动的销售额,桑迪也有different个活动的销售额,但日期是different(2,5).
下一个查询提供了事件(3条记录),但我不确定如何包含条件with sales in the same day
:
SELECT *
FROM sales
GROUP BY event_name
HAVING COUNT(*) > 1
如果我更改GROUP BY
,我会得到Jenny和Sandy(2张唱片),但不确定如何排除Sandy:
SELECT *
FROM sales
GROUP BY sale_date
HAVING COUNT(*) > 1
(不,这不是homework美元,我在一家博物馆工作,里面有一个小剧场,用于 children 戏剧、音乐会和比赛,我们在收银员和资金方面遇到了"问题",我们正试图通过一些数据来解决这个问题.)