SELECT PARENT.containername,
customer.customername,
customer.description,
FRESH.customerlotnumber,
PARENT.qty,
PARENT.targetdevice,
con.containername as SubLot,
con.qty as SubQty,
con.customerlotnumber as SubCustLot,
con.datecode as SubDateCode,
tap.qtyperreel,
FRESH.containername as FreshLot,
product.customerspecificinfo2 as COO,
con.customerorder,
con.custmotherwo,
pac.packagetypename,
smd.shipmentdestinationname,
dim.dimensionname,
lea.leadcountname,
pac.packagetypename || '-' || dim.dimensionname || '-' ||
lea.leadcountname as PDL
FROM insitedev.container con,
insitedev.container PARENT,
insitedev.customer customer,
insitedev.container FRESH,
insitedev.product product,
insitedev.tapenreelclass tap,
insitedev.packagetype pac,
insitedev.shipmentdestination smd,
insitedev.dimension dim,
insitedev.leadcount lea
WHERE ((con.parentcontainerid = PARENT.containerid) AND
(PARENT.customerid = customer.customerid) AND
(PARENT.freshlotid = FRESH.containerid) AND
(PARENT.productid = product.productid) AND
(product.tapenreelclassid = tap.tapenreelid) AND
(product.packagetypeid = pac.packagetypeid) and
(product.dimensionid = dim.dimensionid) AND
(product.leadcountid = lea.leadcountid) and
(fresh.shipmentdestinationid = smd.shipmentdestinationid) AND
(PARENT.ContainerName like
'TR7130017'))
ORDER BY con.qty ASC
其中为什么要用PARENT 和 FRESH,这两个函数有什么用,能详细讲解下么?
表的别名我知道
customer.customername,
customer.description,
FRESH.customerlotnumber,
PARENT.qty,
PARENT.targetdevice,
con.containername as SubLot,
con.qty as SubQty,
con.customerlotnumber as SubCustLot,
con.datecode as SubDateCode,
tap.qtyperreel,
FRESH.containername as FreshLot,
product.customerspecificinfo2 as COO,
con.customerorder,
con.custmotherwo,
pac.packagetypename,
smd.shipmentdestinationname,
dim.dimensionname,
lea.leadcountname,
pac.packagetypename || '-' || dim.dimensionname || '-' ||
lea.leadcountname as PDL
FROM insitedev.container con,
insitedev.container PARENT,
insitedev.customer customer,
insitedev.container FRESH,
insitedev.product product,
insitedev.tapenreelclass tap,
insitedev.packagetype pac,
insitedev.shipmentdestination smd,
insitedev.dimension dim,
insitedev.leadcount lea
WHERE ((con.parentcontainerid = PARENT.containerid) AND
(PARENT.customerid = customer.customerid) AND
(PARENT.freshlotid = FRESH.containerid) AND
(PARENT.productid = product.productid) AND
(product.tapenreelclassid = tap.tapenreelid) AND
(product.packagetypeid = pac.packagetypeid) and
(product.dimensionid = dim.dimensionid) AND
(product.leadcountid = lea.leadcountid) and
(fresh.shipmentdestinationid = smd.shipmentdestinationid) AND
(PARENT.ContainerName like
'TR7130017'))
ORDER BY con.qty ASC
其中为什么要用PARENT 和 FRESH,这两个函数有什么用,能详细讲解下么?
表的别名我知道
FROM insitedev.container con,
insitedev.container PARENT,
insitedev.container FRESH
这几张表不一样?
像我们有个数据字典表
里面有许多业务状态字的解释,
如果一个表里只有一个业务状态字,没问题,直接两张表关联就行了
如果有几个呢,你怎么关联
比如我们有数据字典表
code表
code code_cd code_name
order_cd 10 下单
order_cd 20 付款
order_cd 30 出库
dely_cd 10 收货
dely_Cd 20 送货
dely_cd 30 完成订单表
order 表
结构
order_id order_cd dely_cd 请问你怎么查一个订单的这两个状态?
语句就要这样写select a.order_id,b.code_name order_stat,b.code_name dely_stat
from order a,code b,code c
where a.order_cd=b.code_cd
and a.dely_cd=b.code_cd
and b.order_cd='order_cd'
and c.order_cd='dely_cd'也就是说在同一张表,要取不同内容,并且取的条件也不一样时,就会出现多次关联同一张表,就要用不同的别名了
表示要找这张表里,这两个字段相同的纪录
如果你直接
from insitedev.container
where
parentcontainerid=.containerid
这样结果只是找出同一行纪录中这两个字段相同的
比如你的纪录是这样
parentcontainerid containerid
1 2
2 1
你如果用
select *
from insitedev.container
where
parentcontainerid=containerid
出来结果是0 row用
select a.parentcontainerid, b.containerid
from insitedev.container a,
insitedev.container b
where
a.parentcontainerid=b.containerid出来结果是2rowparentcontainerid containerid
1 1
2 2