From(Select Row_Number() Over(Order By OrderID asc, CreateTime desc) As Rowid,*
From Dish Where ShopID=6 and AppID=6 ) As D
left join DishTag as t1 on D.UnitID=t1.TagID and t1.ShopID=D.ShopID and t1.AppID=D.AppID
left join DishTag as t2 on D.TasteID=t2.TagID and t2.ShopID=D.ShopID and t2.AppID=D.AppID
left join DishSort as t3 on D.SortID=t3.DishSortID and t3.ShopID=D.ShopID and t3.AppID=D.AppID
left join StoreImg S on D.DishID= S.TargetID and D.ShopID=s.ShopID and S.ImgType='菜品'
Where Rowid Between 1 And 15;
Select Count(1) From Dish Where ShopID=6 and AppID=6
在SQL里,常常需要对多个表关联起来进行查询,下面把我写的一个简单的多表关联的例子给大家看看,方法很简单,只要你学会原理就行:
select
o.id id,o.oid oid,o.number number,o.seOrder seOrder,o.endprice endprice,--第一个表的字段
d.uid uid,d.oDatetime oDatetime,--第二个表的字段
p.proname proname,p.spec spec,p.material material,p.price price,--第三个表的字段
c.price1 price1,c.price2 price2,c.price3 price3,c.price4 price4,c.price5 price5 --第四个表的字段
from
orderlist o --表一
left join products p on o.pid=p.id --表二
left join orderForm d on d.id=o.oid --表三
left join classify c on p.bid=c.id --表四
--更多的表
order by o.id desc
这样,就把四个表关联起来查询了。如果有更多的表,可以一个一个的关联下去,不过我还是不希望关联的表太多。
左外部连接 *= left join
右外部连接=* right join
外部连接中不匹配的分量用NULL表示
内连接用法
select a.*,b.* from a,b where a.id=b.id