SQL语句的写法

发布网友

共4个回答

懂视网

表中每個deliver_id可能對應多個loginticsType,但是我們只要獲取最大的那一個.

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 分类表.一级名称=给定的名称

全部栏目

SQL语句的写法