通过表内自连是可以的:
SELECT c.husband,
p1.age,
c.wife,
p2.age
FROM couple c,
people p1,
people p2
WHERE c.husband = p1.name
AND c.wife = p2.name;
但是 DBD::CSV 对自连似乎支持不好，如果你复制一份 people.csv 为 people2.csv, 然后改SQL语句为：
SELECT c.husband,
p1.age,
c.wife,
p2.age
FROM couple c,
people p1,
people2 p2
WHERE c.husband = p1.name
AND c.wife = p2.name
也是可以的。
2010/11/17 ZHANG Jiaqiang A <[email protected]>
> 我想一条语句把老公和老婆的年龄一起读出来，我的想像中，SQL的输出应该如下：
>
> c.husband |h.age |c.wife |w.age |
> ================================================
> James |30 |Mary |22 |
> Roger |24 |Lily |26 |
> ================================================
>
> 下面是可用的测试代码：（附件里面有测试文件，放到和脚本一起的目录应该就可以用)
>
> #!/usr/bin/perl -w
>
> use strict;
>
> use DBI;
>
> my $dbh = DBI->connect ("dbi:CSV:", "", "", {
> f_ext => ".csv",
> csv_null => 1,
> FetchHashKeyName => 'NAME_lc',
> });
>
> my $query = "select c.husband,h.age,c.wife,w.age from couple c, people h,
> people w where c.husband = h.name and c.wife = w.name";
> #my $query = "select c.husband,h.age from couple c, people h where
> c.husband = h.name ";
> my $sth = $dbh->prepare ($query);
> $sth->execute ();
>
> while (my $row = $sth->fetchrow_hashref) {
> foreach my $col ( keys %$row ) {
> print $col,"=",$row->{$col},"\t" if defined $row->{$col};
> }
> print "done\n\n";
> }
> $sth->finish ();
>
> ------------------------------
> *From:* [email protected] [mailto:[email protected]] *On
> Behalf Of *cnhack TNT
> *Sent:* 2010年11月17日 9:41
> *To:* [email protected]
> *Subject:* Re: [PerlChina] CSV的SQL语句中一张表不能出现两次
>
> 为啥要 “people h, people w” ？
>
> 2010/11/17 ZHANG Jiaqiang A <[email protected]>
>
>> 大家好，
>>
>> 有一张表，我需要多次关联，设了不同的别名，但是执行SQL的时候报错，大家有用过DBD-CSV的高手，帮我看看这是DBDCSV的限制还是SQL写的不对
>>
>> SQL语句:
>> select c.husband,h.age,c.wife,w.age from couple c, people h, people w
>> where c.husband = h.name and c.wife = w.name;
>>
>> 报错信息：
>> DBD::CSV::st execute failed: Error 2012 while reading file
>> D:\Perl\people.csv: E
>> OF - End of data in parsing input stream at
>> C:/strawberry/perl/site/lib/SQL/Stat
>> ement.pm line 813
>> [for Statement "select c.husband,h.age,c.wife,w.age from couple c, people
>> h, pe
>> ople w where c.husband = h.name and c.wife = w.name"] at csv.pl line 99.
>>
>>
>> 以下SQL语句都可以正常执行:
>>
>> select * from couple;
>>
>> husband |wife |
>> ========================
>> James |Mary |
>> Roger |Lily |
>> ========================
>> Return 2 columns and 2 lines
>>
>> select * from people;
>>
>> name |age |
>> ========================
>> Mary |22 |
>> Lily |26 |
>> Roger |24 |
>> James |30 |
>> ========================
>> Return 2 columns and 4 lines
>>
>> select c.husband,h.age from couple c, people h where c.husband = h.name;
>>
>> c.husband |h.age |
>> ========================
>> James |30 |
>> Roger |24 |
>> ========================
>> Return 2 columns and 2 lines
>>
>> select c.wife,w.age from couple c, people w where c.wife = w.name;
>>
>> c.wife |w.age |
>> ========================
>> Mary |22 |
>> Lily |26 |
>> ========================
>> Return 2 columns and 2 lines
>>
>>
>> 祝好
>> 家强
>>
>> --
>> 您收到此邮件是因为您订阅了 Google 网上论坛的“PerlChina Mongers 讨论组”论坛。
>> 要向此网上论坛发帖，请发送电子邮件至 [email protected]。
>> 要取消订阅此网上论坛，请发送电子邮件至
>> [email protected]<perlchina%[email protected]>
>> 。
>> 若有更多问题，请通过 http://groups.google.com/group/perlchina?hl=zh-CN 访问此网上论坛。
>>
>
> --
> 您收到此邮件是因为您订阅了 Google 网上论坛的“PerlChina Mongers 讨论组”论坛。
> 要向此网上论坛发帖，请发送电子邮件至 [email protected]。
> 要取消订阅此网上论坛，请发送电子邮件至
> [email protected]<perlchina%[email protected]>
> 。
> 若有更多问题，请通过 http://groups.google.com/group/perlchina?hl=zh-CN 访问此网上论坛。
>
> --
> 您收到此邮件是因为您订阅了 Google 网上论坛的“PerlChina Mongers 讨论组”论坛。
> 要向此网上论坛发帖，请发送电子邮件至 [email protected]。
> 要取消订阅此网上论坛，请发送电子邮件至
> [email protected]<perlchina%[email protected]>
> 。
> 若有更多问题，请通过 http://groups.google.com/group/perlchina?hl=zh-CN 访问此网上论坛。
>
--
您收到此邮件是因为您订阅了 Google 网上论坛的“PerlChina Mongers 讨论组”论坛。
要向此网上论坛发帖，请发送电子邮件至 [email protected]。
要取消订阅此网上论坛，请发送电子邮件至 [email protected]。
若有更多问题，请通过 http://groups.google.com/group/perlchina?hl=zh-CN 访问此网上论坛。
