56,681
社区成员
发帖
与我相关
我的任务
分享
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 | |
| is_citynews | tinyint(1) unsigned | NO | | 0 | |
| pro_id | smallint(5) unsigned | NO | | 0 | |
| city_id | smallint(5) unsigned | NO | | 0 | |
+-------------+----------------------+------+-----+---------+----------------+
5 rows in set (0.02 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 | |
| date2 | 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 limit 0,10;
+---------+-----------+--------+
| city_id | city_name | pro_id |
+---------+-----------+--------+
| 1 | 北京 | 1 |
| 2 | 上海 | 2 |
| 3 | 天津 | 3 |
| 4 | 重庆 | 4 |
| 5 | 合肥 | 5 |
| 6 | 黄山 | 5 |
| 7 | 芜湖 | 5 |
| 8 | 马鞍山 | 5 |
| 9 | 安庆 | 5 |
| 10 | 淮南 | 5 |
+---------+-----------+--------+
10 rows in set (0.00 sec)
mysql> select * from new limit 0,10;
+--------+----------+-------------+--------+---------+
| new_id | new_name | is_citynews | pro_id | city_id |
+--------+----------+-------------+--------+---------+
| 21 | 张庭人 | 0 | 0 | 0 |
| 22 | 美国C | 0 | 0 | 0 |
| 24 | 伏明霞 | 0 | 0 | 0 |
| 25 | 杰克逊 | 0 | 0 | 0 |
| 26 | 全职妈 | 1 | 1 | 1 |
| 27 | 产妇拒 | 1 | 24 | 136 |
| 28 | 北京一 | 1 | 1 | 1 |
| 29 | 女子告 | 1 | 1 | 1 |
| 30 | 河南周 | 1 | 13 | 70 |
| 32 | 我国将 | 1 | 0 | 0 |
+--------+----------+-------------+--------+---------+
10 rows in set (0.00 sec)
mysql> select * from news limit 0,10;
+---------+-----------+------------+---------+------------+
| news_id | news_name | news_time | city_id | date2 |
+---------+-----------+------------+---------+------------+
| 1 | 北京现 | 1247512449 | 1 | 1247512449 |
| 3 | 近20 | 1247512535 | 1 | 1247512535 |
| 4 | 瑞士国 | 1247512601 | 1 | 1247512601 |
| 5 | 日本原 | 1247512639 | 1 | 1247512639 |
| 6 | 北京举 | 1247512770 | 1 | 1247512770 |
| 7 | 200 | 1247512888 | 1 | 1247512888 |
| 8 | 珠海出 | 1249404191 | 35 | 1249404191 |
| 9 | 红九九 | 1249404398 | 4 | 1249404398 |
| 10 | 北外奥 | 1249404542 | 4 | 1249404542 |
| 11 | 谨防手 | 1249404652 | 61 | 1249404652 |
+---------+-----------+------------+---------+------------+
10 rows in set (0.00 sec)
mysql> select * from pro limit 0 ,10;
+--------+----------+
| pro-id | pro-name |
+--------+----------+
| 1 | 北京 |
| 2 | 上海 |
| 3 | 天津 |
| 4 | 重庆 |
| 5 | 安徽 |
| 6 | 福建 |
| 7 | 甘肃 |
| 8 | 广东 |
| 9 | 广西 |
| 10 | 贵州 |
+--------+----------+
10 rows in set (0.00 sec)
(
SELECT
n.news_id AS nid,
n.news_name AS ntitle,
FROM_UNIXTIME( n.news_time ) AS ndate,
'news' AS tb,
n.city_id AS cid,
p.`pro-id` AS pid
FROM news n
LEFT JOIN city c ON n.city_id = c.city_id
LEFT JOIN pro p ON c.pro_id = p.`pro-id`
WHERE 1=1 AND p.`pro-id`='3' AND c.`city_id`='3' AND news_id!='246'
HAVING (pid=0 AND cid=0) OR 1=2 OR (pid='3' AND cid=0) OR cid='3'
)
UNION ALL
(
SELECT n.new_id AS nid,
n.new_name AS ntitle,
n.new_time AS ndate,
'new' AS tb,
n.city_id AS cid,
p.`pro-id` AS pid
FROM new n
LEFT JOIN city c ON n.city_id = c.city_id
LEFT JOIN pro p ON c.pro_id = p.`pro-id`
WHERE 1=1 AND n.is_citynews=1
HAVING (pid=0 AND cid=0) OR 1=2 OR (pid='3' AND cid=0) OR cid='3'
)
ORDER BY ndate DESC
LIMIT 0 , 10;
mysql> (
-> SELECT
-> n.news_id AS nid,
-> n.news_name AS ntitle,
-> FROM_UNIXTIME(n.news_time) AS ndate,
-> 'news' AS tb,
-> p.`pro-name` ,
-> c.`city_name`
-> FROM news n
-> LEFT JOIN city c ON n.city_id = c.city_id
-> LEFT JOIN pro p ON c.pro_id = p.`pro-id`
-> WHERE 1=1 AND ((p.`pro-id`=0 AND c.city_id=0) OR (p.`pro-id`='24' AND c.
city_id=0) OR (p.`pro-id`='24' AND c.city_id='136')) AND n.news_id!='535'
-> )
-> UNION ALL
-> (
-> SELECT n.new_id AS nid,
-> n.new_name AS ntitle,
-> n.new_time AS ndate,
-> 'new' AS tb,
-> p.`pro-name`,
-> c.`city_name`
-> FROM new n
-> LEFT JOIN city c ON n.city_id = c.city_id
-> LEFT JOIN pro p ON c.pro_id = p.`pro-id`
-> WHERE 1=1 AND n.is_citynews=1 AND ((p.`pro-id`=0 AND c.city_id=0) OR (p.
`pro-id`='24' AND c.city_id=0) OR (p.`pro-id`='24' AND c.city_id='136'))
-> )
-> ORDER BY ndate DESC
-> LIMIT 0 , 15;
+-----+--------+---------------------+------+----------+-----------+
| nid | ntitle | ndate | tb | pro-name | city_name |
+-----+--------+---------------------+------+----------+-----------+
| 61 | 当心: | 2009-08-05 01:16:13 | news | 山东 | 青岛 |
| 60 | SIM | 2009-08-05 01:15:46 | news | 山东 | 青岛 |
| 59 | 多门公 | 2009-08-05 01:13:14 | news | 山东 | 青岛 |
| 58 | 暑期去 | 2009-08-05 01:12:42 | news | 山东 | 青岛 |
| 57 | “家有 | 2009-08-05 01:12:05 | news | 山东 | 青岛 |
| 56 | 玛丽亲 | 2009-08-05 01:11:41 | news | 山东 | 青岛 |
| 27 | 产妇拒 | 2009-07-07 01:45:25 | new | 山东 | 青岛 |
+-----+--------+---------------------+------+----------+-----------+
7 rows in set (0.00 sec)
(
SELECT
n.news_id AS nid,
n.news_name AS ntitle,
n.news_content AS ncnt,
n.hit AS hit,
FROM_UNIXTIME( n.news_time ) AS ndate,
'news' AS tb,
p.`pro-name` ,
c.`city_name`
FROM news n
LEFT JOIN city c ON n.city_id = c.city_id
LEFT JOIN pro p ON c.pro_id = p.`pro-id`
WHERE 1=1 AND ((p.`pro-id`=0 AND c.city_id=0) OR (p.`pro-id`='24' AND c.city_id=0) OR (p.`pro-id`='24' AND c.city_id='136')) AND n.news_id!='535'
)
UNION ALL
(
SELECT n.new_id AS nid,
n.new_name AS ntitle,
n.new_content AS ncnt,
n.new_bit AS hit,
n.new_time AS ndate,
'new' AS tb,
p.`pro-name` ,
c.`city_name`
FROM new n
LEFT JOIN city c ON n.city_id = c.city_id
LEFT JOIN pro p ON c.pro_id = p.`pro-id`
WHERE 1=1 AND n.is_citynews=1 AND ((p.`pro-id`=0 AND c.city_id=0) OR (p.`pro-id`='24' AND c.city_id=0) OR (p.`pro-id`='24' AND c.city_id='136'))
)
ORDER BY ndate DESC
LIMIT 0 , 10;