• 主页
• 基础类
• 应用实例
• 新技术前沿

# left join 的On 与查询条件 where 的疑难杂症

newmankind 2005-10-12 05:40:59

1、
select a.col1 ,count(*) as qty
from a
left join b on b.col2 = a.col2 and b.role = 3 and b.void = 'n'
left join c on a.col1 = c.col1
where c.void= 'n'
group by a.col
2、
select a.col1 ,count(*) as qty
from a
left join c on a.Col2 = c.Col2 , b
where c.void= 'n' and a.Col1 = b.Col1 and b.role = 3 and b.void = 'n'
group by a.col1

...全文
337 点赞 收藏 10

10 条回复

MorningTea 2005-10-14
:.(

zxbyhcsdn 2005-10-14

MorningTea 2005-10-14

newmankind 2005-10-12

select a.col1 ,count(b.col2) as qty ,max(c.otherinf)
from a
left join b on b.col2 = a.col2
left join c on a.col1 = c.col1
where c.void= 'n' and b.role = 3 and b.void = 'n'
group by col1

newmankind 2005-10-12

a数据
col1 col2
---------
c1 b1
c1 b2
c2 b2
b中对应数据
role col2 void
------------------
3 b1 n
3 b2 Y
3 b2 n

select a.col1,a.col2,b.role
from a inner join b on a.col2 = b.col2 and b.void = 'n'

select a.col1,a.col2,b.role
from a inner join b on a.col2 = b.col2

newmankind 2005-10-12

col1 col2
---------
c1 b1
c1 b2
c2 b2
b中对应数据
role col2 void
------------------
3 b1 n
2 b2 n
c中数据
col1 void otherinf
--------------------
c1 n inf1
c2 n inf2

c1 1 inf1
c2 0 inf2

select a.col1 ,count(b.col2) as qty ,c.otherinf
from a
left join b on b.col2 = a.col2
left join c on a.col1 = c.col1
where c.void= 'n' and b.role = 3 and b.void = 'n'
group by col1

MorningTea 2005-10-12

and b.role = 3 and b.void = 'n' --这些是率选条件，不要写在on里面

a left join b on a.col1 = b.col12 ...
left join c on b.col3 = c.col4 ...
where a.col2 = 'xx' and b.col5 = 'xx' and c.col1 = 'xx' ...

newmankind 2005-10-12
to：happygong(高兴)

to：wgsasd311(自强不息)

wgsasd311 2005-10-12

happygong 2005-10-12

MS-SQL Server

3.2w+

MS-SQL Server相关内容讨论专区