insert into 查询结果和插入数据有出入
简简单单XTlife 发布于2021-06 浏览:1078 回复:5
0
收藏

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

收藏
点赞
0
个赞
共5条回复 最后由简简单单XTlife回复于2021-06
#6简简单单XTlife回复于2021-06

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

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

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

0
#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"
);这个是我的建表语句

0
#3IamStrangers回复于2021-06

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

1
#2qiliyazhe回复于2021-06

建表语句发一下?

0
快速回复
TOP
切换版块