ファイル名を大文字と小文字の両方を使った場合、テーブルのエリアスが使えなくなる。 例えば、 select a.* from herbRecordNo a => エラー select a.* from herbrecordno a => エラーにならない 原因は不明です。><
Category Archive: SQL
perl memo DBD::CSV JOIN
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 |
#!/usr/bin/perl use DBI; $dbh = DBI->connect("DBI:CSV:f_dir=/Volumes/RamDisk/csvdb") or die "Cannot connect: " . $DBI::errstr; $sth = $dbh->prepare("DROP TABLE a ") or die "Cannot prepare: " . $dbh->errstr(); $sth->execute() or die "Cannot execute: " . $sth->errstr(); $sth = $dbh->prepare("CREATE TABLE a (id INTEGER not null, name CHAR(10))") or die "Cannot prepare: " . $dbh->errstr(); $sth->execute() or die "Cannot execute: " . $sth->errstr(); $sth = $dbh->prepare(" insert into a (id,name) values (1,'カンマ 太郎')") or die "Cannot prepare: " . $dbh->errstr(); $sth->execute() or die "Cannot execute: " . $sth->errstr(); $sth = $dbh->prepare(" insert into a (id,name) values (2,'カンマ 次郎')") or die "Cannot prepare: " . $dbh->errstr(); $sth->execute() or die "Cannot execute: " . $sth->errstr(); $sth = $dbh->prepare(" update a set name='カンマ 花子' where id=1") or die "Cannot prepare: " . $dbh->errstr(); $sth->execute() or die "Cannot execute: " . $sth->errstr(); $sth = $dbh->prepare("DROP TABLE b ") or die "Cannot prepare: " . $dbh->errstr(); $sth->execute() or die "Cannot execute: " . $sth->errstr(); $sth = $dbh->prepare("CREATE TABLE b (id INTEGER not null, age INTEGER)") or die "Cannot prepare: " . $dbh->errstr(); $sth->execute() or die "Cannot execute: " . $sth->errstr(); $sth = $dbh->prepare(" insert into b (id,age) values (2,30)") or die "Cannot prepare: " . $dbh->errstr(); $sth->execute() or die "Cannot execute: " . $sth->errstr(); $sth = $dbh->prepare(" insert into b (id,age) values (1,26)") or die "Cannot prepare: " . $dbh->errstr(); $sth->execute() or die "Cannot execute: " . $sth->errstr(); my($query) = "SELECT a.id id , a.name name, b.age age FROM a a, b b WHERE a.id = b.id"; my($sth) = $dbh->prepare($query); $sth->execute(); while (my $row = $sth->fetchrow_hashref) { print("Found result row: id = ", $row->{'id'}, ", name = ", $row->{'name'}, ", age = ", $row->{'age'}, "\n"); } $sth->finish(); $dbh->disconnect(); |
【実行結果】 My-iPad:~/textastic/perl root# perl csv_join.pl Found result row: id = 1, name = カンマ 花子, age = 26 Found result row: id = 2, name = カンマ 次郎, age = 30
postgresql memo 異なるスキーマの突合
--検索スキーマの優先順位を設定 set search_path to schema1,schema2; --いまのスキーマを表示 select current_schema(); --異なるスキーマにある同じ名前のテーブルを突合 select t1.*,t2.* from schema1.table1 s1, schema2.info_resource s2 where s1.key=s2.key;