longxianghui / chimp Goto Github PK

sql语句多个order by问题，比如 SELECT * FROM t ORDER BY t.Id DESC ,T.Name ASC 就有问题 找不到 ORDER BY

业务都需要很多的DB，能改改成支持多DB的呢？不同的DbContext连不同的DB。

SQL:
select o.id,o.store_name storeName,o.cover_avatar coverAvatar,u.nick_name userNickName,u.avatar userAvatar,o.sales,t.count,o.create_time createTime, (select count(1) from user_trade_order_detail uto where uto.goods_id=o.id from goods o INNER JOIN (select goods_id,name,COUNT(1) count from goods_browse_history g where goods_id <> '1429622205922283520' GROUP BY g.goods_id,g.name ORDER BY count desc) t on o.id=t.goods_id INNER JOIN user_info u ON u.id=o.user_id;

SQL分割后：
Raw:
select o.id,o.store_name storeName,o.cover_avatar coverAvatar,u.nick_name userNickName,u.avatar userAvatar,o.sales,t.count,o.create_time createTime, (select count(1) from user_trade_order_detail uto where uto.goods_id=o.id from goods o INNER JOIN (select goods_id,name,COUNT(1) count from goods_browse_history g where goods_id <> '1429622205922283520' GROUP BY g.goods_id,g.name ORDER BY count desc) t on o.id=t.goods_id INNER JOIN user_info u ON u.id=o.user_id;

Body:
user_trade_order_detail uto where uto.goods_id=o.id from goods o INNER JOIN (select goods_id,name,COUNT(1) count from goods_browse_history g where goods_id <> '1429622205922283520' GROUP BY g.goods_id,g.name ORDER BY count desc) t on o.id=t.goods_id INNER JOIN user_info u ON u.id=o.user_id;

Select:
o.id,o.store_name storeName,o.cover_avatar coverAvatar,u.nick_name userNickName,u.avatar userAvatar,o.sales,t.count,o.create_time createTime, (select count(1) OrderBy: null

报错sql：
SELECT COUNT(*) FROM user_trade_order_detail uto where uto.goods_id=o.id from goods o INNER JOIN (select goods_id,name,COUNT(1) count from goods_browse_history g where goods_id <> '1429622205922283520' GROUP BY g.goods_id,g.name ORDER BY count desc) t on o.id=t.goods_id INNER JOIN user_info u ON u.id=o.user_id;

报错信息及堆栈：
Leo.Chimp.Test.DapperTest.QueryPagedListAsyncByMe
源: DapperTest.cs 行 45
持续时间: 3.5 分钟

消息:
MySql.Data.MySqlClient.MySqlException : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'from goods o INNER JOIN (select goods_id,name,COUNT(1) count from goods_browse_h' at line 1
---- MySql.Data.MySqlClient.MySqlException : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'from goods o INNER JOIN (select goods_id,name,COUNT(1) count from goods_browse_h' at line 1

堆栈跟踪:
MySqlDataReader.ActivateResultSet(ResultSet resultSet) 行 80
MySqlDataReader.ReadFirstResultSetAsync(IOBehavior ioBehavior) 行 302
MySqlDataReader.CreateAsync(MySqlCommand command, CommandBehavior behavior, ResultSetProtocol resultSetProtocol, IOBehavior ioBehavior) 行 287
TextCommandExecutor.ExecuteReaderAsync(String commandText, MySqlParameterCollection parameterCollection, CommandBehavior behavior, IOBehavior ioBehavior, CancellationToken cancellationToken) 行 37
MySqlCommand.ExecuteScalarAsync(IOBehavior ioBehavior, CancellationToken cancellationToken) 行 281
SqlMapper.ExecuteScalarImplAsync[T](IDbConnection cnn, CommandDefinition command) 行 1247
UnitOfWork.QueryPagedListAsync[TEntity](Int32 pageIndex, Int32 pageSize, String pageSql, Object pageSqlArgs) 行 81
DapperTest.QueryPagedListAsyncByMe() 行 47
--- End of stack trace from previous location where exception was thrown ---
----- Inner Stack Trace -----
ResultSet.ReadResultSetHeaderAsync(IOBehavior ioBehavior) 行 42

数据库结构：
`SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- Table structure for goods

DROP TABLE IF EXISTS goods;
CREATE TABLE goods (
id bigint(20) NOT NULL COMMENT '编号',
user_id bigint(20) NULL DEFAULT NULL COMMENT '用户编号',
store_name varchar(150) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '商品名称',
price decimal(8, 2) UNSIGNED NULL DEFAULT NULL COMMENT '商品价格',
cate_id int(11) NULL DEFAULT NULL COMMENT '商品分类id',
stock mediumint(9) NULL DEFAULT 0 COMMENT '库存',
cover_avatar varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '商品封面图片',
slider_image varchar(2000) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '轮播图',
introduction varchar(300) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '商品描述',
keyword varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '关键字',
vip_price decimal(8, 2) UNSIGNED NULL DEFAULT NULL COMMENT '会员价格',
ot_price decimal(8, 2) UNSIGNED NULL DEFAULT NULL COMMENT '市场价',
limit_price decimal(8, 2) NULL DEFAULT NULL COMMENT '限价',
postage decimal(8, 2) UNSIGNED NULL DEFAULT NULL COMMENT '邮费',
insurance_service_price decimal(10, 2) NULL DEFAULT NULL COMMENT '保险服务价格',
unit_name varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '单位名',
sort int(6) NULL DEFAULT 0 COMMENT '排序',
sales mediumint(9) NULL DEFAULT 0 COMMENT '销量',
diy_tags varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '1' COMMENT '商品自定义标签',
is_hot int(1) NULL DEFAULT 0 COMMENT '是否热卖',
is_benefit int(1) NULL DEFAULT 0 COMMENT '是否优惠',
is_best int(1) NULL DEFAULT 0 COMMENT '是否精品',
is_new int(1) NULL DEFAULT 0 COMMENT '是否新品',
description text CHARACTER SET utf8 COLLATE utf8_general_ci NULL COMMENT '产品描述',
is_postage int(1) NULL DEFAULT 0 COMMENT '是否包邮/是否自提',
postage_name varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '如果选择的是自提，必须填写自提名称',
postage_address varchar(120) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '自提的详细地址',
business_hours varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '营业时间',
service_selection varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '服务的选择，如果多个以竖线隔开',
insurance_service int(255) NULL DEFAULT NULL COMMENT '是否开启保险服务0未开启；1已开启',
tag_id varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '商品标签，如果多个以竖线隔开',
is_presell int(255) NULL DEFAULT NULL COMMENT '是否是预售,0非预售；1预售',
presell_desc varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '如果选择的是预售，必须填写预发货时间，商品情况描述',
mer_use int(1) NULL DEFAULT 0 COMMENT '商户是否代理 0不可代理1可代理',
give_integral decimal(8, 2) UNSIGNED NULL DEFAULT NULL COMMENT '获得积分',
cost decimal(8, 2) UNSIGNED NULL DEFAULT NULL COMMENT '成本价',
is_seckill int(1) NULL DEFAULT 0 COMMENT '秒杀状态 0 未开启 1已开启',
is_bargain int(1) NULL DEFAULT NULL COMMENT '砍价状态 0未开启 1开启',
virtual_sales int(9) NULL DEFAULT 100 COMMENT '虚拟销量',
browse int(11) NULL DEFAULT 0 COMMENT '浏览量',
file_type varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '1图片；2视频；5缩略图',
video_url varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '商品信息-视频路径',
picture_url varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '商品信息-图片路径',
detail_video_url varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '详情介绍-视频路径',
detail_picture_url varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '详情介绍-图片路径',
shelves_status int(11) NULL DEFAULT NULL COMMENT '是否上架：0未上架；1已上架',
status int(255) NULL DEFAULT NULL COMMENT '审核状态：0审核中；1审核通过；2审核未通过',
search_count int(11) NULL DEFAULT 0 COMMENT '查看人次',
province varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '省',
city varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '市',
district varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '区',
address varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '详细地址',
longitude varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '经度',
latitude varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '纬度',
remark varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '备注',
create_time datetime(0) NULL DEFAULT NULL COMMENT '创建时间',
createby varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '创建人',
modify_time datetime(0) NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '修改时间',
modifyby varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '修改人',
isdelete int(11) NOT NULL COMMENT '是否删除：0未删除；1已删除',
PRIMARY KEY (id) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '商品信息表' ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- Table structure for goods_browse_history

DROP TABLE IF EXISTS goods_browse_history;
CREATE TABLE goods_browse_history (
id bigint(20) NOT NULL COMMENT '编号',
user_id bigint(20) NULL DEFAULT NULL COMMENT '用户编号',
goods_id bigint(20) NULL DEFAULT NULL COMMENT '商品Id',
name varchar(150) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '商品名称',
type_code tinyint(3) NULL DEFAULT NULL COMMENT '商品类型 0：农场 1：牧场 2自产自销',
remark varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '备注',
create_time datetime(0) NULL DEFAULT NULL COMMENT '创建时间',
createby varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '创建人',
modify_time datetime(0) NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '修改时间',
modifyby varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '修改人',
isdelete int(11) NOT NULL COMMENT '是否删除：0未删除；1已删除',
PRIMARY KEY (id) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '用户浏览商品记录表' ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- Table structure for user_trade_order

DROP TABLE IF EXISTS user_trade_order;
CREATE TABLE user_trade_order (
id bigint(20) NOT NULL COMMENT '编号',
user_id bigint(20) NOT NULL COMMENT '用户编号',
type_code tinyint(4) NULL DEFAULT 0 COMMENT '类型：0田家订养；1自产自销',
order_number varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '订单编号',
trade_order_number varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '第三方订单号',
total_amount decimal(11, 2) NOT NULL COMMENT '订单总金额，包括运费',
real_total_amount decimal(10, 2) NULL DEFAULT NULL COMMENT '实际订单总金额,进行各种折扣之后的金额',
order_status tinyint(4) UNSIGNED ZEROFILL NULL DEFAULT 0002 COMMENT '-3用户拒收；-2待付款订单；-1用户取消；0待发货；1已发货；2用户确认收货',
pay_from tinyint(4) NULL DEFAULT 0 COMMENT ' 支付来源：0支付宝；1微信；2银联；3余额',
pay_status tinyint(4) NULL DEFAULT 0 COMMENT '支付状态，0未付款；1已付款；2线下付款；3线下付款已收款；',
pay_period int(11) NULL DEFAULT NULL COMMENT '订单支付有效期(单位：分钟)，默认15分钟',
refunds_period int(11) NULL DEFAULT NULL COMMENT '订单退款有效期(单位：小时)，默认8小时',
pay_date datetime(0) NULL DEFAULT NULL COMMENT '付款时间',
deliver_type tinyint(4) NULL DEFAULT 0 COMMENT '收货方式，0送货上门；1自提',
deliver_amount decimal(11, 2) NULL DEFAULT NULL COMMENT '运费',
pay_type tinyint(4) NULL DEFAULT 0 COMMENT '支付方式,0货到付款；1在线支付',
user_name varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '收货人姓名',
user_address varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '收件人地址',
user_phone varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '收件人手机号码',
order_score int(11) NULL DEFAULT NULL COMMENT '所得积分',
is_invoice tinyint(4) NULL DEFAULT 0 COMMENT '是否需要发票，0不需要；1需要',
invoice_client varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '发票抬头',
order_remarks varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '订单备注',
order_src tinyint(4) NULL DEFAULT 0 COMMENT '订单来源：0商城 1微信 2手机版 3安卓App 4苹果App 5小程序',
is_refund tinyint(4) NULL DEFAULT 0 COMMENT '是否退款：0否；1是',
is_appraise tinyint(4) NULL DEFAULT 0 COMMENT '0未点评；1已点评',
cancel_reason bigint(20) NULL DEFAULT NULL COMMENT '取消原因id',
reject_reason bigint(20) NULL DEFAULT NULL COMMENT '拒收原因id',
is_closed tinyint(4) NULL DEFAULT NULL COMMENT '是否订单已完结，0未完结；1已完结',
receive_time datetime(0) NULL DEFAULT NULL COMMENT '收货时间',
delivery_time datetime(0) NULL DEFAULT NULL COMMENT '发货时间',
express_id int(11) NULL DEFAULT NULL COMMENT '快递公司ID',
express_no varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '快递号',
trade_no varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '在线支付交易流水',
data_flag tinyint(4) NULL DEFAULT NULL COMMENT '订单是否有效：-1无效；1有效\r\n如果订单在规定时间内未付款，系统自动设置成无效',
order_type tinyint(4) NULL DEFAULT 0 COMMENT '订单类型：0实物订单；1非实物订单',
score_amount decimal(11, 2) NULL DEFAULT NULL COMMENT '积分抵扣金额',
use_score int(11) NULL DEFAULT NULL COMMENT '使用的积分',
notice_deliver tinyint(4) NULL DEFAULT 0 COMMENT '提醒发货。0未提醒；1已提醒',
invoice_json text CHARACTER SET utf8 COLLATE utf8_general_ci NULL COMMENT '发票信息json',
user_coupon_id bigint(20) NULL DEFAULT NULL COMMENT '所使用的优惠券ID',
user_coupon_Json text CHARACTER SET utf8 COLLATE utf8_general_ci NULL COMMENT '所使用的优惠券记录json',
trade_type int(11) NULL DEFAULT NULL COMMENT '交易类型',
remark varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '备注',
create_time datetime(0) NULL DEFAULT NULL COMMENT '创建时间',
createby varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '创建人',
modify_time datetime(0) NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '修改时间',
modifyby varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '修改人',
isdelete int(11) NOT NULL COMMENT '是否删除：0未删除；1已删除',
baseVersion int(11) NULL DEFAULT NULL COMMENT '数据更新版本，控制并发',
PRIMARY KEY (id) USING BTREE,
INDEX order_number(order_number) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '用户订单信息表' ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- Table structure for user_trade_order_detail

DROP TABLE IF EXISTS user_trade_order_detail;
CREATE TABLE user_trade_order_detail (
id bigint(20) NOT NULL,
order_number varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '订单编号',
goods_id bigint(20) NULL DEFAULT NULL COMMENT '商品编号',
goods_name varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '商品名称',
goods_type tinyint(4) NULL DEFAULT 0 COMMENT '商品类型：0实物商品；1虚拟商品',
goods_num int(11) NULL DEFAULT NULL COMMENT '商品数量',
goods_price decimal(12, 2) NULL DEFAULT NULL COMMENT '商品价格',
is_goods_exists int(255) NULL DEFAULT NULL COMMENT '商品是否有效,0无效；1有效',
discount_rate int(11) NULL DEFAULT NULL COMMENT '折扣',
discount_amount decimal(12, 2) NULL DEFAULT NULL COMMENT '折扣金额',
pay_amount decimal(12, 2) NULL DEFAULT NULL COMMENT '支付金额（总金额）',
real_amount decimal(12, 2) NULL DEFAULT NULL COMMENT '实际支付金额',
delivery_money decimal(12, 2) NULL DEFAULT NULL COMMENT '配送费用',
delivery_receive_time datetime(0) NULL DEFAULT NULL COMMENT '配送人员收货时间',
delivery_finish_time datetime(0) NULL DEFAULT NULL COMMENT '配送人员完成时间',
seller_finish_time datetime(0) NULL DEFAULT NULL COMMENT '卖家完成时间',
buyer_finish_time datetime(0) NULL DEFAULT NULL COMMENT '买家完成时间',
goods_spec_id varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '商品-规格ID',
goods_spec_names varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '商品-规格值',
status int(11) NULL DEFAULT NULL COMMENT '废弃状态,0未支付；1支付成功；2支付失败',
content varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '内容',
extra_json text CHARACTER SET utf8 COLLATE utf8_general_ci NULL COMMENT '商品附件信息-一般用来记录虚拟商品卡券或则其他插件信息',
remark varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '备注',
create_time datetime(0) NULL DEFAULT NULL COMMENT '创建时间',
createby varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '创建人',
modify_time datetime(0) NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '修改时间',
modifyby varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '修改人',
isdelete int(11) NOT NULL COMMENT '是否删除：0未删除；1已删除',
baseVersion int(11) NULL DEFAULT NULL COMMENT '数据更新版本，控制并发',
PRIMARY KEY (id) USING BTREE,
INDEX ordernumber(order_number) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '用户订单详情信息表' ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;
`