热门搜索
分布式
数据库一体机
云数据库
建设银行
保险核心
密集交易
登录
注册
sql语句报错，请大神指导
Dieter
2021/02/07
1922
9
为提高效率，提问时请提供以下信息，问题描述清晰可优先响应。
【DM版本】:
【操作系统】：
【CPU】:
【问题描述】*：
回答 0
暂无回答
Dieter
这是sql
SELECT a.MONTH as date, SUM(b.total) AS count
FROM (
SELECT DATE_FORMAT(create_date, '%Y-%m') AS MONTH, SUM(sum) AS total
FROM (
SELECT create_date, DATE_FORMAT(create_date, '%Y-%m') AS MONTH
, COUNT(*) AS sum
FROM dmp_device_model
GROUP BY MONTH
) ddm
GROUP BY MONTH
) a
JOIN (
SELECT DATE_FORMAT(create_date, '%Y-%m') AS MONTH, SUM(sum) AS total
FROM (
SELECT create_date, DATE_FORMAT(create_date, '%Y-%m') AS MONTH
, COUNT(*) AS sum
FROM dmp_device_model
GROUP BY MONTH
) ddm
GROUP BY MONTH
) b
ON a.MONTH >= b.MONTH
GROUP BY a.MONTH
ORDER BY a.MONTH;
发布于
2021/02/07 13:23
Dieter
表：
insert into "SYSDBA"."DMP_DEVICE_MODEL"("ID", "NAME", "DEVICE_MAKER_ID", "DEVICE_MAKER_NAME", "DEVICE_TYPE_ID", "DEVICE_TYPE_NAME", "NETWORK_SYSTEM", "AGREEMENT_TYPE", "CREATER", "CREATE_DATE", "UPDATER", "UPDATE_DATE", "PRODUCT_KEY", "PRODUCT_SECRET", "OBJECT_LINKS", "DOWNLINK_PATH")
VALUES(<"ID", BIGINT>, <"NAME", VARCHAR(30)>, <"DEVICE_MAKER_ID", BIGINT>, <"DEVICE_MAKER_NAME", VARCHAR(30)>, <"DEVICE_TYPE_ID", BIGINT>, <"DEVICE_TYPE_NAME", VARCHAR(30)>, <"NETWORK_SYSTEM", VARCHAR(20)>, <"AGREEMENT_TYPE", VARCHAR(20)>, <"CREATER", VARCHAR(30)>, <"CREATE_DATE", TIMESTAMP(0)>, <"UPDATER", VARCHAR(30)>, <"UPDATE_DATE", TIMESTAMP(0)>, <"PRODUCT_KEY", VARCHAR(255)>, <"PRODUCT_SECRET", VARCHAR(255)>, <"OBJECT_LINKS", VARCHAR(50)>, <"DOWNLINK_PATH", VARCHAR(50)>);
发布于
2021/02/07 13:25
myth8860
建表语句也提供一下
发布于
2021/02/07 13:43
Dieter
insert into "SYSDBA"."DMP_DEVICE_MODEL" ("ID","NAME","DEVICE_MAKER_ID","DEVICE_MAKER_NAME","DEVICE_TYPE_ID","DEVICE_TYPE_NAME","NETWORK_SYSTEM","AGREEMENT_TYPE","CREATER","CREATE_DATE","UPDATER","UPDATE_DATE","PRODUCT_KEY","PRODUCT_SECRET","OBJECT_LINKS","DOWNLINK_PATH") values (255, 'PM2125', 135, 'SCHNEIDER', 149, '电表', 'NB', 'MQTT', '杨广兴', '2020-06-30 16:15:41', '杨广兴', '2020-06-30 16:15:41', '1a3f9599f859ede791270f24242ba14fd6c9476dd3248f5dc9ee2c3cbb777bc6', 'd69f505e68246d47bbb496e8e3b12d215bd599b507233f50f7ebcd5c76279343', '', null);
insert into "SYSDBA"."DMP_DEVICE_MODEL" ("ID","NAME","DEVICE_MAKER_ID","DEVICE_MAKER_NAME","DEVICE_TYPE_ID","DEVICE_TYPE_NAME","NETWORK_SYSTEM","AGREEMENT_TYPE","CREATER","CREATE_DATE","UPDATER","UPDATE_DATE","PRODUCT_KEY","PRODUCT_SECRET","OBJECT_LINKS","DOWNLINK_PATH") values (261, 'MCCB', 137, 'MCCB', 151, '断路器', 'NB', 'MQTT', '杨广兴', '2020-07-01 15:34:41', '杨广兴', '2020-07-01 15:34:41', 'd4a310ea4439d8436831b235529d1e909955dec55092b9ab4afa2cf0446cc852', 'b35a1e5eccb6f682784efc5a0148526619cc8deeed7aa8fa9e070666665904c4', '', null);
insert into "SYSDBA"."DMP_DEVICE_MODEL" ("ID","NAME","DEVICE_MAKER_ID","DEVICE_MAKER_NAME","DEVICE_TYPE_ID","DEVICE_TYPE_NAME","NETWORK_SYSTEM","AGREEMENT_TYPE","CREATER","CREATE_DATE","UPDATER","UPDATE_DATE","PRODUCT_KEY","PRODUCT_SECRET","OBJECT_LINKS","DOWNLINK_PATH") values (265, 'HCRK-DMG03', 141, 'HUACHUANGRUIKE', 155, '门磁', 'NB', 'LWM2M', '杨广兴', '2020-07-02 14:43:30', '杨广兴', '2020-07-02 14:43:30', '(null)', '(null)', '19/0/0', null);
insert into "SYSDBA"."DMP_DEVICE_MODEL" ("ID","NAME","DEVICE_MAKER_ID","DEVICE_MAKER_NAME","DEVICE_TYPE_ID","DEVICE_TYPE_NAME","NETWORK_SYSTEM","AGREEMENT_TYPE","CREATER","CREATE_DATE","UPDATER","UPDATE_DATE","PRODUCT_KEY","PRODUCT_SECRET","OBJECT_LINKS","DOWNLINK_PATH") values (267, 'CU-RW116', 143, '零零红外', 157, '红外', 'NB', 'LWM2M', '杨广兴', '2020-07-02 16:28:41', '杨广兴', '2020-07-14 16:42:02', '(null)', '(null)', '19/0/0', null);
insert into "SYSDBA"."DMP_
发布于
2021/02/07 13:54
Dieter
大神？有结果了吗
发布于
2021/02/07 14:11
Dieter
CREATE TABLE "SYSDBA"."DMP_DEVICE_MODEL"
(
"ID" BIGINT IDENTITY(360, 1) NOT NULL,
"NAME" VARCHAR(30) NOT NULL,
"DEVICE_MAKER_ID" BIGINT NOT NULL,
"DEVICE_MAKER_NAME" VARCHAR(30) NOT NULL,
"DEVICE_TYPE_ID" BIGINT NOT NULL,
"DEVICE_TYPE_NAME" VARCHAR(30) NOT NULL,
"NETWORK_SYSTEM" VARCHAR(20) NOT NULL,
"AGREEMENT_TYPE" VARCHAR(20) NOT NULL,
"CREATER" VARCHAR(30) NOT NULL,
"CREATE_DATE" TIMESTAMP(0) DEFAULT CURRENT_TIMESTAMP() NOT NULL,
"UPDATER" VARCHAR(30) NOT NULL,
"UPDATE_DATE" TIMESTAMP(0) DEFAULT CURRENT_TIMESTAMP(),
"PRODUCT_KEY" VARCHAR(255),
"PRODUCT_SECRET" VARCHAR(255),
"OBJECT_LINKS" VARCHAR(50) DEFAULT '',
"DOWNLINK_PATH" VARCHAR(50),
NOT CLUSTER PRIMARY KEY("ID")) STORAGE(ON "MAIN", CLUSTERBTR) ;
COMMENT ON TABLE "SYSDBA"."DMP_DEVICE_MODEL" IS '设备型号';
COMMENT ON COLUMN "SYSDBA"."DMP_DEVICE_MODEL"."AGREEMENT_TYPE" IS '协议类型：Coap，LWM2M,MQTT,UDP,TCP';
COMMENT ON COLUMN "SYSDBA"."DMP_DEVICE_MODEL"."CREATER" IS '创建人';
COMMENT ON COLUMN "SYSDBA"."DMP_DEVICE_MODEL"."CREATE_DATE" IS '创建时间';
COMMENT ON COLUMN "SYSDBA"."DMP_DEVICE_MODEL"."DEVICE_MAKER_ID" IS '厂商id';
COMMENT ON COLUMN "SYSDBA"."DMP_DEVICE_MODEL"."DEVICE_MAKER_NAME" IS '厂商名称';
COMMENT ON COLUMN "SYSDBA"."DMP_DEVICE_MODEL"."DEVICE_TYPE_ID" IS '设备类型id';
COMMENT ON COLUMN "SYSDBA"."DMP_DEVICE_MODEL"."DEVICE_TYPE_NAME" IS '设备类型';
COMMENT ON COLUMN "SYSDBA"."DMP_DEVICE_MODEL"."DOWNLINK_PATH" IS '下行路径';
COMMENT ON COLUMN "SYSDBA"."DMP_DEVICE_MODEL"."ID" IS '主键';
COMMENT ON COLUMN "SYSDBA"."DMP_DEVICE_MODEL"."NAME" IS '模型名称';
COMMENT ON COLUMN "SYSDBA"."DMP_DEVICE_MODEL"."NETWORK_SYSTEM" IS '网络制式：NB，2G，3G，4G，5G，wifi，lora，有线，其他';
COMMENT ON COLUMN "SYSDBA"."DMP_DEVICE_MODEL"."OBJECT_LINKS" IS 'lwm2m设备注册的资源路径地址';
COMMENT ON COLUMN "SYSDBA"."DMP_DEVICE_MODEL"."PRODUCT_KEY" IS '产品key';
COMMENT ON COLUMN "SYSDBA"."DMP_DEVICE_MODEL"."PRODUCT_SECRET" IS '产品密钥';
COMMENT ON COLUMN "SYSDBA"."DMP_DEVICE_MODEL"."UPDATER" IS '更新人';
COMMENT ON COLUMN "SYSDBA"."DMP_DEVICE_MODEL"."UPDATE_DATE" IS '更新时间';
发布于
2021/02/07 15:01
Dieter
CREATE INDEX “DEVICE_TYPE_NAME” ON “SYSDBA”.“DMP_DEVICE_MODEL”(“DEVICE_TYPE_NAME” ASC) STORAGE(ON “MAIN”, CLUSTERBTR) ;
CREATE INDEX “MAKER_NAME” ON “SYSDBA”.“DMP_DEVICE_MODEL”(“DEVICE_MAKER_NAME” ASC) STORAGE(ON “MAIN”, CLUSTERBTR) ;
CREATE INDEX “DEVICE_TYPE_ID” ON “SYSDBA”.“DMP_DEVICE_MODEL”(“DEVICE_TYPE_ID” ASC) STORAGE(ON “MAIN”, CLUSTERBTR) ;
CREATE INDEX “MAKER_ID” ON “SYSDBA”.“DMP_DEVICE_MODEL”(“DEVICE_MAKER_ID” ASC) STORAGE(ON “MAIN”, CLUSTERBTR) ;
CREATE INDEX “PRODUCT_KEY_INDEX” ON “SYSDBA”.“DMP_DEVICE_MODEL”(“PRODUCT_KEY” ASC) STORAGE(ON “MAIN”, CLUSTERBTR) ;
CREATE INDEX “INDEX1118419380247999” ON “SYSDBA”.“DMP_DEVICE_MODEL”(“NAME” ASC) STORAGE(ON “MAIN”, CLUSTERBTR) ;
CREATE UNIQUE INDEX “INDEX1118419385762000” ON “SYSDBA”.“DMP_DEVICE_MODEL”(“ID” ASC) STORAGE(ON “MAIN”, CLUSTERBTR) ;
发布于
2021/02/07 15:01
myth8860
1.修改dm.ini COMPATIBLE_MODE = 4
2.修改SQL语句
SELECT a.MONTH as date, SUM(b.total) AS count
FROM (
SELECT DATE_FORMAT(create_date, '%Y-%m') AS MONTH, SUM(sum) AS total
FROM (
SELECT create_date, DATE_FORMAT(create_date, '%Y-%m') AS MONTH
, COUNT(*) AS sum
FROM dmp_device_model
-- GROUP BY MONTH
GROUP BY DATE_FORMAT(create_date, '%Y-%m')
) ddm
GROUP BY MONTH
) a
JOIN (
SELECT DATE_FORMAT(create_date, ‘%Y-%m’) AS MONTH, SUM(sum) AS total
FROM (
SELECT create_date, DATE_FORMAT(create_date, ‘%Y-%m’) AS MONTH
, COUNT(*) AS sum
FROM dmp_device_model
GROUP BY MONTH
) ddm
– GROUP BY MONTH
GROUP BY DATE_FORMAT(create_date, ‘%Y-%m’)
) b
ON a.MONTH >= b.MONTH
GROUP BY a.MONTH
ORDER BY a.MONTH;
发布于
2021/02/07 15:45
扫一扫
联系客服
联系客服
鄂Copyright © 达梦国产化应用创新实验室
达梦在线服务平台社区用户管理规范
鄂ICP备18017926号-1
达梦在线服务平台社区用户管理规范
鄂ICP备18017926号-1
表定义和sql语句发出来，图没法重现