解决方案 »
- 请高手解决一个关于MYSQL CLUSTER的问题!急!!!!
- 插入mysql字符出错
- coreseek 无法搜引中文
- 分组查询,讲结果按分组的id拼接起来
- 根据另外一张表新增数据的问题
- 如何导出数据库的表结构,视图,存储过程,自定义方法
- xampp 关于异常显示mysql未激活的问题
- 如何在VB中连接MySQL数据库?
- 求助!windows系统下监控mysql内存
- 表存在,表数据文件也存在,报ERROR 1146 (42S02): Table 'xxx.xxx' doesn't exist
- Can't send long data for non-string/non-binary data types (parameter: 0)
- select for update是写在START TRANSACTION前面还是后面?
inner join `customer` b on a.customer_id=b.idwhere exists(select 1 from order where a.customer_id=customer_id and service_id=2 and curdate(order_time) between '2014-9-1' and '2014-9-30')
and
exists(select 1 from order where a.customer_id=customer_id and service_id=3 and curdate(order_time) between '2014-9-1' and '2014-9-30')
and curdate(order_time) between '2014-9-1' and '2014-9-30'可以把'2014-9-1' and '2014-9-30' service_id=2 修改成变量 service_id=@service_id
是2和3啊那用
select distinct customer.name,count(*) as num from `order` join customer on customer.id = order.customer_id where order.service_id in (2,3) and order.order_time between "20140901" and "20140930" group by order.service_id having num=2 ;
from customer c
where exists (select 1 from order where order_time between '2014-09-01' and '2014-09-30 23:59:59' and customer_id=c.id and service_id=2)
and exists (select 1 from order where order_time between '2014-09-01' and '2014-09-30 23:59:59' and customer_id=c.id and service_id=3)
and exists (select 1 from order where order_time between '2014-09-01' and '2014-09-30 23:59:59' and customer_id=c.id and service_id=5)
...
and exists (select 1 from order where order_time between '2014-09-01' and '2014-09-30 23:59:59' and customer_id=c.id and service_id=9)
是2和3啊那用
select distinct customer.name,count(*) as num from `order` join customer on customer.id = order.customer_id where order.service_id in (2,3) and order.order_time between "20140901" and "20140930" group by order.service_id having num=2 ;
谢谢你帮我解决问题,但请问为什么我在数据库里面运行没有正确结果,你运行可以吗?分析数据库正确结果应该是赵一和李四啊,不应该有孙二,因为孙二的其中一次时间在10月。而且我要求是既购买过服务2,又买过服务3,是与关系,而不是或关系
-> INNER JOIN `customer` b ON a.customer_id=b.id
-> WHERE EXISTS(SELECT 1 FROM `ORDER` WHERE a.customer_id=customer_id AND
service_id=2 AND DATE(order_time) BETWEEN '2014-9-1' AND '2014-9-30')
-> AND
-> EXISTS(SELECT 1 FROM `ORDER` WHERE a.customer_id=customer_id AND serv
ice_id=3 AND DATE(order_time) BETWEEN '2014-9-1' AND '2014-9-30')
-> AND DATE(a.order_time) BETWEEN '2014-9-1' AND '2014-9-30';
+----------+-------------+------------+---------------------+----+------+
| order_id | customer_id | service_id | order_time | id | name |
+----------+-------------+------------+---------------------+----+------+
| 1 | 1 | 2 | 2014-09-11 00:00:00 | 1 | 赵一 |
| 3 | 4 | 3 | 2014-09-24 00:00:00 | 4 | 李四 |
| 5 | 4 | 2 | 2014-09-25 00:00:00 | 4 | 李四 |
| 6 | 1 | 3 | 2014-09-12 00:00:00 | 1 | 赵一 |
+----------+-------------+------------+---------------------+----+------+
4 rows in set (0.03 sec)mysql>
哥你这个离答案已经很近了,我发现一个问题:要求必须是在1日到30日之间,既用过服务2又用过服务3的人,单只用其中一个的人、或者多用别的服务的人,都不能出现在结果中。你这个答案,如果把order中的数据多插入几条,比如换成下面这样,那结果就会出现不符合要求的记录:INSERT INTO `order` (`order_id`, `customer_id`, `service_id`, `order_time`) VALUES
(1, 1, 2, '2014-09-11 00:00:00'),
(2, 2, 4, '2014-09-13 00:00:00'),
(3, 4, 3, '2014-09-24 00:00:00'),
(4, 2, 3, '2014-09-17 00:00:00'),
(5, 4, 2, '2014-09-25 00:00:00'),
(6, 1, 3, '2014-09-12 00:00:00'),
(7, 2, 2, '2014-10-02 11:27:20'),
(8, 3, 2, '2014-09-18 14:32:21'),
(9, 3, 4, '2014-09-15 14:36:34'),
(10, 4, 5, '2014-09-08 14:41:09'),
(11, 2, 1, '2014-09-11 14:41:47'),
(12, 1, 1, '2014-09-04 14:42:25');
INNER JOIN `customer` b ON a.customer_id=b.id
WHERE EXISTS(SELECT 1 FROM `ORDER` WHERE a.customer_id=customer_id AND
service_id=2 AND DATE(order_time) BETWEEN '2014-9-1' AND '2014-9-30')
AND
EXISTS(SELECT 1 FROM `ORDER` WHERE a.customer_id=customer_id AND service_id=3 AND DATE(order_time) BETWEEN '2014-9-1' AND '2014-9-30')
AND NOT EXISTS(SELECT 1 FROM `ORDER` WHERE a.customer_id=customer_id AND service_id NOT IN(2,3) AND DATE(order_time) BETWEEN '2014-9-1' AND '2014-9-30')
AND DATE(a.order_time) BETWEEN '2014-9-1' AND '2014-9-30';
谢谢,太谢谢了,就是这个样子.csdn果然是个好地方
SELECT *
FROM customer c
INNER JOIN `order` o
ON id = customer_id
AND order_time between '2014-09-01' and '2014-09-30 23:59:59'
AND FIND_IN_SET(service_id,'2,3')