mysql的或逻辑怎么写_请问这个sql逻辑怎么写？

由于Mysql没有Oracle的connect by，我平时会常备一个‘序列表’，这个表用于存储1到数百的连续整数。以前建这个表seq时我插入了700多行，够你的例子啦(如果不够，比如你某一行的时间跨度大于700多天，增加seq的行数即可)。

mysql> desc seq;

+-------+---------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+-------+---------+------+-----+---------+----------------+

| id | int(6) | NO | PRI | NULL | auto_increment |

| r | char(1) | YES | | NULL | |

+-------+---------+------+-----+---------+----------------+

2 rows in set (0.03 sec)

mysql> select * from seq limit 10;

+----+------+

| id | r |

+----+------+

| 1 | 1 |

| 2 | 1 |

| 3 | 1 |

| 4 | 1 |

| 5 | 1 |

| 6 | 1 |

| 7 | 1 |

| 8 | 1 |

| 9 | 1 |

| 10 | 1 |

+----+------+

10 rows in set (0.02 sec)

mysql> select count(*) from seq;

+----------+

| count(*) |

+----------+

| 747 |

+----------+

1 row in set (0.02 sec)

-- 再生成你的表

mysql> desc p;

+-------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+-------------+------+-----+---------+-------+

| brand | varchar(20) | NO | | | |

| sd | date | YES | | NULL | |

| ed | date | YES | | NULL | |

+-------+-------------+------+-----+---------+-------+

3 rows in set (0.02 sec)

mysql> select * from p;

+-------+------------+------------+

| brand | sd | ed |

+-------+------------+------------+

| a | 2018-09-01 | 2018-09-05 |

| a | 2018-09-03 | 2018-09-06 |

| a | 2018-09-09 | 2018-09-15 |

| b | 2018-08-04 | 2018-08-15 |

| b | 2018-08-04 | 2018-08-05 |

| c | 2018-08-15 | 2018-08-21 |

| c | 2018-09-02 | 2018-09-12 |

+-------+------------+------------+

7 rows in set (0.02 sec)

-- 用你的表p左连接序列表seq，生成每个活动的不同日期。

mysql> select distinct brand,date_add(p.sd,interval s.id-1 day) dt from p

left join seq s on date_add(p.sd,interval s.id-1 day)>=p.sd

and date_add(p.sd,interval s.id-1 day)<=p.ed order by brand,dt;

+-------+------------+

| brand | dt |

+-------+------------+

| a | 2018-09-01 |

| a | 2018-09-02 |

| a | 2018-09-03 |

| a | 2018-09-04 |

| a | 2018-09-05 |

| a | 2018-09-06 |

| a | 2018-09-09 |

| a | 2018-09-10 |

| a | 2018-09-11 |

| a | 2018-09-12 |

| a | 2018-09-13 |

| a | 2018-09-14 |

| a | 2018-09-15 |

| b | 2018-08-04 |

| b | 2018-08-05 |

| b | 2018-08-06 |

| b | 2018-08-07 |

.......

| c | 2018-09-10 |

| c | 2018-09-11 |

| c | 2018-09-12 |

+-------+------------+

43 rows in set (0.02 sec)

-- 于是，每个brand的活动天数如下：

mysql> select brand,count(*) from (

select distinct brand,date_add(p.sd,interval s.id-1 day) dt from p

left join seq s on date_add(p.sd,interval s.id-1 day)>=p.sd

and date_add(p.sd,interval s.id-1 day)<=p.ed) t group by brand;

+-------+----------+

| brand | count(*) |

+-------+----------+

| a | 13 |

| b | 12 |

| c | 18 |

+-------+----------+

3 rows in set (0.02 sec)

注1 left join 也可以用普通join替换: select .... from p,seq where ....

注2 如果是Oracle，这个seq序列可以用connect by直接生成，就更方便啦。

-- 在oracle中，生成一个序列是一句话的事。

select level from dual connect by level<5;

1

2

3

4

-- 所以，上述查询中的seq不需要预先建，可以直接用sql替换

.... from p left join (select level id from dual connect by level<=100)seq on ......

-- 更近一步，select level from dual connect by level<100可以优化成：

select level from (select max(ed)-min(sd)+1 m from p) t connect by level<=m