|
|
Posted by Mikhail Kovalev on 12/06/07 17:09
On 6 Des, 10:34, Toby A Inkster <usenet200...@tobyinkster.co.uk>
wrote:
> Mikhail Kovalev wrote:
> > I have different databases for different types of sequences.
>
> Aha. I don't think you mentioned that earlier.
>
> CREATE TABLE data_sets
> (
> data_set integer NOT NULL PRIMARY KEY,
> data_set_name varchar(20)
> );
> CREATE TABLE nodes
> (
> data_set integer NOT NULL
> REFERENCES data_sets ON DELETE CASCADE,
> node_address varchar(1024) NOT NULL,
> node_count integer,
> PRIMARY KEY (data_set_id, node_address)
> );
>
> An example of inserting some data:
>
> INSERT INTO data_sets VALUES (1, 'Example Data Set 1');
> INSERT INTO nodes VALUES (1, '112', 3);
> INSERT INTO nodes VALUES (1, '118', 4);
> INSERT INTO nodes VALUES (1, '112/11', 5);
> INSERT INTO data_sets VALUES (2, 'Example Data Set 2');
> INSERT INTO nodes VALUES (2, '112', 2);
> INSERT INTO nodes VALUES (2, '4/115', 3);
>
> An example of selecting data from Data Set 1:
>
> SELECT node_count
> FROM nodes
> WHERE data_set=1
> AND node_address='112';
> (returns 3)
>
> Some examples of selecting the sum of data from multiple sets:
>
> SELECT sum(node_count) AS node_count
> FROM nodes
> WHERE data_set IN (1, 2)
> AND node_address='112'
> GROUP BY node_address;
> (returns 5)
>
> SELECT node_address, sum(node_count) AS node_count
> FROM nodes
> WHERE data_set IN (1, 2)
> GROUP BY node_address;
> (returns list of all nodes in set 1 or set 2, plus sum of counts
> for both data sets)
>
> Let's create a new data set 3 and populate it with the data from the
> previous query. That is, data set 3 is the sum of data sets 1 and 2:
>
> INSERT INTO data_sets VALUES (3, 'Combined Set');
> INSERT INTO nodes
> SELECT 3 AS data_set, node_address, sum(node_count) AS node_count
> FROM nodes
> WHERE data_set IN (1, 2)
> GROUP BY node_address;
>
> SQL is a really easy way of manipulating data sets.
>
> --
> Toby A Inkster BSc (Hons) ARCS
> [Geek of HTML/SQL/Perl/PHP/Python/Apache/Linux]
> [OS: Linux 2.6.17.14-mm-desktop-9mdvsmp, up 11 days, 16:07.]
>
> Sharing Music with Apple iTunes
> http://tobyinkster.co.uk/blog/2007/11/28/itunes-sharing/
This and the wildcard search will save me 50% of programming.
Seriously, I don't understand why I didn't looked into it earlier.
Thanks for the input!
And sorry about last empty message, sent it by mistake.
Navigation:
[Reply to this message]
|