有没有简单点的方法，两个表UNION 后LEFT JOIN 其他的两个表，共有四个表

现在的情况是一个网页list.php。
里面的内容来之两个表：一个是news，一个是news。（当时设计数据库的人这样设计数据表名字，...），list.php网页里面有参数 proID,cityID,(proID表示省份的ID，cityID表示城市的ID)。网页list.php的必选参数是cityID,即为list.php?cityID=.----------------------------------------------------------------------
各个表的结构如下：（总共涉及四个表，表city，表pro，表new，表news）
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 | 美国Ｃ         |           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)=================================比如当前的网址list.php?cityID=1，那么这个网页里面列出来的内容来自于两个表的合并，
（其中表new中的用法是情况是四种情况
is_citynews proid cityid
1,0,0表示放到全国,所有的城市中都要放
0,*,* 表示不放到list.php页面中（其中的*表示任意的）
1,1,0表示放到省份ID为1的所有的城市页面中
1,1,1表示只放到省份ID为1并且城市ID为1的里面。
）
表news其中city_id是必填的，所以取出来的时候就根据网址来取=========================================================================
我用的SQL语句如下（以下的页面时list.php?cityID=3的时候的查询SQL）：（排序是按照新闻发布时间来排序的）
(
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; -------------------------------------
有没有比这个更好的方法啊。另外这个方法不知道取出来的是不是准确的。

解决方案 »

  1.   

    SQL语句我改了下，但是还是不行：(
    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;
      

  2.   

    哪位大哥帮我一下，在线等啊。---------------------------------------------------------------
    -- 
    -- 表的结构 `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=187 ;-- 
    -- 导出表中的数据 `city`
    -- 
    INSERT INTO `city` VALUES (1, '北京', 1);
    INSERT INTO `city` VALUES (2, '上海', 2);
    INSERT INTO `city` VALUES (3, '天津', 3);
    INSERT INTO `city` VALUES (4, '重庆', 4);
    INSERT INTO `city` VALUES (5, '合肥', 5);
    INSERT INTO `city` VALUES (6, '黄山', 5);
    INSERT INTO `city` VALUES (7, '芜湖', 5);
    INSERT INTO `city` VALUES (8, '马鞍山', 5);
    INSERT INTO `city` VALUES (9, '安庆', 5);
    INSERT INTO `city` VALUES (10, '淮南', 5);
    INSERT INTO `city` VALUES (11, '阜阳', 5);
    INSERT INTO `city` VALUES (12, '蚌埠', 5);
    INSERT INTO `city` VALUES (13, '福州', 6);
    INSERT INTO `city` VALUES (14, '厦门', 6);
    INSERT INTO `city` VALUES (15, '泉州', 6);-- 
    -- 表的结构 `news`
    -- CREATE TABLE `news` (
      `news_id` int(11) NOT NULL auto_increment COMMENT '城市新闻编号',
      `news_name` varchar(255) default NULL COMMENT '城市新闻名称',
      `news_time` int(10) unsigned NOT NULL default '0' COMMENT '添加时间',
      `city_id` int(11) default NULL COMMENT '城市新闻所对应城市编号',
      `date2` int(10) unsigned NOT NULL default '0' COMMENT '最后修改日期',
      PRIMARY KEY  (`news_id`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COMMENT='城市动态新闻' AUTO_INCREMENT=744 ;-- 
    -- 导出表中的数据 `news`
    INSERT INTO `news` VALUES (1, '北京现', 1247512449, 1, 1247512449);
    INSERT INTO `news` VALUES (3, '近20', 1247512535, 1, 1247512535);
    INSERT INTO `news` VALUES (4, '瑞士国', 1247512601, 1, 1247512601);
    INSERT INTO `news` VALUES (5, '日本原', 1247512639, 1, 1247512639);
    INSERT INTO `news` VALUES (6, '北京举', 1247512770, 1, 1247512770);
    INSERT INTO `news` VALUES (7, '200', 1247512888, 1, 1247512888);
    INSERT INTO `news` VALUES (8, '珠海出', 1249404191, 35, 1249404191);
    INSERT INTO `news` VALUES (9, '红九九', 1249404398, 4, 1249404398);
    INSERT INTO `news` VALUES (10, '北外奥', 1249404542, 4, 1249404542);
    INSERT INTO `news` VALUES (11, '谨防手', 1249404652, 61, 1249404652);
    INSERT INTO `news` VALUES (12, '厦门：', 1249404690, 14, 1249404690);
    INSERT INTO `news` VALUES (13, '招行信', 1249404734, 27, 1249404734);
    INSERT INTO `news` VALUES (14, '湖北举', 1249404767, 25, 1249404767);
    INSERT INTO `news` VALUES (15, '中南剧', 1249404790, 25, 1249404790);
    INSERT INTO `news` VALUES (16, '武汉植', 1249404807, 25, 1249404807);
    INSERT INTO `news` VALUES (17, '“微笑', 1249404827, 25, 1249404827);-- 
    -- 表的结构 `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=32 ;-- 
    -- 导出表中的数据 `pro`
    -- INSERT INTO `pro` VALUES (1, '北京');
    INSERT INTO `pro` VALUES (2, '上海');
    INSERT INTO `pro` VALUES (3, '天津');
    INSERT INTO `pro` VALUES (4, '重庆');
    INSERT INTO `pro` VALUES (5, '安徽');
    INSERT INTO `pro` VALUES (6, '福建');
    INSERT INTO `pro` VALUES (7, '甘肃');
    INSERT INTO `pro` VALUES (8, '广东');
    INSERT INTO `pro` VALUES (9, '广西');
    INSERT INTO `pro` VALUES (10, '贵州');
    INSERT INTO `pro` VALUES (11, '海南');-- 
    -- 表的结构 `new`
    -- CREATE TABLE `new` (
      `new_id` int(11) NOT NULL auto_increment COMMENT '新闻编号',
      `new_name` varchar(255) 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=1237 ;-- 
    -- 导出表中的数据 `new`
    -- INSERT INTO `new` VALUES (21, '张庭人', 0, 0, 0);
    INSERT INTO `new` VALUES (22, '美国Ｃ', 0, 0, 0);
    INSERT INTO `new` VALUES (24, '伏明霞', 0, 0, 0);
    INSERT INTO `new` VALUES (25, '杰克逊', 0, 0, 0);
    INSERT INTO `new` VALUES (26, '全职妈', 1, 1, 1);
    INSERT INTO `new` VALUES (27, '产妇拒', 1, 24, 136);
    INSERT INTO `new` VALUES (28, '北京一', 1, 1, 1);
    INSERT INTO `new` VALUES (29, '女子告', 1, 1, 1);
    INSERT INTO `new` VALUES (30, '河南周', 1, 13, 70);
    INSERT INTO `new` VALUES (32, '我国将', 1, 0, 0);
    INSERT INTO `new` VALUES (33, '四成妈', 1, 0, 0);
    INSERT INTO `new` VALUES (34, '什么风', 0, 0, 0);
    INSERT INTO `new` VALUES (35, '荷兰最', 0, 0, 0);
    INSERT INTO `new` VALUES (36, '南京6', 1, 18, 28);
      

  3.   

    我的题目其实写错了，其实我没有得到正确的结果的。
    我现在得到的结果是这样的
    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)
      

  4.   

    意思就是指定了城市cityID了，那么就选出以下三种条件的新闻
    （已知条件是城市ID，排序按照发布时间DESC排序）
    1.表new中放到全国的新闻（该新闻只选择了放到全国，未指定任何省份和城市;
    就是表new中的is_citynew=1 ,pro_id=0,city_id=0）+表news中的所在城市ID为cityID的新闻。
    2.表new中放到全省的新闻（该省的proID为该城市cityID所在省份的proID;表new中新闻is_citynews=1,pro_id='该城市所在省份的ID',city_id=0）
    +表news中的所在城市ID为cityID的新闻。
    3.表new放到该市的新闻（指定了城市ID的新闻;即表new中is_citynews=1,pro_id='该城市所在省份的ID',city_id=该城市的ID）+表news中的所在城市ID为cityID的新闻。
      

  5.   

    表news中的城市ID是必选的，所以现在就是表new中的很难选出来。
      

  6.   

    那么正确的结果应该是什么？
    也就是如果我写了个SQL语句，这个SQL语句的结果应该是什么样？
      

  7.   

    非常感谢 ACMAIN_CHM。
    本贴解决办法在 http://topic.csdn.net/u/20091128/10/c63e491a-173e-4709-98ea-705f5cb7d3a1.html