56,677
社区成员
发帖
与我相关
我的任务
分享
单个城市中有重复的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);
mysql> set @cityID=6;
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>
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>
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)
-- phpMyAdmin SQL Dump
-- version 2.10.2
-- http://www.phpmyadmin.net
--
-- 主机: localhost
-- 生成日期: 2009 年 11 月 28 日 07:10
-- 服务器版本: 5.0.45
-- PHP 版本: 5.2.3
SET 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, '江苏');
mysql> show tables;
+-----------------+
| 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)
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)