card表:
card_id,card_type_id,card_code,tel_number_id
1,1,abc001,1
2,1,abc002,2
3,2,abc003,null
4,2,abc004,nullcard_type表:
card_type_id,card_type_name,special_flag
1,大众,0
2,156卡,1tel_number表:
tel_number_id,number
1,13312340001
2,13312340002
3,13312340003
4,13312340004查询语句:
select tn.number
from tel_number tn
left outer join card on tn.tel_number_id=card.tel_number_id
left outer join card_type ct on card.card_type_id=ct.card_type_id
where ct.special_flag=0
目的是取出tel_number表中对应卡的卡类型是0的记录,预期取出
13312340003
13312340004为什么取不出来呢?应该怎么取才对?要求主表是tel_number,而不是card
谢谢
card_id,card_type_id,card_code,tel_number_id
1,1,abc001,1
2,1,abc002,2
3,2,abc003,null
4,2,abc004,nullcard_type表:
card_type_id,card_type_name,special_flag
1,大众,0
2,156卡,1tel_number表:
tel_number_id,number
1,13312340001
2,13312340002
3,13312340003
4,13312340004查询语句:
select tn.number
from tel_number tn
left outer join card on tn.tel_number_id=card.tel_number_id
left outer join card_type ct on card.card_type_id=ct.card_type_id
where ct.special_flag=0
目的是取出tel_number表中对应卡的卡类型是0的记录,预期取出
13312340003
13312340004为什么取不出来呢?应该怎么取才对?要求主表是tel_number,而不是card
谢谢
select tn.number
from tel_number tn
left outer join card on tn.tel_number_id=card.tel_number_id
where card.tel_number_id is null
的话,并且分页取出200条数据,语句执行很慢,大概要5秒。tel_number表里才1万条数据而已。
tel_number as a left join (card_type as b, card as c)
on ( a.tel_number_id = c.tel_number_id and b.card_type_id = c.card_type_id and b.special_flag = 0)
where c.tel_number_id is null;
from tel_number tn
left outer join card on tn.tel_number_id=card.tel_number_id
where card.tel_number_id is null是一样的,还是需要5秒。
from tel_number tn
left outer join card on tn.tel_number_id=card.tel_number_id
where card.tel_number_id is not null这个语句找card.tel_number_id不是null的却用不上1秒。
13312340001
13312340002
好不好要以card为主表进行检索
select c.number from card a
left join card_type b
on a.card_type_id = b.card_type_id
left join tel_number c
left join a.tel_number_id = c.tel_number_id
where b.special_flag = 0
and not a.tel_number_id is NULL --可要可不要,根据需求而定
SELECT c.* from card_type a
left join card b
on a.card_type_id=b.card_type_id
left join tel_number c
on c.tel_number_id=b.tel_number_id
where special_flag=0
恩,应该是
13312340001
13312340002
但我还是要以Number为主表,要是以card为主表,我就不用发贴了。
我觉得你说的是对的。可是我不会对mysql表建索引。
在连接字段上建立索引
[index_type]
ON tbl_name (index_col_name,...)
[index_option ...]index_col_name:
col_name [(length)] [ASC | DESC]index_type:
USING {BTREE | HASH | RTREE}index_option:
KEY_BLOCK_SIZE value
| index_type
| WITH PARSER parser_namecreate index card_id on card