如果有一个表flash保存了所有的flash_id,那么:单个城市中有重复的flash_id: select * from city c where (length(flash_ids)-length(replace(flash_ids,',',''))+1)>(select count(1) from flash where find_in_set(flash_id,c.flash_ids));与某一城市有相同flash_id的其他城市: select distinct c.* from city c,flash f where find_in_set(f.flash_id,(select flash_ids from city where city_name='天津')) and find_in_set(f.flash_id,c.flash_ids) and c.city_name<>'天津';所有含某一flash_id的城市: select * from city c where find_in_set(30,flash_ids); 不过还是建议flash_ids分到另一个表中以city_id,flash_id一对一保存。
表设计不符合3NF,最好是
city_id | city_name | flash_ids
1 11
1 12否则要用临时表还拆分flash_ids ,形成上述格式的表,再做处理
只能用存储过程如果你的 flash_ids 中没有限制的话。可以通过存储过程来分析。 如果flash_ids中项目有限的话,也可以使用效率很低的SQL。这种设计连数据库范式的最起码一范式都不是。
表设计不符合3NF,最好是
city_id flash_ids
1 11
1 12
1 23
..............
建立city_name 对照表
city_id city_name
1 北京
2 上海
查询方便
你提供的测试数据中仅有一个符合。 可以用如下语句得到。
mysql> select *
-> from city a , city b
-> where a.city_id != b.city_id
-> and concat(',',a.flash_ids ,',') regexp concat(',(',replace(b.flash_ids,',','|'),'),');
+---------+-----------+----------------+---------+-----------+----------------+
| city_id | city_name | flash_ids | city_id | city_name | flash_ids |
+---------+-----------+----------------+---------+-----------+----------------+
| 4 | 重庆 | 26,27,28,29,30 | 3 | 天津 | 26,27,28,29,30 |
| 3 | 天津 | 26,27,28,29,30 | 4 | 重庆 | 26,27,28,29,30 |
+---------+-----------+----------------+---------+-----------+----------------+
2 rows in set (0.02 sec)mysql
你的表设计可以为两个表。create table city1 (
city_id primary key
city_name
);
create table city2 (
city_id
flash_ids primary key
);
比如北京的里面存的flash_ids 为12,25,13.
那flash的顺序便是:
第一张是id为12的,
第二张id为25,
第三张id为13.
而不能是12,13,25排序或者25,13,12这样排的。--------------------------------------------------------------------
现在主要就是看下能不能选出不同的城市里面存的flash_ids是否自身里面有重复的id,然后是否自身里面的flash_id和其他的城市的flash_id有重合的,主要就是看下能不能这样选出来。======================================================================如果建表city_flash_relation
CREATE TABLE `city_flash_relation` (
`relid` INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`city_id` INT( 10 ) UNSIGNED NOT NULL ,
`flash_id` INT( 10 ) UNSIGNED NOT NULL ,
`ordernum` INT( 10 ) UNSIGNED NOT NULL DEFAULT '0'
) ENGINE = MYISAM ;
mysql> desc city_flash_relation;
+----------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+----------------+
| relid | int(10) unsigned | NO | PRI | NULL | auto_increment |
| city_id | int(10) unsigned | NO | | | |
| flash_id | int(10) unsigned | NO | | | |
| ordernum | int(10) unsigned | NO | | 0 | |
+----------+------------------+------+-----+---------+----------------+
4 rows in set (0.02 sec)
假如建表的话,另外需要重新填顺序,因为flash比较多,所以不能这样了.
关键是排序的问题。另外的是每个城市5张flash,最少0张。
city2 2 12
2 13
2 25表中数据如上,这样,你可以在查询中使用 ordder by flash_ids 即可确保顺序。
select * from city c where (length(flash_ids)-length(replace(flash_ids,',',''))+1)>(select count(1) from flash where find_in_set(flash_id,c.flash_ids));与某一城市有相同flash_id的其他城市:
select distinct c.* from city c,flash f where find_in_set(f.flash_id,(select flash_ids from city where city_name='天津')) and find_in_set(f.flash_id,c.flash_ids) and c.city_name<>'天津';所有含某一flash_id的城市:
select * from city c where find_in_set(30,flash_ids);
不过还是建议flash_ids分到另一个表中以city_id,flash_id一对一保存。