On 4/3/2014 8:46 PM, YAN HONG YE wrote:
I have a table named aa like this:
id pid name note tablename
1 0 s12 bbc k1
2 1 sss vac k2
another table named k1:
id pid name note
11 1 f2 aaa
12 1 fs bbc
another table named k2:
id pid name note
31 2 f2 aaa
32 2 fs bbc
my question is: in table aa, if possible k1 point to another table "k1"?
select tablename from aa;
I want result is:
id pid name note
11 1 f2 aaa
12 1 fs bbc
31 2 f2 aaa
32 2 fs bbc
I don't believe this can be done in pure SQL since table names are not
values. That doesn't mean it can't be done, though you will have to put
some of the logic in your program itself. Here's a Tcl implementation
that gives the same result as in your question. The actual work is done
in the last two lines; the rest is for displaying the result nicely and
for creating the database.
# Recent version of Tcl required for [lmap] and max().
package require Tcl 8.6
package require sqlite3
# Executes an SQL query and prints it in nicely formatted columns.
proc query {db query} {
$db eval $query out {
if {![info exists widths]} {
lappend grid $out(*)
set widths [lmap column $out(*) {string length $column}]
}
lappend grid [set row [lmap column $out(*) {set out($column)}]]
set widths [lmap width $widths value $row {expr {
max($width, [string length $value])
}}]
}
set format %-[join $widths "s %-"]s
set display {}
foreach row $grid {
lappend display [format $format {*}$row]
}
join $display \n
}
# Create the database.
sqlite3 db :memory:
db eval {
CREATE TABLE aa (id, pid, name, note, tablename);
INSERT INTO aa VALUES (1, 0, 's12', 'bbc', 'k1'),
(2, 1, 'sss', 'vac', 'k2');
CREATE TABLE k1 (id, pid, name, note);
INSERT INTO k1 VALUES (11, 1, 'f2', 'aaa'),
(12, 1, 'fs', 'bbc');
CREATE TABLE k2 (id, pid, name, note);
INSERT INTO k2 VALUES (31, 1, 'f2', 'aaa'),
(32, 1, 'fs', 'bbc');
}
# Get list of subqueries which need to be UNION'ed.
set queries [db eval {SELECT 'SELECT * FROM ' || tablename FROM aa}]
# Join subqueries with UNION ALL, then execute and display result.
puts [query db [join $queries " UNION ALL "]]
--
Andy Goth | <andrew.m.goth/at/gmail/dot/com>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
