求好手帮忙写几条sql语句，新手有困难

求高手帮忙写几条sql语句，新手有困难
两个表，一个user - id,name,sex,DepartmentID 另一个是部门表Department - id, name
问：1.用一条T-sql语句，查询所有员工在哪个部门，另外员工没有指定的部门用赋值为“部门不明确”
2.用一条T-sql语句，查询每一个部门的男女人数各是多少？
3.用一条T-sql语句，查询哪个部门人数最多，要是有相等的就找出部门id数最小的那个！
新手作业，求学习，先谢过了

[解决办法]
try

SQL code

--1.用一条T-sql语句，查询所有员工在哪个部门，另外员工没有指定的部门用赋值为“部门不明确”select a.*,isnull(b.name,'部门不明确') as departmentNamefrom [user] aleft 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] ajoin [department] b on a.departmentid=b.idgroup by b.name--3.用一条T-sql语句，查询哪个部门人数最多，要是有相等的就找出部门id数最小的那个select top 1 b.name,count(*) as 人数from [user] ajoin [department] b on a.departmentid=b.idgroup by b.id,b.nameorder 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)) &lt;&gt; '' then d.name ELSE '部门不明确' END namefrom 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 user1join 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.namefrom c1 join department d on c1.id = d.id where c1.departmentSum = (select MAX(c1.departmentSum) from c1)

求好手帮忙写几条sql语句新手有困难

热点推荐