盗版一下各位大侠的模板
/*
@@version
*/IF OBJECT_ID('[A]') IS NOT NULL DROP TABLE [A]
CREATE TABLE [A](aid INT,aname VARCHAR(1))
INSERT [A] SELECT 1,'A' UNION SELECT 2,'B' UNION SELECT 3,'C'IF OBJECT_ID('[B]') IS NOT NULL DROP TABLE [B]
CREATE TABLE [B](ID INT,bid INT,aid INT)
INSERT [B] SELECT 1,1,1 UNION SELECT 2,1,2 UNION SELECT 3,1,3 UNION SELECT 4,2,3 UNION SELECT 5,2,2SELECT [A].aid,COUNT(bid) FROM [A]
LEFT JOIN [B] ON [A].aid = [B].aid
WHERE bid = 2
GROUP BY [A].aid/*
aid
----------- -----------
2 1
3 1
*/
我想问的就是我用的是左连接了,为什么显示的数据不是aid
----------- -----------
1 null
2 1
3 1
如果我想显示出如下结果应该怎么写?aid
----------- -----------
1 0
2 1
3 1
/*
@@version
*/IF OBJECT_ID('[A]') IS NOT NULL DROP TABLE [A]
CREATE TABLE [A](aid INT,aname VARCHAR(1))
INSERT [A] SELECT 1,'A' UNION SELECT 2,'B' UNION SELECT 3,'C'IF OBJECT_ID('[B]') IS NOT NULL DROP TABLE [B]
CREATE TABLE [B](ID INT,bid INT,aid INT)
INSERT [B] SELECT 1,1,1 UNION SELECT 2,1,2 UNION SELECT 3,1,3 UNION SELECT 4,2,3 UNION SELECT 5,2,2SELECT [A].aid,COUNT(bid) FROM [A]
LEFT JOIN [B] ON [A].aid = [B].aid
WHERE bid = 2
GROUP BY [A].aid/*
aid
----------- -----------
2 1
3 1
*/
我想问的就是我用的是左连接了,为什么显示的数据不是aid
----------- -----------
1 null
2 1
3 1
如果我想显示出如下结果应该怎么写?aid
----------- -----------
1 0
2 1
3 1
LEFT JOIN [B] ON [A].aid = [B].aid
AND bid = 2
GROUP BY [A].aid
[A].aid,isnull(COUNT(bid),0)
FROM
[A]
LEFT JOIN
[B]
ON
[A].aid = [B].aid and [B].bid = 2
GROUP BY
[A].aid
CREATE TABLE [A](aid INT,aname VARCHAR(1))
INSERT [A] SELECT 1,'A' UNION SELECT 2,'B' UNION SELECT 3,'C'IF OBJECT_ID('[B]') IS NOT NULL DROP TABLE [B]
CREATE TABLE [B](ID INT,bid INT,aid INT)
INSERT [B] SELECT 1,1,1 UNION SELECT 2,1,2 UNION SELECT 3,1,3 UNION SELECT 4,2,3 UNION SELECT 5,2,2SELECT [A].aid,COUNT(bid) FROM [A]
LEFT JOIN (SELECT * FROM [B] WHERE bid = 2)T ON [A].aid = T.aid
GROUP BY [A].aid
/*aid
----------- -----------
1 0
2 1
3 1
警告: 聚合或其他 SET 操作消除了空值。*/
LEFT JOIN [B] ON [A].aid = [B].aid
WHERE bid = 2
GROUP BY [A].aid
我以为是先LEFT JOIN后判断了再判断WHERE bid =2的。为什么不是先判断LEFT JOIN 而是先判断WHERE呢?
参考执行步骤
明白了,WHERE还会过滤一遍结果。
相当于先执行了LEFT JOIN后的结果,再根据LEFT JOIN结果执行WHERE过滤。
相当与执行了2次过滤。
谢谢!