sqlserver联查,数据库联查

sqlserver多表联合查询

select a.a_name as 名字,count(b.a_id) as 数量 from a inner join b on a.a_id = b.a_id group by a.a_name

名字 数量

me 3

wo 1

he 1

select a.a_name as 名字,count(b.a_id) as 数量 from a left join b on a.a_id = b.a_id group by a.a_name

名字 数量

me 3

wo 1

he 1

she 0

our 0

相关问答

Q1: sqlserver联合查询问题

使用 case when ..then else end 语句进行显示

多列数据展示都是使用case end

学会使用case end可以展示多列报表

自己试试吧

Q2: “sqlserver”三表联如何查询“sql”语句？

假设学生表叫student,课程表叫class,选课表叫choose\x0d\x0a1.三层嵌套的问题\x0d\x0aselect student.name from student where student.id IN\x0d\x0a (select choose.sid from choose where choose.cid NOT IN\x0d\x0a (select class.id from class where class.teacher='李明'))\x0d\x0a2.一个内连接，一个嵌套\x0d\x0aselect student.name,avg(choose.score) from\x0d\x0astudent inner join choose on student.id=choose.sid\x0d\x0awhere student.id IN\x0d\x0a (select choose.sid from choose \x0d\x0a where choose.score=2)\x0d\x0agruop by student.id\x0d\x0a3.一个联合查询，一个嵌套查询\x0d\x0aselect student.name from student\x0d\x0awhere student.id IN\x0d\x0a(select c1.sid from choose c1 where choose.cid='1'\x0d\x0a union \x0d\x0a select c2.sid from choose c2 where choose.cid='2'\x0d\x0a on c1.sid=c2.sid\x0d\x0a)\x0d\x0a4.其实就是自连接查询和行列交换的问题：\x0d\x0aselect student.id,\x0d\x0a(case choose.id when '1' then choose.score end) as 1号课成绩,\x0d\x0a(case choose.id when '2' then choose.score end) as 2号课成绩,\x0d\x0afrom student inner join choose on student.id=choose.sid sc1,\x0d\x0astudent inner join choose on student.id=choose.sid sc2\x0d\x0awhere sc1.id='1' \x0d\x0aand sc2.id='2'\x0d\x0aand sc1.scoresc2.score

Q3: 请教大神SQlSERVER向这样多表联合查询怎么写语句

SELECT s.*,p.player_name FROM t_match_score s

LEFT JOIN t_match_player p

ON s.player_id=p.player_id

Q4: sqlserver三表联查sql语句

假设学生表叫student,课程表叫class,选课表叫choose

1.三层嵌套的问题

select student.name from student where student.id IN

(select choose.sid from choose where choose.cid NOT IN

(select class.id from class where class.teacher='李明'))

2.一个内连接，一个嵌套

select student.name,avg(choose.score) from

student inner join choose on student.id=choose.sid

where student.id IN

(select choose.sid from choose

where choose.score'60'

group by choose.sid

having count(choose.sid)=2)

gruop by student.id

3.一个联合查询，一个嵌套查询

select student.name from student

where student.id IN

(select c1.sid from choose c1 where choose.cid='1'

union

select c2.sid from choose c2 where choose.cid='2'

on c1.sid=c2.sid

)

4.其实就是自连接查询和行列交换的问题：

select student.id,

(case choose.id when '1' then choose.score end) as 1号课成绩,

(case choose.id when '2' then choose.score end) as 2号课成绩,

from student inner join choose on student.id=choose.sid sc1,

student inner join choose on student.id=choose.sid sc2

where sc1.id='1'

and sc2.id='2'

and sc1.scoresc2.score

Q5: sqlserver中的关联查询问题

sql server 本省对语句就有自动优化功能， 第一个里边where语句相当于join on 来操作的

这样看来两个的效率基本上是一样的，你可以做两个表试一试。但是join的写法有助于你的编写语法检查，和易读性