三表关联子查询与左联接

最新推荐文章于 2024-09-04 22:58:19 发布

weixin_30920853

最新推荐文章于 2024-09-04 22:58:19 发布

阅读量146

点赞数

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/lgq0100/p/6178265.html

1.子查询

SELECT * FROM (SELECT ROW_NUMBER() OVER ( order by E_DateTime DESC )AS Row, * from (SELECT *, (SELECT C_Name FROM dbo.CaringPeople WHERE C_Id=dbo.Equipment.EC_Id ) AS Cname, (SELECT Mem_Name FROM dbo.Member WHERE Mem_Id=E_Memid) AS Mname, (SELECT Mem_PhoneNum FROM dbo.Member WHERE Mem_Id=E_Memid) AS Mphone FROM Equipment ) TT WHERE 1=1 and Mname like '%U%' )TTT WHERE TTT.Row between 1 and 20

SELECT COUNT(1)　FROM (SELECT * from (SELECT *, (SELECT C_Name FROM dbo.CaringPeople WHERE C_Id=dbo.Equipment.EC_Id ) AS Cname,
(SELECT Mem_Name FROM dbo.Member WHERE Mem_Id=E_Memid) AS Mname,
(SELECT Mem_PhoneNum FROM dbo.Member WHERE Mem_Id=E_Memid) AS Mphone
FROM Equipment )TT )TTT where 1=1 and Mname like '%U%'

2.左联接

SELECT * FROM ( SELECT ROW_NUMBER() OVER ( order by B_Testtime DESC )AS Row, * from (SELECT *　FROM (SELECT * from dbo.Bloodpressure left JOIN dbo.CaringPeople ON BC_Id=C_Id left join dbo.Member on C_MemId=Mem_Id )TT) T )TTT WHERE TTT.Row between 1 and 20

SELECT COUNT(1)　FROM (SELECT * from Equipment left JOIN dbo.CaringPeople ON EC_Id=C_Id left join dbo.Member on C_MemId=Mem_Id )TT

转载于:https://www.cnblogs.com/lgq0100/p/6178265.html