mysql²éѯsqlÓï¾ä

mysqlÓбíA:

id  num
1    4
2    9
3    14
4    20
5    25


±íB:

id start end
1  0    5
2  6    10
3  11    15

ÔõôÄܲéѯµ½´æÔÚÓÚ±íBÖÐÈÎÒ»µÄstart×ֶκÍend×Ö¶ÎÖ®¼äµÄÊý?

±ÈÈç ±íAÖеÄ4,9,14¶¼ÊÇÔÚ±íbÁ½¸ö×Ö¶ÎÖ®¼äµÄÊý,¶ø20ºÍ25ÒòΪûÓÐÔÚ±íbÈÎÒ»×Ö¶ÎÖ®¼äËùÒÔÎÞ·¨²éѯ³öÀ´..

---

select a.* from a inner join b
on a.num between b.start and b.enf

---

SQL code:
mysql> select * from a;
+----+------+
| id | num |
+----+------+
| 1 | 4 |
| 2 | 9 |
| 3 | 14 |
| 4 | 20 |
| 5 | 25 |
+----+------+
5 rows in set (0.00 sec)

mysql> select * from b;
+----+-------+------+
| id | start | end |
+----+-------+------+
| 1 | 0 | 5 |
| 2 | 6 | 10 |
| 3 | 11 | 15 |
+----+-------+------+
3 rows in set (0.00 sec)

mysql> select a.id,num from a,b where a.id=b.id and a.num between b
-> .start and b.end;
+----+------+
| id | num |
+----+------+
| 1 | 4 |
| 2 | 9 |
| 3 | 14 |
+----+------+
3 rows in set (0.02 sec)

---

SQL code:
selec