一条SQL语句
SELECT distinct
[[shipment_requests.*]],
[[shippingaddress.*]],
[[billingaddress.*]],
[[orders.*]],
[[rep_infos.*]],
[[warehouses.*]] FROM warehouses
INNER JOIN (select top {2} *
from shipment_requests
where warehouse_id={0} and shipment_requests.status_code='{1}') shipment_requests
ON warehouses.id = shipment_requests.warehouse_id
INNER JOIN shipment_request_line_items
ON shipment_requests.id = shipment_request_line_items.shipment_request_id
LEFT JOIN comments
ON shipment_requests.id=comments.shipment_request_id
LEFT JOIN orders
ON shipment_requests.order_id = orders.id
LEFT JOIN order_line_items
ON orders.id=order_line_items.order_id
INNER JOIN rep_infos
ON shipment_requests.rep_info_id=rep_infos.id
INNER JOIN addresses shippingaddress
ON shipment_requests.shipping_address_id = shippingaddress.id
INNER JOIN addresses billingaddress
ON shipment_requests.billing_address_id = billingaddress.id
首先遇到的问题是[[shipment_requests.*]],两个中括号是什么意思。单步跟到程序里发现被替换成了{shipment_requests.*}。这{}又不能直接在SQLSERVER里面运行。后问了老人才知道,这{}是hibernate框架自己定义的一个与类关联的东西。
然后就是不明白连续的连接是怎么运行的。
比如:
select ... from A
LEFT JOIN B ON A.id = B.id
LEFT JOIN C ON B.id = C.id。
困惑的是,C表究竟是和A表做了左连接;还是C表和B表做了左连接。
思考之后发现,这两个猜想得出的结果是一样。所以,这个过程可以理解成这样,就是进行了 select ... from A LEFT JOIN B ON A.id = B.id 得出的结果集,再和C表做了左连接。