mysql version 4.0.13-nt，能不能用一个sql语句实现这样的select

[loveflea]

说明：province表 parent=0 的是省、直辖市、自治区，parent <> 0 的就是代表属于省、直辖市、自治区的id号
region 表 city 对应 province 表的 id 字段

mysql version 4.0.13-nt，能不能用一个sql语句实现这样的select

id username city province
1 coolwind 成都 四川
2 sunnywest 内江 四川
3 sunnyday 攀枝花 四川

数据如下：

[lxf_1976]

试试：

SELECT r.id, r.username, p1.Province as city, p2.Province FROM region r
LEFT JOIN province p1 ON r.city=p1.id
LEFT JOIN province p2 ON p1.parent=p2.id
WHERE ...

[shuixin13]

SELECT A.id, A.UserName, B.Province, C.Province
FROM province B
LEFT JOIN province C ON B.Parent = C.id
LEFT JOIN region A ON A.City = B.id
WHERE A.id IS NOT NULL
ORDER BY A.id

[shuixin13]

嘻嘻,
查询效率是小木的高

[loveflea]

谢谢两位大虾。

[loveflea]

几条纪录测试不出来谁的效率高，不过看样子确实 lxf_1976(小木) 的要好些；呵呵！

[loveflea]

# MySQL-Front Dump 2.5
#
# Host: localhost Database: tmp
# --------------------------------------------------------
# Server version 4.0.13-nt


#
# Table structure for table 'province'
#

DROP TABLE IF EXISTS province;
CREATE TABLE province (
id int(10) unsigned NOT NULL auto_increment,
Parent tinyint(4) unsigned NOT NULL default '0',
Province varchar(15) NOT NULL default '',
About varchar(100) default NULL,
PRIMARY KEY (id)
) TYPE=MyISAM;



#
# Dumping data for table 'province'
#

