数据库面试题

Question

两张表userinfo deptinfouserid username loginstate deptid
1      a        0          1
2      b        1          1
3      c        0          1
4      d        1          1
5      e        1          2
6      f        0          2
7      g        1          3
8      h        1          3deptid deptname
1      开发部
2      市场部
3      客服部
要求结果deptname totalnum loginnum
开发部    4        2
市场部    2        1
客服部    2        2
这是我做的，有很大的问题，不知道该怎么办了，各位大哥给说的详细一点啊，100分奉上select * from 
(select distinct deptinfo.deptname 
from userinfo,deptinfo  where userinfo.deptid = deptinfo.deptid) deptname,
(select count(*) totalnum from userinfo group by deptid) totalnum,
(select count(*) loginnum from userinfo where loginstate = 1 group by deptid) loginnum

Answer 1

DECLARE @TA TABLE([userid] INT, [username] VARCHAR(1), [loginstate] INT, [deptid] INT)
INSERT @TA 
SELECT 1, 'a', 0, 1 UNION ALL 
SELECT 2, 'b', 1, 1 UNION ALL 
SELECT 3, 'c', 0, 1 UNION ALL 
SELECT 4, 'd', 1, 1 UNION ALL 
SELECT 5, 'e', 1, 2 UNION ALL 
SELECT 6, 'f', 0, 2 UNION ALL 
SELECT 7, 'g', 1, 3 UNION ALL 
SELECT 8, 'h', 1, 3DECLARE @TB TABLE([deptid] INT, [deptname] NVARCHAR(3))
INSERT @TB 
SELECT 1, N'开发部' UNION ALL 
SELECT 2, N'市场部' UNION ALL 
SELECT 3, N'客服部'SELECT [deptname],COUNT(*) AS totalnum,SUM(loginstate) AS loginnum 
FROM @TB AS B LEFT JOIN @TA AS A
  ON B.[deptid]=A.[deptid]
GROUP BY [deptname]
/*
deptname totalnum    loginnum
-------- ----------- -----------
客服部      2           2
市场部      2           1
开发部      4           2
*/

Answer 2

--加order byDECLARE @TA TABLE([userid] INT, [username] VARCHAR(1), [loginstate] INT, [deptid] INT)
INSERT @TA 
SELECT 1, 'a', 0, 1 UNION ALL 
SELECT 2, 'b', 1, 1 UNION ALL 
SELECT 3, 'c', 0, 1 UNION ALL 
SELECT 4, 'd', 1, 1 UNION ALL 
SELECT 5, 'e', 1, 2 UNION ALL 
SELECT 6, 'f', 0, 2 UNION ALL 
SELECT 7, 'g', 1, 3 UNION ALL 
SELECT 8, 'h', 1, 3DECLARE @TB TABLE([deptid] INT, [deptname] NVARCHAR(3))
INSERT @TB 
SELECT 1, N'开发部' UNION ALL 
SELECT 2, N'市场部' UNION ALL 
SELECT 3, N'客服部'SELECT [deptname],COUNT(*) AS totalnum,SUM(loginstate) AS loginnum 
FROM @TB AS B LEFT JOIN @TA AS A
  ON B.[deptid]=A.[deptid]
GROUP BY B.deptid ,[deptname]
ORDER BY B.deptid 
/*
deptname totalnum    loginnum
-------- ----------- -----------
开发部      4           2
市场部      2           1
客服部      2           2
*/

Answer 3

select 
deptname,
count(deptid) as totalnum,
sum(
case when loginstate = 1 then 1
else 0
end
)loginnum
from userinfo
inner join deptinfo on deptinfo.deptid = userinfo.deptid
group by deptname

Answer 4

