SQLserver 两表联查及三表联查 两种方法

  1. 列表内容

–合同表显示 跟用户表联查

SELECT * FROM dbo.Contract
SELECT h.Contract_id,h.Contract_Name,y.NetName,h.Contract_Money,h.Contract_Time,h.Contract_Enclosure

FROM Contract h JOIN dbo.Users y on h.UsersID = y.ID

SELECT * FROM USERS
SELECT h.Contract_id,h.Contract_Name,Y.ID,y.NetName,h.Contract_Money,h.Contract_Time,h.Contract_Enclosure
from Contract h JOIN dbo.Users y on h.UsersID = y.ID

–======================================
–物品表 用户表 借用表 三表联查
–======================================
———–第一种方法
SELECT * froM dbo.Goods
Goods_ID
Select Top 4 h.Contract_id,h.Contract_Name,y.NetName,h.Contract_Money,h.Contract_Time,h.Contract_Enclosure From Contract h JOIN dbo.Users y on h.UsersID = y.ID where (Contract_id Not In (Select Top 0 Contract_id From Contract h JOIN dbo.Users y on h.UsersID = y.ID where Contract_Name like ‘%二号合同%’ Order by Contract_id asc)) And Contract_Name like ‘%二号合同%’ Order by Contract_id ASC
————第二种方法
SELECT b.Borrow_ID,g.Goods_Name,b.Borrow_Time,b.Borrow_Days,u.NetName,b.Borrow_Reason
,b.Borrow_State FROM dbo.Borrow b
JOIN dbo.Goods g ON b.Goods_Name=g.Goods_Name
JOIN dbo.Users u on b.UID=u.ID

SELECT b.Borrow_ID,g.Goods_Name,b.Borrow_Time,b.Borrow_Days,u.NetName,b.Borrow_Reason
,b.Borrow_State FROM dbo.Borrow b , dbo.Goods g , dbo.Users u where b.Goods_Name=g.Goods_Name and b.UID=u.ID

SELECT * FROM dbo.Borrow

展开阅读全文

没有更多推荐了,返回首页