Question

求SQL高手，解答SQL语句！

两张表 company和user表，表间关系自己设定。字段自己设定。要求用SQL语句查询出1、小李的单位负责人
2、删除没有员工的公司。
3、员工数量最多的公司名称和员工数量。

Answer 1

假设
company表中有
company_id 公司编号
company_name 公司名称
company_manager 公司负责人

user表中有
user_id 员工编号
user_name 员工姓名
user_cid 所属公司编号

1.select company_manager from company where company_id =(select user_cid from user where user_name='小李')

2.delete from user where user_cid not in (select company_id from company)

3.select top 1 a.company_name,b.count1 from company as a,(select user_cid,count(*) as count1 from user group by user_cid) as b where a.company_id=b.user_cid
order by b.count1 desc

Answer 2

我看到回复比问题还长的时候，通常都比较佩服

Answer 3

1.SELECT COMPANY.负责人 FROM COMPANY INNER JOIN USER ON COMPANY.ID=USER.公司ID WHERE B.姓名=‘小李’
2.DELETE FROM COMPANY WHERE ID NOT IN(SELECT 公司ID FROM USER)
3.SELECT TOP 1 COMPANY.公司名称，COUNT(USER.ID) FROM COMPANY INNER JOIN USER ON COMPANY.ID=USER.公司ID GROUP BY COMPANY.公司名称 HAVING BY 2 DESC