求助一条SQL

larry_chen · 发表于 2007-1-15 11:17

最初由 atgc 发布
[B]就这样好了，left join也不需要的
[PHP]
select x.PROPERTY_NAME,
   y.PROPERTY_NAME
from (select a.PROPERTY_NAME,
            b.MATCH_CODE
      from property a,
            matchs b
      where  a.PROPERTY_CODE=b.position) x,
   (select a.MATCH_CODE,
            b.PROPERTY_NAME
      from matchs a,
            property b
      where  a.MATCH_CODE=b.PROPERTY_CODE) y
where  x.MATCH_CODE=y.MATCH_CODE;

+---------------+---------------+
| PROPERTY_NAME | PROPERTY_NAME |
+---------------+---------------+
| FW          | football    |
| MF          | golf       |
+---------------+---------------+
2 rows in set (0.00 sec)

[/PHP] [/B]

谢谢楼上的朋友！！！

larry_chen · 发表于 2007-1-15 11:24

对了，麻烦再问一下，如果我的matchs表里还有别的字段能一起显示出来吗？比如：
mysql> select * from property;
+---------------+---------------+
| PROPERTY_CODE | PROPERTY_NAME |
+---------------+---------------+
| 100010             | FW          |
| 100011       | MF          |
| 100020       | football    |
| 100021       | golf       |
+---------------+---------------+

mysql> select * from matchs;
+----------+------------+------------+
| POSITION | MATCH_CODE |MATCH_DATE |
+----------+------------+------------+
| 100010 | 100020    | 2006-01-15 |
| 100011 | 100021    | 2006-01-12 |
+----------+------------+------------+

结果：
+---------------+---------------+---------------+
| PROPERTY_NAME | PROPERTY_NAME |MATCH_DATE |
+---------------+---------------+---------------+
| FW          | football    | 2006-01-15 |
| MF          | golf       | 2006-01-12 |
+---------------+---------------+---------------+

atgc · 发表于 2007-1-15 17:51

能的，你只要在子查询里加上你要的字段即可

atgc · 发表于 2007-1-15 18:15

[PHP]
select x.PROPERTY_NAME,
   y.PROPERTY_NAME,
   y.match_date
from (select a.PROPERTY_NAME,
            b.MATCH_CODE
      from property a,
            matchs b
      where  a.PROPERTY_CODE=b.position) x,
   (select a.MATCH_CODE,
            b.PROPERTY_NAME,
            a.match_date
      from matchs a,
            property b
      where  a.MATCH_CODE=b.PROPERTY_CODE) y
where  x.MATCH_CODE=y.MATCH_CODE;

+---------------+---------------+------------+
| PROPERTY_NAME | PROPERTY_NAME | match_date |
+---------------+---------------+------------+
| FW          | football    | 2006-01-15 |
| MF          | golf       | 2006-01-12 |
+---------------+---------------+------------+
2 rows in set (0.00 sec)
[/PHP]

larry_chen · 发表于 2007-1-16 10:50

最初由 atgc 发布
[B][PHP]
select x.PROPERTY_NAME,
   y.PROPERTY_NAME,
   y.match_date
from (select a.PROPERTY_NAME,
            b.MATCH_CODE
      from property a,
            matchs b
      where  a.PROPERTY_CODE=b.position) x,
   (select a.MATCH_CODE,
            b.PROPERTY_NAME,
            a.match_date
      from matchs a,
            property b
      where  a.MATCH_CODE=b.PROPERTY_CODE) y
where  x.MATCH_CODE=y.MATCH_CODE;

+---------------+---------------+------------+
| PROPERTY_NAME | PROPERTY_NAME | match_date |
+---------------+---------------+------------+
| FW          | football    | 2006-01-15 |
| MF          | golf       | 2006-01-12 |
+---------------+---------------+------------+
2 rows in set (0.00 sec)
[/PHP] [/B]

十分感谢！

larry_chen · 发表于 2007-1-16 11:08

最初由 atgc 发布
[B]能的，你只要在子查询里加上你要的字段即可 [/B]

如果是这种情况呢？
[PHP]
mysql> select * from property;
+---------------+---------------+
| PROPERTY_CODE | PROPERTY_NAME |
+---------------+---------------+
| 100010       | FW          |
| 100011       | MF          |
| 100020       | football    |
| 100021       | golf       |
+---------------+---------------+

mysql> select * from matchs;
+----------+------------+------------+
| POSITION | MATCH_CODE | MATCH_DATE |
+----------+------------+------------+
| 100010 |          | 2006-01-15 |
| 100011 | 100021    | 2006-01-12 |
+----------+------------+------------+

