发布网友
共4个回答
懂视网
SELECT dm.delivery_code,dm.deliver_id,om.order_id,DATE(FROM_UNIXTIME(om.order_createdate)) as order_date,om.order_status,om.order_payment,dm.delivery_store,
ld.logisticsTypes,dm.delivery_status,vd.vendor_name_simple,dm.freight_set,dm.delivery_freight_cost,dm.delivery_date,dm.arrival_date,dm.estimated_delivery_date
,dm.estimated_arrival_date,dm.estimated_arrival_period,dm.delivery_name,pt.product_name,odt.buy_num
FROM deliver_master dm
LEFT JOIN vendor vd on vd.vendor_id=dm.export_id
LEFT JOIN order_master om on om.order_id=dm.order_id
LEFT JOIN (SELECT max(logisticsType) as logisticsTypes, deliver_id fROM logistics_detail GROUP BY deliver_id ) as ld
on ld.deliver_id=dm.deliver_id
LEFT JOIN order_slave os on os.order_id=om.order_id
LEFT JOIN order_detail odt on odt.slave_id=os.slave_id
LEFT JOIN product_item pii on pii.item_id =odt.item_id
LEFT JOIN product pt on pii.product_id=pt.product_id where 1=1 and om.order_createdate >=‘1430409600‘ and om.order_createdate <=‘1432656000‘ limit 0,25;
SELECT dm.delivery_code,dm.deliver_id,om.order_id,DATE(FROM_UNIXTIME(om.order_createdate)) as order_date,om.order_status,om.order_payment,dm.delivery_store
,dm.delivery_status,vd.vendor_name_simple,dm.freight_set,dm.delivery_freight_cost,dm.delivery_date,dm.arrival_date,dm.estimated_delivery_date
,dm.estimated_arrival_date,dm.estimated_arrival_period,dm.delivery_name,pt.product_name,odt.buy_num,
(SELECT logisticsType FROM logistics_detail ld where dm.deliver_id = ld.deliver_id ORDER BY ld.rid DESC LIMIT 1) as logisticsTypes
FROM deliver_master dm
LEFT JOIN vendor vd on vd.vendor_id=dm.export_id
LEFT JOIN order_master om on om.order_id=dm.order_id
LEFT JOIN order_slave os on os.order_id=om.order_id
LEFT JOIN order_detail odt on odt.slave_id=os.slave_id
LEFT JOIN product_item pii on pii.item_id =odt.item_id
LEFT JOIN product pt on pii.product_id=pt.product_id where 1=1 and om.order_createdate >=‘1430409600‘ and om.order_createdate <=‘1432656000‘ limit 0,25;
兩種sql方法可以解決同樣的問題.
sql的寫法
标签:
热心网友
这是sql server 2000的方法,如果是2005以上可以用row_number(),oracle可以用rank,比这个就方便多了。
说明一下:class1就是一级分类的ID,class2就是二级分类ID,id就是产品表的产品id,这个也可以换成产品名称。
select a.class1, b.*
from class a, (
select *, px=(select count(*)
from proct a
where a.class2=b.class2 and a.id < b.id)+1
from
proct b) b
where a.class2 = b.class2
and px <=3 and a.class1 = 输入的一级分类ID
热心网友
select * from chanpin where fenleiid in (
select top 3 fenleiid from chanpin where fenleiid in
(select fenleiid from fenlei where parentid = [id])
group by fenleiid)
order by fenleiid
你用的时候把相应的表名给换掉.还有[id]也换成你查询的参数
SQL SERVER试验过,如有问题欢迎留言
热心网友
select top 3 产品名称 from 分类表 inner join 产品表 on 产品表.二级名称=分类表.二级名称 and 分类表.一级名称=给定的名称