我有一个表名relation_country,其中有三列id、countrya和countryb数据如下。。。
id country_a country_b
1 USA France
2 Australia USA
3 France Australia
4 India Rassia
5 Rassia USA
6 France Rassia
7 USA India
在这里,我看到了美国和拉西亚之间的双赢关系。现在我想知道他们中有多少人有相似的国家。
say, Rassia has France & India
and USA has also France and India.
我可以提出疑问,表明美国有(法国、澳大利亚、印度)拉西亚有(法国和印度)但我不能证明,这两个国家与(印度和法国)有着相似的关系。
请帮忙。
很抱歉花了这么长时间,我不得不将数据放在本地并进行测试。
这是您需要的代码:
(SELECT DISTINCT `country_a` FROM `relation_country` WHERE(`country_b` ='USA' AND (`country_a` IN (SELECT `country_a` FROM `relation_country` WHERE `country_b`= 'Russia') OR `country_a` IN (SELECT `country_b` FROM `relation_country` WHERE `country_a`='Russia')))) UNION (SELECT DISTINCT `country_b` FROM `relation_country` WHERE(`country_a` ='USA' AND (`country_b` IN (SELECT `country_a` FROM `relation_country` WHERE `country_b`= 'Russia') OR `country_b` IN (SELECT `country_b` FROM `relation_country` WHERE `country_a`='Russia'))))
格式化形式:
(
SELECT DISTINCT `country_a`
FROM `relation_country`
WHERE (
`country_b` = 'USA'
AND (
`country_a`
IN (
SELECT `country_a`
FROM `relation_country`
WHERE `country_b` = 'Russia'
)
OR `country_a`
IN (
SELECT `country_b`
FROM `relation_country`
WHERE `country_a` = 'Russia'
)
)
)
)
UNION (
SELECT DISTINCT `country_b`
FROM `relation_country`
WHERE (
`country_a` = 'USA'
AND (
`country_b`
IN (
SELECT `country_a`
FROM `relation_country`
WHERE `country_b` = 'Russia'
)
OR `country_b`
IN (
SELECT `country_b`
FROM `relation_country`
WHERE `country_a` = 'Russia'
)
)
)
)
注意对DISTINCT值使用UNION。
是的,我为美国捐款,这与Rassia 类似
$USA1 = mysql_query("SELECT * FROM relation_country WHERE country_a= 'USA'");
$USA_numrows1 = mysql_num_rows($USA1);
if($USA_numrows1!=0){
while($row1 = mysql_fetch_assoc($USA1)){
$country_b =$row1['country_b'];
echo $country_b."<br/>";
}
}
$USA2=mysql_query("SELECT*FROM relation_country WHERE country_b='USA'");$USA_numrows2=mysql_num_rows($USA2);
if($USA_numrows2!=0){
while($row2 = mysql_fetch_assoc($USA2)){
$country_a =$row2['country_a'];
echo $country_a."<br/>";
}
}
$russia1=mysql_query("SELECT*FROM relation_country WHERE country_a='russia'");$russia_numrows1=mysql_num_rows($russia1);
if($russia_numrows1!=0){
while($row3 = mysql_fetch_assoc($russia1)){
$country_b1 =$row3['country_b'];
echo $country_b1."<br/>";
}
}
$russia2=mysql_query("SELECT*FROM relation_country WHERE country_b='Russian'");$russia_numrows2=mysql_num_rows($russia2);
if($russia_numrows2!=0){
while($row4 = mysql_fetch_assoc($russia2)){
$country_a1 =$row4['country_a'];
echo $country_a1."<br/>";
}
}
如果我理解正确,这里是sql结果。为了让sql更容易理解,创建以下视图。
"创建视图xcountry as select country_a,country_b from relation_country union select country_b,country_afrom relation.country;"
然后选择
"从xcountry中选择country_b,其中country_a='USA'和country_bin(从xcountry_a='Rassia'中选择country _b);"
结果是两行;法国和印度。希望这能有所帮助。