现在的结果是：
+---------------+---------------+---------------+
| PROPERTY_NAME | PROPERTY_NAME | MATCH_DATE |
+---------------+---------------+---------------+
| MF          | golf       | 2006-01-12 |
+---------------+---------------+---------------+
我希望的结果是：
+---------------+---------------+---------------+
| PROPERTY_NAME | PROPERTY_NAME | MATCH_DATE |
+---------------+---------------+---------------+
| FW          |             | 2006-01-15 |
| MF          | golf       | 2006-01-12 |
+---------------+---------------+---------------+
[/PHP]
请指教，谢谢。

atgc · 发表于 2007-1-16 19:21

[PHP]
mysql> select * from property;
+---------------+---------------+
| PROPERTY_CODE | PROPERTY_NAME |
+---------------+---------------+
| 100010       | FW          |
| 100011       | MF          |
| 100020       | football    |
| 100021       | golf       |
+---------------+---------------+
4 rows in set (0.00 sec)

mysql> select * from matchs;
+----------+------------+------------+
| POSITION | MATCH_CODE | match_date |
+----------+------------+------------+
| 100010 |          | 2006-01-15 |
| 100011 | 100021    | 2006-01-12 |
+----------+------------+------------+
2 rows in set (0.00 sec)

select x.PROPERTY_NAME,
   if(y.PROPERTY_NAME is null,'',y.PROPERTY_NAME) PROPERTY_NAME,
   y.match_date
from property x,
   (
   select a.position,
            b.property_name,
            a.match_date
   from matchs a
            left join property b
   on    a.match_code=b.property_code
   ) y
where  x.PROPERTY_CODE=y.position;

+---------------+---------------+------------+
| PROPERTY_NAME | PROPERTY_NAME | match_date |
+---------------+---------------+------------+
| FW          |             | 2006-01-15 |
| MF          | golf       | 2006-01-12 |
+---------------+---------------+------------+
2 rows in set (0.00 sec)

[/PHP]

larry_chen · 发表于 2007-1-16 19:33

最初由 atgc 发布
[B][PHP]
mysql> select * from property;
+---------------+---------------+
| PROPERTY_CODE | PROPERTY_NAME |
+---------------+---------------+
| 100010       | FW          |
| 100011       | MF          |
| 100020       | football    |
| 100021       | golf       |
+---------------+---------------+
4 rows in set (0.00 sec)

mysql> select * from matchs;
+----------+------------+------------+
| POSITION | MATCH_CODE | match_date |
+----------+------------+------------+
| 100010 |          | 2006-01-15 |
| 100011 | 100021    | 2006-01-12 |
+----------+------------+------------+
2 rows in set (0.00 sec)

select x.PROPERTY_NAME,
   if(y.PROPERTY_NAME is null,'',y.PROPERTY_NAME) PROPERTY_NAME,
   y.match_date
from property x,
   (
   select a.position,
            b.property_name,
            a.match_date
   from matchs a
            left join property b
   on    a.match_code=b.property_code
   ) y
where  x.PROPERTY_CODE=y.position;

+---------------+---------------+------------+
| PROPERTY_NAME | PROPERTY_NAME | match_date |
+---------------+---------------+------------+
| FW          |             | 2006-01-15 |
| MF          | golf       | 2006-01-12 |
+---------------+---------------+------------+
2 rows in set (0.00 sec)

[/PHP] [/B]

多谢！

larry_chen · 发表于 2007-1-17 17:00

如果是这种情况应该如何写SQL?
[PHP]
mysql> select * from property;
+---------------+---------------+
| PROPERTY_CODE | PROPERTY_NAME |
+---------------+---------------+
| 100010       | FW          |
| 100011       | MF          |
| 100020       | football    |
| 100021       | golf       |
+---------------+---------------+

mysql> select * from users;
+-----------+------------+
| USER_CODE | USER_NAME  |
+-----------+------------+
| 100001 | Jack    |
| 100002 | Lee       |
+-----------+------------+

mysql> select * from matchs;
+----------+------------+------------+
| POSITION | MATCH_CODE | USER_CODE  |
+----------+------------+------------+
| 100010 | 100020    | 100001    |
| 100011 | 100020    | 100002    |
+----------+------------+------------+

我希望的结果是：
+---------------+---------------+
| PROPERTY_NAME | USER_NAME    |
+---------------+---------------+
| FW          | Jack          |
| MF          | Lee          |
+---------------+---------------+
[/PHP]
请指教，谢谢。

atgc · 发表于 2007-1-17 18:29

你自己写写试试好吗，我已经给你写了很多了，都类似的