1.is_citynews=1 AND pro_id=0 AND city_id=0 //放到全国所有的城市里面,当然深圳肯定是要出现 2.is_citynews=1 AND pro_id=3 AND city_id=0 //放到全广东省的所有的城市,深圳肯定也是要出现的 3.is_citynews=1 AND pro_id=3 AND city_id=4 //只放到深圳里面,其他的任何地方都不出现。 这个表new设计的时候是这样设计的。
mysql> set @cityID=4; Query OK, 0 rows affected (0.00 sec)mysql> select new_id AS nid, -> new_name AS ntitle, -> new_time AS ndate -> ,'new' as tb -> from new -> where is_citynews=1 -> and -> ( -> pro_id=0 -> or -> city_id=@cityID -> or( -> pro_id=(select pro_id from city where city_id=@cityID) -> and -> city_id=0 -> ) -> ) -> union all -> select news_id,news_name,news_time,'news' as newTbl -> from news -> where city_id=@cityID; +-----+--------------------+---------------------+------+ | nid | ntitle | ndate | tb | +-----+--------------------+---------------------+------+ | 3 | 阿斯顿王二按时打发 | 2009-11-28 14:35:48 | new | | 4 | 按时打发地方则需 | 2009-11-28 14:36:31 | new | | 2 | 阿尔按时打发王二 | 1259390258 | news | | 6 | 暗色调广发士大夫 | 1259391127 | news | +-----+--------------------+---------------------+------+ 4 rows in set (0.00 sec)mysql>
那个帖子的10楼的方法中的第三句是可行的(但是需要指定flash_id),但是另外的两句不太准确。 10楼的方法:单个城市中有重复的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);
+-----------------+
| Tables_in_test2 |
+-----------------+
| city |
| new |
| news |
| pro |
+-----------------+
4 rows in set (0.00 sec)mysql> desc city;
+-----------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+----------------+
| city_id | int(11) | NO | PRI | NULL | auto_increment |
| city_name | varchar(255) | NO | | | |
| pro_id | int(11) | NO | | | |
+-----------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)mysql> desc new;
+-------------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------------------+------+-----+---------+----------------+
| new_id | int(11) | NO | PRI | NULL | auto_increment |
| new_name | varchar(255) | YES | | NULL | |
| new_bit | int(11) | YES | | 1 | |
| new_time | datetime | YES | | NULL | |
| is_citynews | tinyint(1) unsigned | NO | | 0 | |
| pro_id | smallint(5) unsigned | NO | | 0 | |
| city_id | smallint(5) unsigned | NO | | 0 | |
+-------------+----------------------+------+-----+---------+----------------+
7 rows in set (0.01 sec)mysql> desc news;
+-----------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+------------------+------+-----+---------+----------------+
| news_id | int(11) | NO | PRI | NULL | auto_increment |
| news_name | varchar(255) | YES | | NULL | |
| news_time | int(10) unsigned | NO | | 0 | |
| city_id | int(11) | YES | | NULL | |
| hit | int(10) unsigned | NO | | 0 | |
+-----------+------------------+------+-----+---------+----------------+
5 rows in set (0.02 sec)mysql> desc pro;
+----------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+----------------+
| pro-id | int(11) | NO | PRI | NULL | auto_increment |
| pro-name | varchar(255) | NO | | | |
+----------+--------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
mysql> select * from city;
+---------+-----------+--------+
| city_id | city_name | pro_id |
+---------+-----------+--------+
| 1 | 北京 | 1 |
| 2 | 上海 | 2 |
| 3 | 广州 | 3 |
| 4 | 深圳 | 3 |
| 5 | 南京 | 4 |
| 6 | 连云港 | 4 |
+---------+-----------+--------+
6 rows in set (0.00 sec)
mysql> select * from new;
+--------+------------------------+---------+---------------------+-------------+--------+---------+
| new_id | new_name | new_bit | new_time | is_citynews | pro_id | city_id |
+--------+------------------------+---------+---------------------+-------------+--------+---------+
| 1 | 按时大法师发 | 1 | 2009-11-28 14:34:52 | 0 | 0 | 0 | //不放到list.php列表中
| 2 | 按时认为人23按时大法师 | 1 | 2009-11-28 14:35:48 | 1 | 0 | 0 | //放到全国,任何城市都出现
| 3 | 阿斯顿王二按时打发 | 1 | 2009-11-28 14:35:48 | 1 | 3 | 0 | //放到省份ID=3的所有的城市的里面
| 4 | 按时打发地方则需 | 1 | 2009-11-28 14:36:31 | 1 | 3 | 4 | //只放到城市ID为4的城市里面,即当list.php?cityID=4才显示
| 5 | 按时大法师发 | 1 | 2009-11-28 14:34:52 | 0 | 0 | 0 | //不放到list.php中
| 6 | 按时认为人23按时大法师 | 1 | 2009-11-28 14:35:48 | 1 | 0 | 0 | //放到全国,任何城市都出现
| 7 | 阿斯顿王二按时打发 | 1 | 2009-11-28 14:35:48 | 1 | 4 | 0 | //放到省份ID=4的城市的列表页里面
| 8 | 按时打发地方则需 | 1 | 2009-11-28 14:36:31 | 1 | 4 | 6 | //只放到城市ID=6的城市列表页面里面
+--------+------------------------+---------+---------------------+-------------+--------+---------+
8 rows in set (0.00 sec)mysql> select * from news;
+---------+----------------------+------------+---------+-----+
| news_id | news_name | news_time | city_id | hit |
+---------+----------------------+------------+---------+-----+
| 1 | 按时打发王二在 | 1259390258 | 6 | 0 | //只在list.php?cityID=6中显示
| 2 | 阿尔按时打发王二 | 1259390258 | 4 | 0 | //只在list.php?cityID=4中显示
| 3 | seysdfg | 1259391105 | 1 | 0 | //只在list.php?cityID=1中显示
| 4 | 阿斯顿他噶山东省地 | 1259391105 | 2 | 0 | //只在list.php?cityID=2中显示
| 5 | NULL | 1259391127 | 3 | 0 | //只在list.php?cityID=3中显示
| 6 | 暗色调广发士大夫 | 1259391127 | 4 | 0 | //只在list.php?cityID=4中显示
+---------+----------------------+------------+---------+-----+
6 rows in set (0.00 sec)
mysql> select * from pro;
+--------+----------+
| pro-id | pro-name |
+--------+----------+
| 1 | 北京 |
| 2 | 上海 |
| 3 | 广东 |
| 4 | 江苏 |
+--------+----------+
4 rows in set (0.02 sec)
当网址为list.php?cityID=4的时候,以上两个新闻表中的应该出现的新闻是如下的这些:
mysql> (
-> select new_id AS nid,
-> new_name AS ntitle,
-> new_time AS ndate,
-> 'new' AS tb
-> FROM new
-> where new_id IN(2,3,4,6)
-> )UNION ALL(
-> SELECT news_id AS nid,
-> news_name AS ntitle,
-> FROM_UNIXTIME(news_time) AS ndate,
-> 'news' AS tb
-> FROM news
-> WHERE city_id=4
-> )
-> ORDER BY ndate DESC
-> LIMIT 0 ,30;
+-----+------------------------+---------------------+------+
| nid | ntitle | ndate | tb |
+-----+------------------------+---------------------+------+
| 6 | 暗色调广发士大夫 | 2009-11-28 14:52:07 | news |
| 2 | 阿尔按时打发王二 | 2009-11-28 14:37:38 | news |
| 4 | 按时打发地方则需 | 2009-11-28 14:36:31 | new |
| 3 | 阿斯顿王二按时打发 | 2009-11-28 14:35:48 | new |
| 6 | 按时认为人23按时大法师 | 2009-11-28 14:35:48 | new |
| 2 | 按时认为人23按时大法师 | 2009-11-28 14:35:48 | new |
+-----+------------------------+---------------------+------+
6 rows in set (0.00 sec)
-- phpMyAdmin SQL Dump
-- version 2.10.2
-- http://www.phpmyadmin.net
--
-- 主机: localhost
-- 生成日期: 2009 年 11 月 28 日 07:10
-- 服务器版本: 5.0.45
-- PHP 版本: 5.2.3SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";--
-- 数据库: `test2`
-- -- ----------------------------------------------------------
-- 表的结构 `city`
-- CREATE TABLE `city` (
`city_id` int(11) NOT NULL auto_increment COMMENT '城市编号',
`city_name` varchar(255) NOT NULL COMMENT '城市名称',
`pro_id` int(11) NOT NULL COMMENT '所属省份编号',
PRIMARY KEY (`city_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=7 ;--
-- 导出表中的数据 `city`
-- INSERT INTO `city` VALUES (1, '北京', 1);
INSERT INTO `city` VALUES (2, '上海', 2);
INSERT INTO `city` VALUES (3, '广州', 3);
INSERT INTO `city` VALUES (4, '深圳', 3);
INSERT INTO `city` VALUES (5, '南京', 4);
INSERT INTO `city` VALUES (6, '连云港', 4);-- ----------------------------------------------------------
-- 表的结构 `new`
-- CREATE TABLE `new` (
`new_id` int(11) NOT NULL auto_increment COMMENT '新闻编号',
`new_name` varchar(20) default NULL COMMENT '新闻标题',
`new_bit` int(3) default '1' COMMENT '新闻点击率',
`new_time` datetime default NULL COMMENT '新闻发布时间',
`is_citynews` tinyint(1) unsigned NOT NULL default '0' COMMENT '是否是城市动态新闻?0=>不是,1=>是,',
`pro_id` smallint(5) unsigned NOT NULL default '0',
`city_id` smallint(5) unsigned NOT NULL default '0',
PRIMARY KEY (`new_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='新闻信息数据表' AUTO_INCREMENT=9 ;--
-- 导出表中的数据 `new`
-- INSERT INTO `new` VALUES (1, '按时大法师发', 1, '2009-11-28 14:34:52', 0, 0, 0);
INSERT INTO `new` VALUES (2, '按时认为人23按时大法师', 1, '2009-11-28 14:35:48', 1, 0, 0);
INSERT INTO `new` VALUES (3, '阿斯顿王二按时打发', 1, '2009-11-28 14:35:48', 1, 3, 0);
INSERT INTO `new` VALUES (4, '按时打发地方则需', 1, '2009-11-28 14:36:31', 1, 3, 4);
INSERT INTO `new` VALUES (5, '按时大法师发', 1, '2009-11-28 14:34:52', 0, 0, 0);
INSERT INTO `new` VALUES (6, '按时认为人23按时大法师', 1, '2009-11-28 14:35:48', 1, 0, 0);
INSERT INTO `new` VALUES (7, '阿斯顿王二按时打发', 1, '2009-11-28 14:35:48', 1, 4, 0);
INSERT INTO `new` VALUES (8, '按时打发地方则需', 1, '2009-11-28 14:36:31', 1, 4, 6);-- ----------------------------------------------------------
-- 表的结构 `news`
-- CREATE TABLE `news` (
`news_id` int(11) NOT NULL auto_increment COMMENT '城市新闻编号',
`news_name` varchar(20) default NULL COMMENT '城市新闻名称',
`news_time` int(10) unsigned NOT NULL default '0' COMMENT '添加时间',
`city_id` int(11) default NULL COMMENT '城市新闻所对应城市编号',
`hit` int(10) unsigned NOT NULL default '0' COMMENT '点击率',
PRIMARY KEY (`news_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='城市动态新闻' AUTO_INCREMENT=7 ;--
-- 导出表中的数据 `news`
-- INSERT INTO `news` VALUES (1, '按时打发王二在', 1259390258, 6, 0);
INSERT INTO `news` VALUES (2, '阿尔按时打发王二', 1259390258, 4, 0);
INSERT INTO `news` VALUES (3, 'seysdfg', 1259391105, 1, 0);
INSERT INTO `news` VALUES (4, '阿斯顿他噶山东省地方', 1259391105, 2, 0);
INSERT INTO `news` VALUES (5, NULL, 1259391127, 3, 0);
INSERT INTO `news` VALUES (6, '暗色调广发士大夫', 1259391127, 4, 0);-- ----------------------------------------------------------
-- 表的结构 `pro`
-- CREATE TABLE `pro` (
`pro-id` int(11) NOT NULL auto_increment COMMENT '省的编号',
`pro-name` varchar(255) NOT NULL COMMENT '省的名称',
PRIMARY KEY (`pro-id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;--
-- 导出表中的数据 `pro`
-- INSERT INTO `pro` VALUES (1, '北京');
INSERT INTO `pro` VALUES (2, '上海');
INSERT INTO `pro` VALUES (3, '广东');
INSERT INTO `pro` VALUES (4, '江苏');
语句你不是已经有了吗? 到底什么问题?
并且提供的数据和你另一个贴子中并不一样了。建议提供你的 insert 语句。省得别人再定写一遍了。
本贴的第2,3楼的数据为准。本贴中的语句和另外那个帖子的语句都无法取出正确的结果。
mysql> select * from new;
+--------+------------------------+---------+---------------------+-------------+--------+---------+
| new_id | new_name | new_bit | new_time | is_citynews | pro_id | city_id |
+--------+------------------------+---------+---------------------+-------------+--------+---------+
| 1 | 按时大法师发 | 1 | 2009-11-28 14:34:52 | 0 | 0 | 0 | //不放到list.php列表中
| 2 | 按时认为人23按时大法师 | 1 | 2009-11-28 14:35:48 | 1 | 0 | 0 | //放到全国,任何城市都出现
| 3 | 阿斯顿王二按时打发 | 1 | 2009-11-28 14:35:48 | 1 | 3 | 0 | //放到省份ID=3的所有的城市的里面
| 4 | 按时打发地方则需 | 1 | 2009-11-28 14:36:31 | 1 | 3 | 4 | //只放到城市ID为4的城市里面,即当list.php?cityID=4才显示
| 5 | 按时大法师发 | 1 | 2009-11-28 14:34:52 | 0 | 0 | 0 | //不放到list.php中
| 6 | 按时认为人23按时大法师 | 1 | 2009-11-28 14:35:48 | 1 | 0 | 0 | //放到全国,任何城市都出现
| 7 | 阿斯顿王二按时打发 | 1 | 2009-11-28 14:35:48 | 1 | 4 | 0 | //放到省份ID=4的城市的列表页里面
| 8 | 按时打发地方则需 | 1 | 2009-11-28 14:36:31 | 1 | 4 | 6 | //只放到城市ID=6的城市列表页面里面
+--------+------------------------+---------+---------------------+-------------+--------+---------+
8 rows in set (0.00 sec)表new的含义就是这样的,
如何区分就是靠is_citynews,pro_id,city_id来区分放到哪里去的。表news中的取出的条件就是安装city_id来取的,因为list.php的必选参数是cityID,所以直接取表news的city_id=网址中的cityID的值的就可以,但是表new中的取的话要取出放到这个城市所在省份的所有的城市中新闻加放到全国的新闻,加放到这个城市的新闻.
例如城市cityID=4(深圳)的时候,通过PH查询P语句得到该城市的省份pro_id=3(广东),
然后表new中的新闻的符合条件是的如下三种情况,任意一个条件符合就可以的1.is_citynews=1 AND pro_id=0 AND city_id=0
2.is_citynews=1 AND pro_id=3 AND city_id=0
3.is_citynews=1 AND pro_id=3 AND city_id=4
2.is_citynews=1 AND pro_id=3 AND city_id=0 //放到全广东省的所有的城市,深圳肯定也是要出现的
3.is_citynews=1 AND pro_id=3 AND city_id=4 //只放到深圳里面,其他的任何地方都不出现。
这个表new设计的时候是这样设计的。
Query OK, 0 rows affected (0.00 sec)mysql> select new_id AS nid,
-> new_name AS ntitle,
-> new_time AS ndate
-> ,'new' as tb
-> from new
-> where is_citynews=1
-> and
-> (
-> pro_id=0
-> or
-> city_id=@cityID
-> or(
-> pro_id=(select pro_id from city where city_id=@cityID)
-> and
-> city_id=0
-> )
-> )
-> union all
-> select news_id,news_name,news_time,'news' as newTbl
-> from news
-> where city_id=@cityID;
+-----+--------------------+---------------------+------+
| nid | ntitle | ndate | tb |
+-----+--------------------+---------------------+------+
| 3 | 阿斯顿王二按时打发 | 2009-11-28 14:35:48 | new |
| 4 | 按时打发地方则需 | 2009-11-28 14:36:31 | new |
| 2 | 阿尔按时打发王二 | 1259390258 | news |
| 6 | 暗色调广发士大夫 | 1259391127 | news |
+-----+--------------------+---------------------+------+
4 rows in set (0.00 sec)mysql>
是需要用到的LIMIT 语句的。还有分页统计符合条件的记录数。当网址是,list.php?cityID=4&kw=关键字&page=26
Query OK, 0 rows affected (0.00 sec)mysql>
mysql> select *
-> from (
-> select new_id AS nid,
-> new_name AS ntitle,
-> new_time AS ndate
-> ,'new' as tb
-> from new
-> where is_citynews=1
-> and
-> (
-> pro_id=0
-> or
-> city_id=@cityID
-> or(
-> pro_id=(select pro_id from city where city_id=@cityID)
-> and
-> city_id=0
-> )
-> )
-> union all
-> select news_id,news_name,FROM_UNIXTIME(news_time) ,'news' as newTbl
-> from news
-> where city_id=@cityID
-> ) t
-> order by ndate desc
-> LIMIT 0 ,30;
+-----+--------------------+---------------------+------+
| nid | ntitle | ndate | tb |
+-----+--------------------+---------------------+------+
| 1 | 按时打发王二在 | 2009-11-28 14:37:38 | news |
| 8 | 按时打发地方则需 | 2009-11-28 14:36:31 | new |
| 7 | 阿斯顿王二按时打发 | 2009-11-28 14:35:48 | new |
+-----+--------------------+---------------------+------+
3 rows in set (0.00 sec)mysql>
10楼的方法:单个城市中有重复的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);