|
|
Posted by Toby A Inkster on 12/06/07 09:34
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/
Navigation:
[Reply to this message]
|