insert into 查询结果和插入数据有出入

首页版块访问AI主站注册发帖

简简单单XTlife 发布于2021-06 浏览:3310 回复:5

快速回复

我的select 语句能插入 1900多条数据，但是插入的时候只插了10条，我最后查询也只查到10条。这个是什么原因导致的？我建表也没做特殊设置。就是根据order_id，_dt做了唯一主键设置，但是我group by两个字段，数据也不止10条。

技术问答

个赞

共5条回复最后由简简单单XTlife回复于2021-06

#6简简单单XTlife回复于2021-06

加了set enable_insert_strict=true 这个之后找到原因了，某个字段不能为空导致为空的数据全被过滤了。

#5简简单单XTlife回复于2021-06

我查出来的条数是1961条但是插入之后只有10条

#4简简单单XTlife回复于2021-06

CREATE TABLE `dwd_dingjia_inf_user_vehicle_history_list` (
`order_id` varchar(25) NOT NULL COMMENT "",
`_dt` date NOT NULL COMMENT "",
`user_id` largeint(40) NULL COMMENT "",
`vc_order_id` varchar(60) NULL COMMENT "",
`mch_type` varchar(30) NULL COMMENT "",
`vin` varchar(40) NULL COMMENT "",
`brand_id` int(11) NULL COMMENT "",
`series_id` int(11) NULL COMMENT "",
`model_id` int(11) NULL COMMENT "",
`reg_date` date NOT NULL COMMENT "",
`mile_age` double NOT NULL COMMENT "",
`city_id` int(11) NULL COMMENT "",
`engine_no` varchar(500) NULL COMMENT "",
`query_status` tinyint(4) NULL COMMENT "",
`pay_status` tinyint(4) NULL COMMENT "",
`query_time` datetime NULL COMMENT "",
`update_time` datetime NULL COMMENT "",
`add_time` datetime NULL COMMENT "",
`is_deleted` tinyint(4) NULL COMMENT "",
`app_from` varchar(150) NULL COMMENT "",
`license_pic` varchar(600) NULL COMMENT "",
`model_name` varchar(150) NULL COMMENT "",
`show_time` datetime NULL COMMENT "",
`report_version` varchar(20) NULL COMMENT "",
`_create_time` datetime NULL COMMENT "doris入库时间"
) ENGINE=OLAP
UNIQUE KEY(`order_id`, `_dt`)
COMMENT "dwd_dingjia_inf_user_vehicle_history_list"
PARTITION BY RANGE(`_dt`)
(PARTITION m201312 VALUES [('0000-01-01'), ('2013-12-01')),
PARTITION m201401 VALUES [('2013-12-01'), ('2014-01-01')),
PARTITION m201402 VALUES [('2014-01-01'), ('2014-02-01')),
PARTITION m201403 VALUES [('2014-02-01'), ('2014-03-01')),
PARTITION m201404 VALUES [('2014-03-01'), ('2014-04-01')),
PARTITION m201405 VALUES [('2014-04-01'), ('2014-05-01')),
PARTITION m201406 VALUES [('2014-05-01'), ('2014-06-01')),
PARTITION m201407 VALUES [('2014-06-01'), ('2014-07-01')),
PARTITION m201408 VALUES [('2014-07-01'), ('2014-08-01')),
PARTITION m201409 VALUES [('2014-08-01'), ('2014-09-01')),
PARTITION m201410 VALUES [('2014-09-01'), ('2014-10-01')),
PARTITION m201411 VALUES [('2014-10-01'), ('2014-11-01')),
PARTITION m201412 VALUES [('2014-11-01'), ('2014-12-01')),
PARTITION m201501 VALUES [('2014-12-01'), ('2015-01-01')),
PARTITION m201502 VALUES [('2015-01-01'), ('2015-02-01')),
PARTITION m201503 VALUES [('2015-02-01'), ('2015-03-01')),
PARTITION m201504 VALUES [('2015-03-01'), ('2015-04-01')),
PARTITION m201505 VALUES [('2015-04-01'), ('2015-05-01')),
PARTITION m201506 VALUES [('2015-05-01'), ('2015-06-01')),
PARTITION m201507 VALUES [('2015-06-01'), ('2015-07-01')),
PARTITION m201508 VALUES [('2015-07-01'), ('2015-08-01')),
PARTITION m201509 VALUES [('2015-08-01'), ('2015-09-01')),
PARTITION m201510 VALUES [('2015-09-01'), ('2015-10-01')),
PARTITION m201511 VALUES [('2015-10-01'), ('2015-11-01')),
PARTITION m201512 VALUES [('2015-11-01'), ('2015-12-01')),
PARTITION m201601 VALUES [('2015-12-01'), ('2016-01-01')),
PARTITION m201602 VALUES [('2016-01-01'), ('2016-02-01')),
PARTITION m201603 VALUES [('2016-02-01'), ('2016-03-01')),
PARTITION m201604 VALUES [('2016-03-01'), ('2016-04-01')),
PARTITION m201605 VALUES [('2016-04-01'), ('2016-05-01')),
PARTITION m201606 VALUES [('2016-05-01'), ('2016-06-01')),
PARTITION m201607 VALUES [('2016-06-01'), ('2016-07-01')),
PARTITION m201608 VALUES [('2016-07-01'), ('2016-08-01')),
PARTITION m201609 VALUES [('2016-08-01'), ('2016-09-01')),
PARTITION m201610 VALUES [('2016-09-01'), ('2016-10-01')),
PARTITION m201611 VALUES [('2016-10-01'), ('2016-11-01')),
PARTITION m201612 VALUES [('2016-11-01'), ('2016-12-01')),
PARTITION m201701 VALUES [('2016-12-01'), ('2017-01-01')),
PARTITION m201702 VALUES [('2017-01-01'), ('2017-02-01')),
PARTITION m201703 VALUES [('2017-02-01'), ('2017-03-01')),
PARTITION m201704 VALUES [('2017-03-01'), ('2017-04-01')),
PARTITION m201705 VALUES [('2017-04-01'), ('2017-05-01')),
PARTITION m201706 VALUES [('2017-05-01'), ('2017-06-01')),
PARTITION m201707 VALUES [('2017-06-01'), ('2017-07-01')),
PARTITION m201708 VALUES [('2017-07-01'), ('2017-08-01')),
PARTITION m201709 VALUES [('2017-08-01'), ('2017-09-01')),
PARTITION m201710 VALUES [('2017-09-01'), ('2017-10-01')),
PARTITION m201711 VALUES [('2017-10-01'), ('2017-11-01')),
PARTITION m201712 VALUES [('2017-11-01'), ('2017-12-01')),
PARTITION m201801 VALUES [('2017-12-01'), ('2018-01-01')),
PARTITION m201802 VALUES [('2018-01-01'), ('2018-02-01')),
PARTITION m201803 VALUES [('2018-02-01'), ('2018-03-01')),
PARTITION m201804 VALUES [('2018-03-01'), ('2018-04-01')),
PARTITION m201805 VALUES [('2018-04-01'), ('2018-05-01')),
PARTITION m201806 VALUES [('2018-05-01'), ('2018-06-01')),
PARTITION m201807 VALUES [('2018-06-01'), ('2018-07-01')),
PARTITION m201808 VALUES [('2018-07-01'), ('2018-08-01')),
PARTITION m201809 VALUES [('2018-08-01'), ('2018-09-01')),
PARTITION m201810 VALUES [('2018-09-01'), ('2018-10-01')),
PARTITION m201811 VALUES [('2018-10-01'), ('2018-11-01')),
PARTITION m201812 VALUES [('2018-11-01'), ('2018-12-01')),
PARTITION m201901 VALUES [('2018-12-01'), ('2019-01-01')),
PARTITION m201902 VALUES [('2019-01-01'), ('2019-02-01')),
PARTITION m201903 VALUES [('2019-02-01'), ('2019-03-01')),
PARTITION m201904 VALUES [('2019-03-01'), ('2019-04-01')),
PARTITION m201905 VALUES [('2019-04-01'), ('2019-05-01')),
PARTITION m201906 VALUES [('2019-05-01'), ('2019-06-01')),
PARTITION m201907 VALUES [('2019-06-01'), ('2019-07-01')),
PARTITION m201908 VALUES [('2019-07-01'), ('2019-08-01')),
PARTITION m201909 VALUES [('2019-08-01'), ('2019-09-01')),
PARTITION m201910 VALUES [('2019-09-01'), ('2019-10-01')),
PARTITION m201911 VALUES [('2019-10-01'), ('2019-11-01')),
PARTITION m201912 VALUES [('2019-11-01'), ('2019-12-01')),
PARTITION m2010241 VALUES [('2019-12-01'), ('2020-01-01')),
PARTITION m2010242 VALUES [('2020-01-01'), ('2020-02-01')),
PARTITION m2010243 VALUES [('2020-02-01'), ('2020-03-01')),
PARTITION m2010244 VALUES [('2020-03-01'), ('2020-04-01')),
PARTITION m201021024 VALUES [('2020-04-01'), ('2020-05-01')),
PARTITION m2010246 VALUES [('2020-05-01'), ('2020-06-01')),
PARTITION m2010247 VALUES [('2020-06-01'), ('2020-07-01')),
PARTITION m2010248 VALUES [('2020-07-01'), ('2020-08-01')),
PARTITION m2010249 VALUES [('2020-08-01'), ('2020-09-01')),
PARTITION m202010 VALUES [('2020-09-01'), ('2020-10-01')),
PARTITION m202011 VALUES [('2020-10-01'), ('2020-11-01')),
PARTITION m202012 VALUES [('2020-11-01'), ('2020-12-01')),
PARTITION m202101 VALUES [('2020-12-01'), ('2021-01-01')),
PARTITION m202102 VALUES [('2021-01-01'), ('2021-02-01')),
PARTITION m202103 VALUES [('2021-02-01'), ('2021-03-01')),
PARTITION m202104 VALUES [('2021-03-01'), ('2021-04-01')),
PARTITION m202105 VALUES [('2021-04-01'), ('2021-05-01')),
PARTITION m202106 VALUES [('2021-05-01'), ('2021-06-01')),
PARTITION m202107 VALUES [('2021-06-01'), ('2021-07-01')),
PARTITION m202108 VALUES [('2021-07-01'), ('2021-08-01')))
DISTRIBUTED BY HASH(`order_id`) BUCKETS 10
PROPERTIES (
"replication_num" = "3",
"dynamic_partition.enable" = "true",
"dynamic_partition.time_unit" = "MONTH",
"dynamic_partition.time_zone" = "Asia/Shanghai",
"dynamic_partition.start" = "-2147483648",
"dynamic_partition.end" = "2",
"dynamic_partition.prefix" = "m",
"dynamic_partition.replication_num" = "3",
"dynamic_partition.buckets" = "10",
"dynamic_partition.start_day_of_month" = "1",
"in_memory" = "false",
"storage_format" = "V2"
);这个是我的建表语句

#3IamStrangers回复于2021-06

set enable_insert_strict=true 之后在执行insert 试下

#2qiliyazhe回复于2021-06

建表语句发一下？

快速回复

TOP

操作指南

常见问答

平台公告

经验交流

技术专区

文字识别

人脸识别

语音技术

PaddlePaddle

EasyDL

BML

EasyData

AI Studio

UNIT

人体分析

图像搜索

图像识别

内容审核

自然语言处理

机器人视觉

视频技术

增强现实

知识图谱

智能创作

智能呼叫中心

文心

EdgeBoard

DuerOS

EasyEdge

度目硬件

百度AI市场

Doris

AI赛事

百度之星大赛

AI Studio人工智能竞赛

语言与智能技术竞赛

千言数据集

集思广益

共享工具

头脑风暴

成果展示

智能客服