Alan Cor's questions

考虑下表

Device
--------
id
name
type
--------

components
--------
id INT
type VARCHAR
--------

Manufacturers
-------------
id INT
name VARCHAR
country VARCHAR
-------------

Device_components
-----------------
deviceid REFERENCES Devices(id)
componentid REFERENCES Components(id)
-----------------

Component_Manufacturers
-----------------------
componentid REFERENCES Components(id)
manufacturerid REFERENCES Manufacturers(id)
-----------------------

我想查询数据库以返回如下内容：

{
 "id": 1,
  "name": "phone",
  "components": [
    {
      "id": 1,
      "type": "screen",
      "manufacturers": [
       {
         "id": 1,
          "name": "a",
          "country": "Germany"
        }
      ]
   },
   {
     "id": 2,
      "type": "keyboard",
      "manufacturers": [
        {
         "id": 1,
          "name": "a",
          "country": "UK"
        }
     ]
   }
  ]
}

到目前为止，我分别从每个表中进行选择，然后在我的应用程序中组装 JSON 对象。

以下是一些示例数据：

CREATE TABLE IF NOT EXISTS Devices
(
    ID SERIAL PRIMARY KEY NOT NULL,
    NAME VARCHAR(30) UNIQUE NOT NULL
);

CREATE TABLE IF NOT EXISTS Components
(
    ID SERIAL PRIMARY KEY NOT NULL,
    NAME VARCHAR(30) UNIQUE NOT NULL
);

CREATE TABLE IF NOT EXISTS Manufacturers
(
    ID SERIAL PRIMARY KEY NOT NULL,
    NAME VARCHAR(30) UNIQUE NOT NULL,
    COUNTRY VARCHAR(40)
);  

CREATE TABLE IF NOT EXISTS Device_components
(
DeviceID INT REFERENCES Devices(ID),
ComponentID INT REFERENCES Components(ID)
);

CREATE TABLE IF NOT EXISTS Component_manufacturers
(
ComponentID INT REFERENCES Components(ID),
ManufacturerID INT REFERENCES Manufacturers(ID)
);

INSERT INTO Devices (NAME) VALUES ('phone');
INSERT INTO Devices (NAME) VALUES ('tablet');
INSERT INTO Devices (NAME) VALUES ('pc');

INSERT INTO Components (NAME) VALUES ('mouse');
INSERT INTO Components (NAME) VALUES ('camera');
INSERT INTO Components (NAME) VALUES ('screen');

INSERT INTO Manufacturers (NAME,Country) VALUES ('foo','france');
INSERT INTO Manufacturers (NAME,Country) VALUES ('bar','spain');
INSERT INTO Manufacturers (NAME,Country) VALUES ('baz','germany');

INSERT INTO Device_components VALUES (1,2);
INSERT INTO Device_components VALUES (1,3);
INSERT INTO Device_components VALUES (2,2);
INSERT INTO Device_components VALUES (2,3);
INSERT INTO Device_components VALUES (3,1);
INSERT INTO Device_components VALUES (3,2);
INSERT INTO Device_components VALUES (3,3);

INSERT INTO Component_manufacturers VALUES (1,1);
INSERT INTO Component_manufacturers VALUES (1,2);
INSERT INTO Component_manufacturers VALUES (1,3);
INSERT INTO Component_manufacturers VALUES (2,2);
INSERT INTO Component_manufacturers VALUES (3,3);

我想创建一个函数来创建一个具有特定结构的表，将表名的一部分作为参数传递，因此表名是 t_ 。与此类似：

CREATE OR REPLACE FUNCTION create_table_type1(t_name VARCHAR(30)) RETURNS VOID AS $$
BEGIN
    EXECUTE "CREATE TABLE IF NOT EXISTS t_"|| t_name ||"
    (
    id SERIAL,
    customerid INT,
    daterecorded DATE,
            value DOUBLE PRECISION,
    PRIMARY KEY (id)
    )"
END
$$ LANGUAGE plpgsql

然后这样称呼它：

SELECT create_table_type1('one');

可能吗？

我有一个三列的表，

• 序列号。
• 价值真实。
• timein 时间戳。

我想根据天数选择值，即。从两天前到现在。该表每天可能包含一到两行，如果它确实包含两行，我想选择第二行以及该时间段内跟随它的所有行。

澄清：

id | value | timein
1  | 20.5  | 2013-04-25 11:25:42
2  |  4.2  | 2013-04-26 09:10:42
3  |  3.1  | 2013-04-26 15:45:42
4  |  100  | 2013-04-27 14:52:42
5  | 15.5  | 2013-04-28 17:41:42
6  | 23.3  | 2013-04-29 07:32:42
7  | 21.4  | 2013-04-29 14:32:42

如果我想选择第 26 天（仅第二行）的值加上第 29 天之前的所有值等等，可以在一个查询中完成吗？