多个表连接,语句比较长,,大家不需要看这么多,只看最后,我想问哈,如果我还有一个表需要做内连接,
比如需要连接的表示表是c,代码怎么加?如果在语句最后面 添加 inner join OrderInfo C On p.orderid = C.Orderid
就会有错误,如果将group by p.orderid,p.item,p.detail 放在后面就可以了,但是我不希望怎么做?代码怎么写呢
?
select p.orderid,p.item,p.detail,max(orderreview.Duedate) from (select distinct OrderId,orderreview_item.* FROM orderreview,orderreview_item) as p left outer join orderreview on p.item=orderreview.item and p.orderid=orderreview.orderid group by p.orderid,p.item,p.detail
比如需要连接的表示表是c,代码怎么加?如果在语句最后面 添加 inner join OrderInfo C On p.orderid = C.Orderid
就会有错误,如果将group by p.orderid,p.item,p.detail 放在后面就可以了,但是我不希望怎么做?代码怎么写呢
?
select p.orderid,p.item,p.detail,max(orderreview.Duedate) from (select distinct OrderId,orderreview_item.* FROM orderreview,orderreview_item) as p left outer join orderreview on p.item=orderreview.item and p.orderid=orderreview.orderid group by p.orderid,p.item,p.detail
--内联接简单写法
SELECT A.IDA,A.VA,B.IDB,B.VB FROM @TA A,@TB B
WHERE A.IDA=B.IDB--内联接
SELECT A.IDA,A.VA,B.IDB,B.VB FROM @TA A INNER JOIN @TB B
ON A.IDA=B.IDBSELECT A.IDA,A.VA,B.IDB,B.VB FROM @TA A JOIN @TB B
ON A.IDA=B.IDB
建议使用临时表技术,就是将前一个连接的表作为一个临时表再与最后的表连接就OK了
select p.orderid,p.item,p.detail,max(orderreview.Duedate),C.Orderid
from
((select distinct OrderId,orderreview_item.* FROM orderreview,orderreview_item) as p
left outer join orderreview on p.item=orderreview.item and p.orderid=orderreview.orderid) where p.orderid = C.Orderid
group by p.orderid,p.item,p.detail
FROM
(
select p.orderid,p.item,p.detail,Duedate=max(orderreview.Duedate)
from (select distinct OrderId,orderreview_item.* from orderreview,orderreview_item) as p
left outer join orderreview on p.item=orderreview.item and p.orderid=orderreview.orderid
group by p.orderid,p.item,p.detail
) A
inner join OrderInfo C On A.orderid = C.Orderid