INSERT INTO province (id, Parent, Province, About) VALUES("1", "0", "北京", "直辖市");
INSERT INTO province (id, Parent, Province, About) VALUES("2", "0", "天津", "直辖市");
INSERT INTO province (id, Parent, Province, About) VALUES("3", "0", "河北", NULL);
INSERT INTO province (id, Parent, Province, About) VALUES("4", "0", "山西", NULL);
INSERT INTO province (id, Parent, Province, About) VALUES("5", "0", "内蒙", "自治区");
INSERT INTO province (id, Parent, Province, About) VALUES("6", "0", "辽宁", NULL);
INSERT INTO province (id, Parent, Province, About) VALUES("7", "0", "吉林", NULL);
INSERT INTO province (id, Parent, Province, About) VALUES("8", "0", "黑龙江", NULL);
INSERT INTO province (id, Parent, Province, About) VALUES("9", "0", "上海", "直辖市");
INSERT INTO province (id, Parent, Province, About) VALUES("10", "0", "江苏", NULL);
INSERT INTO province (id, Parent, Province, About) VALUES("11", "0", "浙江", NULL);
INSERT INTO province (id, Parent, Province, About) VALUES("12", "0", "安徽", NULL);
INSERT INTO province (id, Parent, Province, About) VALUES("13", "0", "福建", NULL);
INSERT INTO province (id, Parent, Province, About) VALUES("14", "0", "江西", NULL);
INSERT INTO province (id, Parent, Province, About) VALUES("15", "0", "山东", NULL);
INSERT INTO province (id, Parent, Province, About) VALUES("16", "0", "河南", NULL);
INSERT INTO province (id, Parent, Province, About) VALUES("17", "0", "湖北", NULL);
INSERT INTO province (id, Parent, Province, About) VALUES("18", "0", "湖南", NULL);
INSERT INTO province (id, Parent, Province, About) VALUES("19", "0", "广东", NULL);
INSERT INTO province (id, Parent, Province, About) VALUES("20", "0", "广西", "自治区");
INSERT INTO province (id, Parent, Province, About) VALUES("21", "0", "海南", NULL);
INSERT INTO province (id, Parent, Province, About) VALUES("22", "0", "四川", NULL);
INSERT INTO province (id, Parent, Province, About) VALUES("23", "0", "重庆", "直辖市");
INSERT INTO province (id, Parent, Province, About) VALUES("24", "0", "贵州", NULL);
INSERT INTO province (id, Parent, Province, About) VALUES("25", "0", "云南", NULL);
INSERT INTO province (id, Parent, Province, About) VALUES("26", "0", "陕西", NULL);
INSERT INTO province (id, Parent, Province, About) VALUES("27", "0", "甘肃", NULL);
INSERT INTO province (id, Parent, Province, About) VALUES("28", "0", "青海", NULL);
INSERT INTO province (id, Parent, Province, About) VALUES("29", "0", "宁夏", "自治区");
INSERT INTO province (id, Parent, Province, About) VALUES("30", "0", "新疆", "自治区");
INSERT INTO province (id, Parent, Province, About) VALUES("31", "0", "西藏", "自治区");
INSERT INTO province (id, Parent, Province, About) VALUES("32", "0", "香港", "特别行政区");
INSERT INTO province (id, Parent, Province, About) VALUES("33", "0", "澳门", "特别行政区");
INSERT INTO province (id, Parent, Province, About) VALUES("34", "0", "台湾", "海外");
INSERT INTO province (id, Parent, Province, About) VALUES("35", "22", "成都", NULL);
INSERT INTO province (id, Parent, Province, About) VALUES("36", "22", "攀枝花", NULL);
INSERT INTO province (id, Parent, Province, About) VALUES("37", "22", "自贡", NULL);
INSERT INTO province (id, Parent, Province, About) VALUES("38", "22", "绵阳", NULL);
INSERT INTO province (id, Parent, Province, About) VALUES("39", "22", "南充", NULL);
INSERT INTO province (id, Parent, Province, About) VALUES("40", "22", "达川", NULL);
INSERT INTO province (id, Parent, Province, About) VALUES("41", "22", "遂宁", NULL);
INSERT INTO province (id, Parent, Province, About) VALUES("42", "22", "广安", NULL);
INSERT INTO province (id, Parent, Province, About) VALUES("43", "22", "巴中", NULL);
INSERT INTO province (id, Parent, Province, About) VALUES("44", "22", "宜宾", NULL);
INSERT INTO province (id, Parent, Province, About) VALUES("45", "22", "内江", NULL);
INSERT INTO province (id, Parent, Province, About) VALUES("46", "22", "资阳", NULL);
INSERT INTO province (id, Parent, Province, About) VALUES("47", "22", "乐山", NULL);
INSERT INTO province (id, Parent, Province, About) VALUES("48", "22", "眉山", NULL);
INSERT INTO province (id, Parent, Province, About) VALUES("49", "22", "西昌(凉山)", NULL);
INSERT INTO province (id, Parent, Province, About) VALUES("50", "22", "雅安", NULL);
INSERT INTO province (id, Parent, Province, About) VALUES("51", "22", "康定(甘孜)", NULL);
INSERT INTO province (id, Parent, Province, About) VALUES("52", "22", "马尔康(阿坝)", NULL);
INSERT INTO province (id, Parent, Province, About) VALUES("53", "22", "德阳", NULL);
INSERT INTO province (id, Parent, Province, About) VALUES("54", "22", "广元", NULL);
INSERT INTO province (id, Parent, Province, About) VALUES("55", "22", "泸州", NULL);
INSERT INTO province (id, Parent, Province, About) VALUES("56", "23", "重庆", NULL);
INSERT INTO province (id, Parent, Province, About) VALUES("57", "24", "贵阳", NULL);
INSERT INTO province (id, Parent, Province, About) VALUES("58", "24", "遵义", NULL);
INSERT INTO province (id, Parent, Province, About) VALUES("59", "24", "安顺", NULL);
INSERT INTO province (id, Parent, Province, About) VALUES("60", "24", "都匀(黔南)", NULL);
INSERT INTO province (id, Parent, Province, About) VALUES("61", "24", "凯里(黔东南)", NULL);
INSERT INTO province (id, Parent, Province, About) VALUES("62", "24", "铜仁", NULL);
INSERT INTO province (id, Parent, Province, About) VALUES("63", "24", "毕节", NULL);
INSERT INTO province (id, Parent, Province, About) VALUES("64", "24", "六盘水", NULL);
INSERT INTO province (id, Parent, Province, About) VALUES("65", "24", "兴义(黔西南)", NULL);


#
# Table structure for table 'region'
#

DROP TABLE IF EXISTS region;
CREATE TABLE region (
id int(10) unsigned NOT NULL auto_increment,
UserName varchar(20) NOT NULL default '',
City tinyint(4) unsigned NOT NULL default '0',
PRIMARY KEY (id)
) TYPE=MyISAM;



#
# Dumping data for table 'region'
#

INSERT INTO region (id, UserName, City) VALUES("1", "coolwind", "35");
INSERT INTO region (id, UserName, City) VALUES("2", "sunnywest", "45");
INSERT INTO region (id, UserName, City) VALUES("3", "sunnyday", "36");