--> 测试数据: @userinfo
declare @userinfo table (userid int,username varchar(1),loginstate int,deptid int)
insert into @userinfo
select 1,'a',0,1 union all
select 2,'b',1,1 union all
select 3,'c',0,1 union all
select 4,'d',1,1 union all
select 5,'e',1,2 union all
select 6,'f',0,2 union all
select 7,'g',1,3 union all
select 8,'h',1,3
--> 测试数据: @deptinfo
declare @deptinfo table (deptid int,deptname varchar(6))
insert into @deptinfo
select 1,'开发部' union all
select 2,'市场部' union all
select 3,'客服部' union all
select 4,'技术部'
select b.deptname,count(*) as totalnum,sum(case when loginstate=1 then 1 else 0 end) as loginnum 
from @userinfo as a
inner join @deptinfo b on a.deptid=b.deptid
group by deptname,b.deptid
order by b.deptid/*
开发部 4 2
市场部 2 1
客服部 2 2
*/select b.deptname,count(*) as totalnum,sum(a.loginstate) as loginnum 
from @userinfo as a
inner join @deptinfo b on a.deptid=b.deptid
group by deptname,b.deptid
order by b.deptid/*
开发部 4 2
市场部 2 1
客服部 2 2
*/
select a.deptname,count(*) as totalnum,sum(case when loginstate=1 then 1 else 0 end) as loginnum 
from @deptinfo as a
left join @userinfo b on a.deptid=b.deptid
group by a.deptname,a.deptid
order by a.deptid/*
开发部 4 2
市场部 2 1
客服部 2 2
技术部 1 0
*/

Answer 5

--创建测试数据
CREATE TABLE LI1 ([userid] INT, [username] VARCHAR(1), [loginstate] INT, [deptid] INT)
INSERT LI1([USERID],[USERNAME],[LOGINSTATE],[DEPTID]) 
SELECT 1, 'a', 0, 1 UNION ALL 
SELECT 2, 'b', 1, 1 UNION ALL 
SELECT 3, 'c', 0, 1 UNION ALL 
SELECT 4, 'd', 1, 1 UNION ALL 
SELECT 5, 'e', 1, 2 UNION ALL 
SELECT 6, 'f', 0, 2 UNION ALL 
SELECT 7, 'g', 1, 3 UNION ALL 
SELECT 8, 'h', 1, 3CREATE TABLE LI2([deptid] INT, [deptname] NVARCHAR(3))
INSERT LI2([deptid], [deptname]) 
SELECT 1, N'开发部' UNION ALL 
SELECT 2, N'市场部' UNION ALL 
SELECT 3, N'客服部'
--查询结果
SELECT [deptname],COUNT(*) AS totalnum,SUM(loginstate) AS loginnum 
FROM LI1 AS B LEFT JOIN LI2 AS A
  ON B.[deptid]=A.[deptid]
GROUP BY B.deptid ,[deptname]
ORDER BY B.deptid 
/*deptname totalnum  loginnum
  开发部    4        2
  市场部    2        1
  客服部    2        2
*/

Answer 6

不能用count(*),再来：
--> 测试数据: @userinfo
declare @userinfo table (userid int,username varchar(1),loginstate int,deptid int)
insert into @userinfo
select 1,'a',0,1 union all
select 2,'b',1,1 union all
select 3,'c',0,1 union all
select 4,'d',1,1 union all
select 5,'e',1,2 union all
select 6,'f',0,2 union all
select 7,'g',1,3 union all
select 8,'h',1,3
--> 测试数据: @deptinfo
declare @deptinfo table (deptid int,deptname varchar(6))
insert into @deptinfo
select 1,'开发部' union all
select 2,'市场部' union all
select 3,'客服部' union all
select 4,'技术部'select a.deptname,sum(case when b.deptid is null then 0 else 1 end) as totalnum,
sum(case when loginstate=1 then 1 else 0 end) as loginnum 
from @deptinfo as a
left join @userinfo b on a.deptid=b.deptid
group by a.deptname,a.deptid
order by a.deptid/*
开发部 4 2
市场部 2 1
客服部 2 2
技术部 0 0
*/

Answer 7

declare @usr table
(
userid  int,
username varchar(1),
loginstate bit,
deptid int
);
declare @dep table
(
deptid int,
deptname  nvarchar(20)
);insert into @usr
values
(1,'a',0,1 ),
(2,'b',1,1 ),
(3,'c',0,1 ),
(4,'d',1,1 ),
(5,'e',1,2 ),
(6,'f',0,2 ),
(7,'g',1,3 ),
(8,'h',1,3 );insert into @dep
values
(1,'开发部' ),
(2,'市场部'),
(3,'客服部' );select max(t2.deptname) as deptname
   ,count(t1.deptid) as totalnum
   ,(
select COUNT(loginstate)
from @usr t3
where t3.deptid=t1.deptid and t3.loginstate=1
) as loginnum
from @usr  t1
 join @dep t2 on t1.deptid=t2.deptid 
group by t1.deptid