如何创建递增列? [英] How to create incrementing columns?
本文介绍了如何创建递增列?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有包含这些列名的表.
省/州国家/地区纬度长1/22/201/23/201/24/201/25/20......3/21/20我知道创建前 4 列，但我不知道如何创建日期列并增加它.
如何一次实现如此多的列?
谢谢！
感染
死了
恢复
我创建的对象关系数据模型
问题 -:提交适用于您的数据库架构的工作 Oracle 脚本.
解决方案
不要尝试每天创建一列；只需创建一个包含位置、日期和每个统计数据(即感染、恢复、死亡等)列的表，然后如果您需要在查询(或任何中间层应用程序 [即 PHP、Java、.net] 用于访问数据库).
类似于:
CREATE TABLE Regions(id VARCHAR2(6)约束区域__id__pk 主键，parent_id VARCHAR2(6)CONSTRAINT region_parent__fk REFERENCES 区域( id )，名称 VARCHAR2(50)约束区域__name__nn NOT NULL约束区域__name__u 唯一，纬度 NUMBER约束区域__lat__nn 非空，经度 NUMBER约束区域__long__nn 非空，约束区域__id__chk 检查(( parent_id 为 NULL AND REGEXP_LIKE( id, '^[A-Z]{2}$' ) )OR ( parent_id 不是 NULL AND REGEXP_LIKE( id, '^[A-Z]{2}-[A-Z0-9]{1,3}$' ) )));对 COLUMN Regions.id 的评论是ISO 3166-2 Alpha-2 国家代码或 ISO 3166-2 省代码"；对列的评论 Regions.name 是ISO 3166-2 英文简称."；对列区域的评论.纬度是该地区主要城市的纬度."；对列区域的评论.经度是该地区主要城市的经度."；CREATE TABLE Virus_Statistics(编号 (20,0)始终作为身份生成约束virus_statistics__id__pk PRIMARY KEY，位置 VARCHAR2(6)约束virus_statistics__loc__nn NOT NULL约束virus_statistics__loc__fk 参考区域(id)，日期时间约束virus_statistics__dt__nn NOT NULL约束virus_statistics__dt__chk 检查(日期时间= TRUNC(日期时间))，感染人数(10,0)，恢复 NUMBER(10,0),死亡人数(10,0)，约束virus_statistics__loc__dt__u UNIQUE ( location, datetime ));然后你就可以输入你的数据了.例如，区域将是:
INSERT INTO Regions ( id, parent_id, name, latitude, longitude )SELECT 'TH', NULL, 'Thailand', 15.00000, 101.00000 FROM DUAL UNION ALLSELECT 'JP', NULL, 'Japan', 36.00000, 138.00000 FROM DUAL UNION ALLSELECT 'SG', NULL, 'Singapore', 1.28333, 103.83333 FROM DUAL UNION ALLSELECT 'NP', NULL, 'Nepal', 28.16667, 84.25000 FROM DUAL UNION ALLSELECT 'MY', NULL, 'Malaysia', 2.50000, 112.50000 FROM DUAL UNION ALLSELECT 'CA', NULL, 'Canada', 45.42472, - 75.69500 FROM DUAL UNION ALL从 DUAL 中选择CA-BC"、CA"、不列颠哥伦比亚省"、48.40733、-123.32977；前 3 列数据为:
INSERT INTO Virus_Statistics(位置、日期时间、感染、恢复、死亡)SELECT 'TH', DATE '2020-01-22', 2 AS i, 0 AS r, 0 AS d from DUAL UNION ALLSELECT 'TH', DATE '2020-01-23', 3, 0, 0 from DUAL UNION ALLSELECT 'TH', DATE '2020-01-24', 5, 0, 0 from DUAL UNION ALLSELECT 'JP', DATE '2020-01-22', 2, 0, 0 from DUAL UNION ALLSELECT 'JP', DATE '2020-01-23', 1, 0, 0 from DUAL UNION ALLSELECT 'JP', DATE '2020-01-24', 2, 0, 0 from DUAL UNION ALLSELECT 'SG', DATE '2020-01-22', 0, 0, 0 from DUAL UNION ALLSELECT 'SG', DATE '2020-01-23', 1, 0, 0 from DUAL UNION ALLSELECT 'SG', DATE '2020-01-24', 3, 0, 0 from DUAL UNION ALLSELECT 'NP', DATE '2020-01-22', 0, 0, 0 from DUAL UNION ALLSELECT 'NP', DATE '2020-01-23', 0, 0, 0 from DUAL UNION ALLSELECT 'NP', DATE '2020-01-24', 0, 0, 0 from DUAL UNION ALLSELECT 'MY', DATE '2020-01-22', 0, 0, 0 from DUAL UNION ALLSELECT 'MY', DATE '2020-01-23', 0, 0, 0 from DUAL UNION ALLSELECT 'MY', DATE '2020-01-24', 0, 0, 0 from DUAL UNION ALLSELECT 'CA-BC', DATE '2020-01-22', 0, 0, 0 from DUAL UNION ALLSELECT 'CA-BC', DATE '2020-01-23', 0, 0, 0 from DUAL UNION ALLSELECT 'CA-BC', DATE '2020-01-24', 0, 0, 0 from DUAL;然后，如果您想将其输出为每天的列，请使用 PIVOT:
SELECT *从 (选择名称，纬度，经度，约会时间，已感染FROM Virus_Statistics v内部连接区域 rON ( r.id = v.location ))枢轴 (MAX(已感染)FOR 日期时间输入 (日期2020-01-22"为2020-01-22"，日期2020-01-23"为2020-01-23"，日期2020-01-24"为2020-01-24"))输出:
<块引用><前>姓名 |纬度 |经度 |2020-01-22 |2020-01-23 |2020-01-24:--------------- |-------: |---------: |---------: |---------: |---------:日本 |36 |138 |2 |1 |2马来西亚 |2.5 |112.5 |0 |0 |0新加坡 |1.28333 |103.83333 |0 |1 |3尼泊尔 |28.16667 |84.25 |0 |0 |0不列颠哥伦比亚省 |48.40733 |-123.32977 |0 |0 |0泰国 |15 |101 |2 |3 |5db<>fiddle 这里
I have table with these column names.
Province/State
Country/Region
Lat
Long
1/22/20
1/23/20
1/24/20
1/25/20
...
...
3/21/20
I know to create first 4 columns but I don't know how create date column and increment it.
How can I implement such number of columns at once?
Thank you!
Infected
Dead
Recovered
Object relational data model created by me
Question -: Submit working Oracle script for your database schema.
解决方案
Don't try to create a column-per-day; just create a table with columns for location, date and for each statistic (i.e. infected, recovered, dead, etc.) and then if you need to pivot them do that in a query (or in whatever middle-tier application [i.e. PHP, Java, .net] you're using to access the database).
Something like:
CREATE TABLE Regions(
id VARCHAR2(6)
CONSTRAINT regions__id__pk PRIMARY KEY,
parent_id VARCHAR2(6)
CONSTRAINT regions_parent__fk REFERENCES Regions ( id ),
name VARCHAR2(50)
CONSTRAINT regions__name__nn NOT NULL
CONSTRAINT regions__name__u UNIQUE,
latitude NUMBER
CONSTRAINT regions__lat__nn NOT NULL,
longitude NUMBER
CONSTRAINT regions__long__nn NOT NULL,
CONSTRAINT regions__id__chk CHECK (
( parent_id IS NULL AND REGEXP_LIKE( id, '^[A-Z]{2}$' ) )
OR ( parent_id IS NOT NULL AND REGEXP_LIKE( id, '^[A-Z]{2}-[A-Z0-9]{1,3}$' ) )
)
);
COMMENT ON COLUMN Regions.id IS 'ISO 3166-2 Alpha-2 Country Code or ISO 3166-2 Province Code';
COMMENT ON COLUMN Regions.name IS 'ISO 3166-2 English Short Name.';
COMMENT ON COLUMN Regions.latitude IS 'Latitude of the region''s main city.';
COMMENT ON COLUMN Regions.longitude IS 'Longitude of the region''s main city.';
CREATE TABLE Virus_Statistics(
id NUMBER(20,0)
GENERATED ALWAYS AS IDENTITY
CONSTRAINT virus_statistics__id__pk PRIMARY KEY,
location VARCHAR2(6)
CONSTRAINT virus_statistics__loc__nn NOT NULL
CONSTRAINT virus_statistics__loc__fk REFERENCES Regions ( id ),
datetime DATE
CONSTRAINT virus_statistics__dt__nn NOT NULL
CONSTRAINT virus_statistics__dt__chk CHECK ( datetime = TRUNC( datetime ) ),
infected NUMBER(10,0),
recovered NUMBER(10,0),
dead NUMBER(10,0),
CONSTRAINT virus_statistics__loc__dt__u UNIQUE ( location, datetime )
);
Then you can input your data. For example, the regions would be:
INSERT INTO Regions ( id, parent_id, name, latitude, longitude )
SELECT 'TH', NULL, 'Thailand', 15.00000, 101.00000 FROM DUAL UNION ALL
SELECT 'JP', NULL, 'Japan', 36.00000, 138.00000 FROM DUAL UNION ALL
SELECT 'SG', NULL, 'Singapore', 1.28333, 103.83333 FROM DUAL UNION ALL
SELECT 'NP', NULL, 'Nepal', 28.16667, 84.25000 FROM DUAL UNION ALL
SELECT 'MY', NULL, 'Malaysia', 2.50000, 112.50000 FROM DUAL UNION ALL
SELECT 'CA', NULL, 'Canada', 45.42472, - 75.69500 FROM DUAL UNION ALL
SELECT 'CA-BC', 'CA', 'British Columbia', 48.40733, -123.32977 FROM DUAL;
And the first 3 columns of data would be:
INSERT INTO Virus_Statistics ( location, datetime, infected, recovered, dead )
SELECT 'TH', DATE '2020-01-22', 2 AS i, 0 AS r, 0 AS d FROM DUAL UNION ALL
SELECT 'TH', DATE '2020-01-23', 3, 0, 0 FROM DUAL UNION ALL
SELECT 'TH', DATE '2020-01-24', 5, 0, 0 FROM DUAL UNION ALL
SELECT 'JP', DATE '2020-01-22', 2, 0, 0 FROM DUAL UNION ALL
SELECT 'JP', DATE '2020-01-23', 1, 0, 0 FROM DUAL UNION ALL
SELECT 'JP', DATE '2020-01-24', 2, 0, 0 FROM DUAL UNION ALL
SELECT 'SG', DATE '2020-01-22', 0, 0, 0 FROM DUAL UNION ALL
SELECT 'SG', DATE '2020-01-23', 1, 0, 0 FROM DUAL UNION ALL
SELECT 'SG', DATE '2020-01-24', 3, 0, 0 FROM DUAL UNION ALL
SELECT 'NP', DATE '2020-01-22', 0, 0, 0 FROM DUAL UNION ALL
SELECT 'NP', DATE '2020-01-23', 0, 0, 0 FROM DUAL UNION ALL
SELECT 'NP', DATE '2020-01-24', 0, 0, 0 FROM DUAL UNION ALL
SELECT 'MY', DATE '2020-01-22', 0, 0, 0 FROM DUAL UNION ALL
SELECT 'MY', DATE '2020-01-23', 0, 0, 0 FROM DUAL UNION ALL
SELECT 'MY', DATE '2020-01-24', 0, 0, 0 FROM DUAL UNION ALL
SELECT 'CA-BC', DATE '2020-01-22', 0, 0, 0 FROM DUAL UNION ALL
SELECT 'CA-BC', DATE '2020-01-23', 0, 0, 0 FROM DUAL UNION ALL
SELECT 'CA-BC', DATE '2020-01-24', 0, 0, 0 FROM DUAL;
Then if you want to output it as columns-per-day then use a PIVOT:
SELECT *
FROM (
SELECT name,
latitude,
longitude,
datetime,
infected
FROM Virus_Statistics v
INNER JOIN Regions r
ON ( r.id = v.location )
)
PIVOT (
MAX( infected )
FOR datetime IN (
DATE '2020-01-22' AS "2020-01-22",
DATE '2020-01-23' AS "2020-01-23",
DATE '2020-01-24' AS "2020-01-24"
)
)
Which outputs:
NAME | LATITUDE | LONGITUDE | 2020-01-22 | 2020-01-23 | 2020-01-24 :--------------- | -------: | ---------: | ---------: | ---------: | ---------: Japan | 36 | 138 | 2 | 1 | 2 Malaysia | 2.5 | 112.5 | 0 | 0 | 0 Singapore | 1.28333 | 103.83333 | 0 | 1 | 3 Nepal | 28.16667 | 84.25 | 0 | 0 | 0 British Columbia | 48.40733 | -123.32977 | 0 | 0 | 0 Thailand | 15 | 101 | 2 | 3 | 5
db<>fiddle here
这篇关于如何创建递增列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
