解决方案 »
- 求个mysql自动备份的 批处理脚本,带删除的
- 如何使相同属性值的字段只在查询中最多出现两次?
- ?高手快来吧
- 在线求一个经典的数据库设计问题!一张表对多张表
- Access数据倒入mysql后数据乱码两种环境一个查询显示正常,一个查询现实都不正常解决方法
- mysql 修复方案
- 求SQL文!!!
- 求一条SQL执行语句,试了半天,还是不行!
- 求助啊,Mysql,在线等
- MySQL乱码问题
- 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')