--1.用一条T-sql语句，查询所有员工在哪个部门，另外员工没有指定的部门用赋值为“部门不明确”
select a.*,isnull(b.name,'部门不明确') as departmentName
from [user] a
left join [Department] b on a.DepartmentID=b.id

--2.用一条T-sql语句，查询每一个部门的男女人数各是多少？
select b.name,
  sum(case when a.sex='男' then 1 else 0 end) as 男,
  sum(case when a.sex='女' then 1 else 0 end) as 女
from [user] a
join [department] b on a.departmentid=b.id
group by b.name

--3.用一条T-sql语句，查询哪个部门人数最多，要是有相等的就找出部门id数最小的那个
select top 1 b.name,count(*) as 人数
from [user] a
join [department] b on a.departmentid=b.id
group by b.id,b.name
order by 人数 desc,b.id

------解决方案--------------------
--1
  SELECT a.id,a.NAME,ISNULL(b.NAME,'部门不明确') '部门'
  FROM USER a LEFT JOIN department b ON a.departmentid=b.id
  --2
  SELECT b.NAME,sex,COUNT(1)
  FROM USER a INNER JOIN department b ON a.departmentid=b.id
  GROUP BY b.NAME,sex

------解决方案--------------------
SQL code


--1.
select u.name, u.sex, 
        CASE WHEN d.name is not null and rtrim(ltrim(d.name)) <> '' then d.name ELSE '部门不明确' END name
from user u 
left join department d on u.DepartmentId = d.id

--2.
select d.name,
        (select COUNT(sex) from u where departmentId = user1.departmentid and sex='男') 男同事人数,
        (select COUNT(sex) from u where departmentId = user1.departmentid and sex='女') 女同事人数,
from user user1
join department d on user1.departmentid = d.id

--3.
;with c1 as
(
    select d.id, COUNT(user1.id) departmentSum
    from user user1
    join department d on user1.departmentid = d.id
    GROUP BY d.id
)
select MIN(d.id), d.name
from c1 
join department d on c1.id = d.id 
where c1.departmentSum = (select MAX(c1.departmentSum) from c1)