-- 创建 adi 状态、类型表
DROP TABLE IF EXISTS adi;
DROP SEQUENCE IF EXISTS adi;
CREATE SEQUENCE s_adi;
CREATE TABLE "public"."adi" (
"id" int4 NOT NULL DEFAULT nextval('ac_device_info'),
"device_sys_code" varchar(32) COLLATE "pg_catalog"."default" NOT NULL,
"device_name" varchar(128) COLLATE "pg_catalog"."default",
"device_type" int4 DEFAULT 0,
"device_status" int4 DEFAULT 0,
CONSTRAINT "adi_pkey" PRIMARY KEY ("id")
);
ALTER TABLE "public"."adi" OWNER TO "test";
COMMENT ON COLUMN "public"."adi"."id" IS '主键id';
COMMENT ON COLUMN "public"."adi"."device_sys_code" IS '编码';
COMMENT ON COLUMN "public"."adi"."device_name" IS '名称';
COMMENT ON COLUMN "public"."adi"."device_type" IS '类型:0 普通,1 特殊';
COMMENT ON COLUMN "public"."adi"."device_status" IS '状态:0 离线,1 在线';
COMMENT ON TABLE "public"."adi" IS '状态类、型表';
--创建视图 v_adis
DROP VIEW IF EXISTS v_adis CASCADE;
CREATE OR REPLACE VIEW "public"."v_adis" AS
SELECT
split_part(string_agg(concat(countInfo.device_count, ''), ',') , ',', 1) AS common_offline_count,-- 00数量
split_part(string_agg(concat(countInfo.device_count, ''), ',') , ',', 2) AS common_online_count, -- 01数量
split_part(string_agg(concat(countInfo.device_count, ''), ',') , ',', 3) AS face_offline_count, -- 10数量
split_part(string_agg(concat(countInfo.device_count, ''), ',') , ',', 4) AS face_online_count -- 11数量
FROM (
SELECT
concat(adi.device_type, adi.device_status) AS device_tt,
COUNT( * ) AS device_count
FROM
adi
GROUP BY
adi.device_type,
adi.device_status
ORDER BY device_tt
) countInfo;
ALTER TABLE "public"."v_adis" OWNER TO "test";
grant select on v_adis TO third_user;
PostgresSQL将多行转成一行多列
©著作权归作者所有,转载或内容合作请联系作者
- 文/潘晓璐 我一进店门,熙熙楼的掌柜王于贵愁眉苦脸地迎上来,“玉大人,你说我怎么就摊上这事。” “怎么了?”我有些...
- 文/花漫 我一把揭开白布。 她就那样静静地躺着,像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上,一...
- 文/苍兰香墨 我猛地睁开眼,长吁一口气:“原来是场噩梦啊……” “哼!你这毒妇竟也来了?” 一声冷哼从身侧响起,我...
推荐阅读更多精彩内容
- 每年到这个时候,身边就会有很多人开始咳嗽、咳痰、流鼻涕、打喷嚏的,有些是感冒、有些是哮喘、有些是气管炎,有些的鼻炎...
- 今天和大家分享一个小技巧,使用逻辑运算符搞定多行多列变成一行或一列,我们都知道Excel有一个“转置”功能,可以进...
- 1、表结构 2、movie.txt 3、创建表 hive 支持数组和字典,创建一个category数组列,用逗号分...