select fir.name as topcataname,
sec.name as seccataname,
thd.name as thdcataname,
p.title,
up.hasproduct,
up.price,
up.inputdate
from userproduct up
inner join product p on up.productid=p.productid
inner join cata thd on p.catano=thd.catano
inner join cata sec on thd.upcatano= sec.catano
inner join cata fir on sec.upcatano= fir.catano ";
sec.name as seccataname,
thd.name as thdcataname,
p.title,
up.hasproduct,
up.price,
up.inputdate
from userproduct up
inner join product p on up.productid=p.productid
inner join cata thd on p.catano=thd.catano
inner join cata sec on thd.upcatano= sec.catano
inner join cata fir on sec.upcatano= fir.catano ";
inner join cata sec on thd.upcatano= sec.catano
inner join cata fir on sec.upcatano= fir.catano ";
这几句他一定是在调戏我了,一个表定义三个别名,fir÷sec÷thd都是一个表的别名
sec.name as seccataname,
thd.name as thdcataname,
p.title,
up.hasproduct,
up.price,
up.inputdate
from userproduct up
inner join product p on up.productid=p.productid
inner join cata thd on p.catano=thd.catano
inner join cata sec on thd.upcatano= sec.catano
inner join cata fir on sec.upcatano= fir.catano ";inner join好办,不要写这么难看的代码:个人认为容易理解些的如下:select fir.name as topcataname,sec.name as seccataname,thd.name as thdcataname,
p.title,up.hasproduct,up.price,up.inputdate
from userproduct up,product p,cata thd,cata sec,cata fir
where up.productid=p.productid and p.catano=thd.catano and thd.upcatano= sec.catano
and on sec.upcatano= fir.catano
改写之后,还是难看,呵呵。
cata 一个表记录着三个级别的东西,却用三个字段来区分。不如考虑用两个字段来区别,一个是parent,一个是层,其实有一个parent就行了。
查找结果是用户产品的价格,注册时间以及产品名称以及三个cata(这是什么意思)级别的名称。
inner join cata sec on thd.upcatano= sec.catano
inner join cata fir on sec.upcatano= fir.catano ";
这两句替换后不就是cata.upcatano=cata.catano;然后重复了一遍么?
select fir.name as topcataname,
sec.name as seccataname,
thd.name as thdcataname,
不都是选择数据库中同一字段么?
select fir.name as topcataname,
sec.name as seccataname,
thd.name as thdcataname,
p.title,
up.hasproduct,
up.price,
up.inputdate
from userproduct up
inner join product p on up.productid=p.productid
inner join cata thd on p.catano=thd.catano
inner join cata sec on thd.upcatano= sec.catano
inner join cata fir on sec.upcatano= fir.catano ";//这是我解析出来的意思
select cata.name as topcataname,
cata.name as seccataname,
cata.name as thdcataname,
product.title,
userproduct.hasproduct,
userproduct.price,
userproduct.inputdate
from userproduct up
inner join product p on userproduct.productid=product.productid
inner join cata thd on product.catano=cata.catano
inner join cata sec on cata.upcatano= cata.catano
inner join cata fir on cata.upcatano= cata.catano ";//进一步简化。可以得到
select
cata.name,
cata.name ,
cata.name,
product.title,
userproduct.hasproduct,
userproduct.price,
userproduct.inputdate
from userproduct
inner join product on userproduct.productid=product.productid
inner join cata on product.catano=cata.catano
inner join cata on cata.upcatano= cata.catano
inner join cata on cata.upcatano= cata.catano;大家看看我理解正不正确?
根据代码的意思,的确是要原来那样写的。
同表关联,必须用别名才能区别记录集。
关联时,并不需要表中全部内容,而是符合条件的记录集了,别名就表示该记录集的意思。