1. 查询出订单状态为 已出库 或者 已完成 ,并且还没有进行过评论的SO#。
提示:下面两张表为 评论主表和从表:[OverseaECommerceManagement].[dbo].[ProductGroupReview_Master] , [OverseaECommerceManagement].[dbo].[ProductReview_Detail]
2. 查询出订单状态为 已出库 或者 已完成 ,并且还没有进行过退货的SO#
提示:退货主表:ipp3.dbo.RMA_Register
SQL如下:
3. 以productID为变量,查询出下面的结果:
SQL如下:
4 下面是通过一个ProductID,得到这个商品前台website一,二,三级分类的ID和名称的步骤,我们把它整合到一个SQL里面,以ProductID为变量。
--1、通过商品编号得到C3SysNo:【得到c3sysno=405,商品id】
--select top 5 C3SysNo,* from IPP3.dbo.Product where ProductID = 'A03-002-001'
--2、得到前台三级分类名和sysno:【sysno=410 ,得到3级分类】
--select * from OverseaECommerceManagement.dbo.EC_Category (nolock) where C3Sysno in(405)
--3、将sysno带到关系表里面,对应关系表的EC_CategorySysno,【parentsysno=57 sysno=EC_CategorySysno=410 】
--select top 5 * from OverseaECommerceManagement.dbo.EC_CategoryRelation where EC_CategorySysno = 410
--4、得到二级分类的EC_CategorySysno=57为,得到parentsysno=7
--select top 5 * from OverseaECommerceManagement.dbo.EC_CategoryRelation where Sysno =57
----5、得到一级分类的EC_CategorySysno=7为
--select top 5 * from OverseaECommerceManagement.dbo.EC_CategoryRelation where Sysno = 7
--6、再将三个EC_CategorySysno带入EC_Category,得到一,二,三级分类的name
select top 5 * from OverseaECommerceManagement.dbo.EC_Category (nolock) where Sysno in(7,57,410)
select p.ProductID,
eccn1.ParentSysno as 'C1ID',
ecc2.Name as 'C1Name',
eccn.ParentSysno as 'C2ID',
ecc1.Name as 'C2Name',
eccn.Sysno as 'C3ID',
ecc.Name as 'C3Name'
from IPP3.dbo.Product as p (nolock)
inner join OverseaECommerceManagement.dbo.EC_Category as ecc(nolock)
on p.C3SysNo = ecc.C3Sysno
inner join OverseaECommerceManagement.dbo.EC_CategoryRelation as eccn(nolock)
on ecc.Sysno = eccn.Sysno
inner join OverseaECommerceManagement.dbo.EC_Category as ecc1(nolock)
on ecc1.Sysno = eccn.ParentSysno
inner join OverseaECommerceManagement.dbo.EC_CategoryRelation as eccn1(nolock)
on eccn.ParentSysno = eccn1.Sysno
inner join OverseaECommerceManagement.dbo.EC_Category as ecc2(nolock)
on eccn1.ParentSysno = ecc2.Sysno
where p.ProductID = 'A07-009-001'
查询出下面的结果:
4. 查询出商品的评论条数大于5的商品
SQL如下: