订单表orderTable
id:主键,订单流水id
customerId:下订单的客户id,客户表Id的外键订单商品表ordergoods
id:主键
orderId:订单表id的外键
goodsId:商品表id的外键
count:商品数量商品表goods:
id:主键
name:商品名
price:单价客户表customer:
id:主键
name:客户名
address:客户地址诸表建表语句
create table customer(
id int(10) primary key not null,
name VARCHAR(255),
address VARCHAR(255)
) create table goods(
id int(10) primary key not null,
name VARCHAR(255),
price DOUBLE(10,2)
) create table orderTable(
id int(10) primary key not null,
customerid int(10) not null,
foreign key(customerid) references customer(id)
)create table ordergoods(
id int(10) primary key not null,
orderid int(10) not null,
goodsid int(10) not null,
count int(10),
foreign key(orderid) references orderTable(id),
foreign key(goodsid) references goods(id)
)插值语句
insert into customer ( id, name, address ) values ( '1', '张三', '北京' )
insert into customer ( id, name, address ) values ( '2', '李四', '大连' )
insert into customer ( id, name, address ) values ( '3', '王五', '上海' ) insert into goods ( id, name, price ) values ( '11', '上衣', '240' )
insert into goods ( id, name, price ) values ( '12', '裤子', '300' )
insert into goods ( id, name, price ) values ( '13', '鞋子', '350' ) insert into ordertable ( id, customerid ) values ( '111', '1' )
insert into ordertable ( id, customerid ) values ( '112', '2' )
insert into ordertable ( id, customerid ) values ( '113', '3' ) insert into ordergoods ( id, orderid, goodsid, count ) values ( '1111', '111', '11', '20' )
insert into ordergoods ( id, orderid, goodsid, count ) values ( '1112', '112', '11', '10' )
insert into ordergoods ( id, orderid, goodsid, count ) values ( '1113', '112', '12', '15' )
insert into ordergoods ( id, orderid, goodsid, count ) values ( '1114', '113', '11', '30' )
insert into ordergoods ( id, orderid, goodsid, count ) values ( '1115', '113', '12', '45' )
insert into ordergoods ( id, orderid, goodsid, count ) values ( '1116', '113', '13', '60' )====================================================
查询订单总价值 请问高手们这里的执行语句有什么优化的地方?逻辑读取,扫描计数太多了吧select t01.orderId,t01.customerName,t02.total from
(select
concat(customer.address,'商户',customer.name) as customerName,
orderTable.id as orderId
from
orderTable,
customer
where
ordertable.customerid=customer.id) t01,
(select orderid,sum(ordergoods.count*goods.price) as total
from ordergoods,
goods
where
ordergoods.goodsid=goods.id
group by orderid) t02
where t01.orderid=t02.orderid------------------------------------------
(3 行受影响)
表 'CustomerTable'。扫描计数 3,逻辑读取 6 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'OrderTable'。扫描计数 3,逻辑读取 6 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'GoodTable'。扫描计数 0,逻辑读取 24 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'OrderGood'。扫描计数 1,逻辑读取 2 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
id:主键,订单流水id
customerId:下订单的客户id,客户表Id的外键订单商品表ordergoods
id:主键
orderId:订单表id的外键
goodsId:商品表id的外键
count:商品数量商品表goods:
id:主键
name:商品名
price:单价客户表customer:
id:主键
name:客户名
address:客户地址诸表建表语句
create table customer(
id int(10) primary key not null,
name VARCHAR(255),
address VARCHAR(255)
) create table goods(
id int(10) primary key not null,
name VARCHAR(255),
price DOUBLE(10,2)
) create table orderTable(
id int(10) primary key not null,
customerid int(10) not null,
foreign key(customerid) references customer(id)
)create table ordergoods(
id int(10) primary key not null,
orderid int(10) not null,
goodsid int(10) not null,
count int(10),
foreign key(orderid) references orderTable(id),
foreign key(goodsid) references goods(id)
)插值语句
insert into customer ( id, name, address ) values ( '1', '张三', '北京' )
insert into customer ( id, name, address ) values ( '2', '李四', '大连' )
insert into customer ( id, name, address ) values ( '3', '王五', '上海' ) insert into goods ( id, name, price ) values ( '11', '上衣', '240' )
insert into goods ( id, name, price ) values ( '12', '裤子', '300' )
insert into goods ( id, name, price ) values ( '13', '鞋子', '350' ) insert into ordertable ( id, customerid ) values ( '111', '1' )
insert into ordertable ( id, customerid ) values ( '112', '2' )
insert into ordertable ( id, customerid ) values ( '113', '3' ) insert into ordergoods ( id, orderid, goodsid, count ) values ( '1111', '111', '11', '20' )
insert into ordergoods ( id, orderid, goodsid, count ) values ( '1112', '112', '11', '10' )
insert into ordergoods ( id, orderid, goodsid, count ) values ( '1113', '112', '12', '15' )
insert into ordergoods ( id, orderid, goodsid, count ) values ( '1114', '113', '11', '30' )
insert into ordergoods ( id, orderid, goodsid, count ) values ( '1115', '113', '12', '45' )
insert into ordergoods ( id, orderid, goodsid, count ) values ( '1116', '113', '13', '60' )====================================================
查询订单总价值 请问高手们这里的执行语句有什么优化的地方?逻辑读取,扫描计数太多了吧select t01.orderId,t01.customerName,t02.total from
(select
concat(customer.address,'商户',customer.name) as customerName,
orderTable.id as orderId
from
orderTable,
customer
where
ordertable.customerid=customer.id) t01,
(select orderid,sum(ordergoods.count*goods.price) as total
from ordergoods,
goods
where
ordergoods.goodsid=goods.id
group by orderid) t02
where t01.orderid=t02.orderid------------------------------------------
(3 行受影响)
表 'CustomerTable'。扫描计数 3,逻辑读取 6 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'OrderTable'。扫描计数 3,逻辑读取 6 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'GoodTable'。扫描计数 0,逻辑读取 24 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'OrderGood'。扫描计数 1,逻辑读取 2 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
(select o.orderid,c.customername+c.address as userinfo from ordertable o,customertable c where c.customerid=o.customerid) as T1
,
(select orderid,sum(goodprice*[count]) as total from goodtable,ordergood where goodtable.goodid=ordergood.goodid group by orderid) as T2
where t1.orderid=t2.orderid是sql2005的哦?
select o.orderid,c.customername+c.address as userinfo, sum(goodprice*[count]) as total
from ordertable o
left join customertable c on c.customerid=o.customerid
left join ordergood d on d.orderid=o.orderid
left join goodtable e on d.goodid=e.goodid
group by o.orderid,c.customername+c.address
表 'GoodTable'。扫描计数 1,逻辑读取 13 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'OrderGood'。扫描计数 1,逻辑读取 7 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'CustomerTable'。扫描计数 1,逻辑读取 7 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'OrderTable'。扫描计数 1,逻辑读取 2 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。真是不错。。,原理何在呢?巧门在哪呢???
不晓得,这样效果会不会好点select A.orderid, B.customername, C.total
From
ordertable A left join
(Select id,concat(customer.address,'商户',customer.name) as customername
from customer) B on A.customerid = B.ID left join
(select orderid,sum(ordergoods.count*goods.price) as total
from ordergoods, goods
where ordergoods.goodsid=goods.id
group by orderid) C on A.orderid = c.orderid
消息 195,级别 15,状态 10,第 4 行
'concat' 不是可以识别的 内置函数名称。
消息 102,级别 15,状态 1,第 9 行
'C' 附近有语法错误。