u表id自段为主键,s表引用u表id,其中s表只有u表id自段中的部分记录。
u为左,s为右,用left join 查询,却不能把u表的所有记录查询出来,跟用inner join查询的结果没有区别,我想把所有s表匹配和不匹配u表的内容查询出来
例如:
-----------------
u表:
id userName
1 张三
2 李四
3 王五
-----------------
s表:
a_id score
1 65
1 68
3 70
-----------------
我想要实现的效果:
userName totalScore
张三 133
李四 0
王五 70
------------------
要想实现上面的效果应该怎样写呢?请别简单的告诉我用left join 或 right join ,我上面已经说过了,那样不行,只能显示总分不为0的数据,跟用inner join一样,或者请大虾指明我下面sql语句的错误之处:
select u.username ,sum(s.score)
from users u left join score s
on u.id = s.a_id
where time between '2008-10-01' and '2008-10-31'
and u.id not in (select id from users where popedom = 0)
group by u.username
order by s.score desc
u为左,s为右,用left join 查询,却不能把u表的所有记录查询出来,跟用inner join查询的结果没有区别,我想把所有s表匹配和不匹配u表的内容查询出来
例如:
-----------------
u表:
id userName
1 张三
2 李四
3 王五
-----------------
s表:
a_id score
1 65
1 68
3 70
-----------------
我想要实现的效果:
userName totalScore
张三 133
李四 0
王五 70
------------------
要想实现上面的效果应该怎样写呢?请别简单的告诉我用left join 或 right join ,我上面已经说过了,那样不行,只能显示总分不为0的数据,跟用inner join一样,或者请大虾指明我下面sql语句的错误之处:
select u.username ,sum(s.score)
from users u left join score s
on u.id = s.a_id
where time between '2008-10-01' and '2008-10-31'
and u.id not in (select id from users where popedom = 0)
group by u.username
order by s.score desc
INSERT @TA
SELECT 1, N'张三' UNION ALL
SELECT 2, N'李四' UNION ALL
SELECT 3, N'王五'DECLARE @TB TABLE(a_id INT, score INT)
INSERT @TB
SELECT 1, 65 UNION ALL
SELECT 1, 68 UNION ALL
SELECT 3, 70SELECT userName,SUM(ISNULL(score,0)) as score
FROM @TA AS A LEFT JOIN @TB AS B ON A.ID=B.A_ID
GROUP BY userName
/*
userName score
---------- -----------
王五 70
李四 0
张三 133
*/
left join (select a_id,sum(score) as score from s group by a_id) s
on u.id=s.a_id1 张三 133
2 李四 0
3 王五 70
INSERT @TA
SELECT 1, N'张三' UNION ALL
SELECT 2, N'李四' UNION ALL
SELECT 3, N'王五'DECLARE @TB TABLE(a_id INT, score INT)
INSERT @TB
SELECT 1, 65 UNION ALL
SELECT 1, 68 UNION ALL
SELECT 3, 70SELECT a.userName,ISNULL(b.score,0) as score
FROM @TA AS A LEFT JOIN (select a_id,sum(score) as score from @TB group by a_id) AS B ON A.ID=B.A_ID--结果
userName score
---------- -----------
张三 133
李四 0
王五 70(3 行受影响)
select
u.username ,
sum(s.score)
from users u
left join score s on u.id = s.a_id
where time between '2008-10-01' and '2008-10-31' and popedom <> 0
group by u.username
order by s.score desc
select u.username,
isnull(b.score,0) as score
from users u
left outer join (
select a_id, sum(isnull(score,0)) as score
from score
group by a_id
) b on b.a_id = u.id
where time between '2008-10-01' and '2008-10-31'
and u.id not in (select id from users where popedom = 0)
order by score desc
这不就是外部连接?