|zyciis| 如何将left join 中的on条件中的and条件，提取到where条件中呢？有示例SQL脚本 谢谢

Question

CREATE TABLE [dbo].[Tst_Company](
[ID] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL,
[Name] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL,
[Status] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]GO
CREATE TABLE [dbo].[Tst_Employee](
[ID] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL,
[CompanyID] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL,
[Name] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL,
[Status] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
INSERT INTO Tst_Company ([ID], [Name], [Status]) VALUES (N'1         ', N'163       ', N'0         ')
INSERT INTO Tst_Company ([ID], [Name], [Status]) VALUES (N'2         ', N'Sina      ', N'1         ')
INSERT INTO Tst_Company ([ID], [Name], [Status]) VALUES (N'3         ', N'PConline  ', N'1         ')
INSERT INTO Tst_Company ([ID], [Name], [Status]) VALUES (N'4         ', N'Taobao    ', N'0         ')
INSERT INTO Tst_Employee ([ID], [CompanyID], [Name], [Status]) VALUES (N'1         ', N'1         ', N'小陈        ', N'1         ')
INSERT INTO Tst_Employee ([ID], [CompanyID], [Name], [Status]) VALUES (N'2         ', N'1         ', N'小王        ', N'1         ')
INSERT INTO Tst_Employee ([ID], [CompanyID], [Name], [Status]) VALUES (N'3         ', N'4         ', N'小张        ', N'0         ')
INSERT INTO Tst_Employee ([ID], [CompanyID], [Name], [Status]) VALUES (N'4         ', N'3         ', N'小李        ', N'1         ')
INSERT INTO Tst_Employee ([ID], [CompanyID], [Name], [Status]) VALUES (N'5         ', N'4         ', N'小白        ', N'0         ')
INSERT INTO Tst_Employee ([ID], [CompanyID], [Name], [Status]) VALUES (N'6         ', N'2         ', N'小黑        ', N'1         ')
INSERT INTO Tst_Employee ([ID], [CompanyID], [Name], [Status]) VALUES (N'7         ', N'3         ', N'小红        ', N'1         ')
GO
select * from tst_employee a left join tst_company b on a.companyid = b.id and b.status = 0以上脚本生成的查询结果为：
1          1          小陈         1          1          163        0         
2          1          小王         1          1          163        0         
3          4          小张         0          4          Taobao     0         
4          3          小李         1          NULL NULL NULL
5          4          小白         0          4          Taobao     0         
6          2          小黑         1          NULL NULL NULL
7          3          小红         1          NULL NULL NULL
-----------------------------------------------------
但是因为我现在是用NHibernate自动生成SQL的，而NHibernate在关联的时候没有办法给On再添加AND条件
那我的查询
select * from tst_employee a left join tst_company b on a.companyid = b.id where ??
能实现和我上面一样的查询结果呢？
谢谢

Answer 1

select * 
from tst_employee a left join (select * from tst_company where status = 0) b on a.companyid = b.id

Answer 2

where b.status = 0和 and b.status = 0
有可能是不一样的

Answer 3

--改成select
 *  
from
 tst_employee a 
left join
 (select * from tst_company where status = 0) b 
on
 a.companyid = b.id

Answer 4

用where做条件就直接过滤掉了，就没有null了。

Answer 5

select * from tst_employee a left join tst_company b on a.companyid = b.id where ??
用这种格式 绝对实现不了

Answer 6

select * from tst_employee a left join tst_company b on a.companyid = b.id 
  where isnull(b.status,0)=0

Answer 7

select *  from tst_employee a 
left join (select * from tst_company where status = 0) b 
on
 a.companyid = b.id
--------
这种不行因为NHibernate没有办法自动生成这样的格式
where isnull(b.status,0)=0
这些都不行。。
谢谢

Answer 8

http://fan.renren.it/a/JAVAbiancheng/Hibernate/20100922/35044.html
这里有说用with条件，但我不知道什么意思

Answer 9

最简单的办法，就是基于Tst_Company这个表建个视图：
CREATE VIEW v_tst_company AS
SELECT * FROM tst_company
WHERE STATUS=0然后：
select * from tst_employee a left join v_tst_company b on a.companyid = b.id 
这样就和你要的一模一样了。否则，单纯改where不改select *是不可能实现你要的那样的。

Answer 10

最简单的办法，就是基于Tst_Company这个表建个视图：
-----------------
视图不行啊。。因为我的AND条件是不定的。。我没有办法做视图
谢谢

Answer 11

难道我要写成这样select a.*,
case when b.status <>0 then null else b.ID end ,
case when b.status <>0 then null else b.Name end ,
case when b.status <>0 then null else b.Status end 
from tst_employee a left join tst_company b on a.companyid = b.id 

Answer 12

hibernate 的 HQL 写法不是很灵活，有很多和SQL的语法不同，建议你重新建个实体，然后用JDBC来处理，返回list就行。