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
|
-- from http://www.depesz.com/index.php/2010/04/19/getting-unique-elements/
CREATE TEMP TABLE articles (
id int CONSTRAINT articles_pkey PRIMARY KEY,
keywords text,
title text UNIQUE NOT NULL,
body text UNIQUE,
created date
);
CREATE TEMP TABLE articles_in_category (
article_id int,
category_id int,
changed date,
PRIMARY KEY (article_id, category_id)
);
-- example from documentation
CREATE TEMP TABLE products (product_id int, name text, price numeric);
CREATE TEMP TABLE sales (product_id int, units int);
-- Drupal example, http://drupal.org/node/555530
CREATE TEMP TABLE node (
nid SERIAL,
vid integer NOT NULL default '0',
type varchar(32) NOT NULL default '',
title varchar(128) NOT NULL default '',
uid integer NOT NULL default '0',
status integer NOT NULL default '1',
created integer NOT NULL default '0',
-- snip
PRIMARY KEY (nid, vid)
);
CREATE TEMP TABLE users (
uid integer NOT NULL default '0',
name varchar(60) NOT NULL default '',
pass varchar(32) NOT NULL default '',
-- snip
PRIMARY KEY (uid),
UNIQUE (name)
);
-- OK
SELECT u.uid, u.name FROM node n
INNER JOIN users u ON u.uid = n.uid
WHERE n.type = 'blog' AND n.status = 1
GROUP BY u.uid, u.name;
|
