建三张表。
1.
Code
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[employee](
[id] [bigint] IDENTITY(1,1) NOT NULL,
[name] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[sportitem](
[id] [int] IDENTITY(1,1) NOT NULL,
[name] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[empchooseitem](
[employee] [bigint] NOT NULL,
[item] [int] NOT NULL
) ON [PRIMARY]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[employee](
[id] [bigint] IDENTITY(1,1) NOT NULL,
[name] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[sportitem](
[id] [int] IDENTITY(1,1) NOT NULL,
[name] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[empchooseitem](
[employee] [bigint] NOT NULL,
[item] [int] NOT NULL
) ON [PRIMARY]
列出报名最多的那个项目的人员列表
sql:
Code
select e.name from employee e, empchooseitem ec
where e.id=ec.employee and
ec.item=
(
select l.item from( select count(employee) c,h.item item
from empchooseitem h
group by h.item) l
where l.c in
(select max(f.c) from (select count(employee) c,b.item item
from empchooseitem b
group by b.item) f
)
)
2.统计每项报名人数
select e.name from employee e, empchooseitem ec
where e.id=ec.employee and
ec.item=
(
select l.item from( select count(employee) c,h.item item
from empchooseitem h
group by h.item) l
where l.c in
(select max(f.c) from (select count(employee) c,b.item item
from empchooseitem b
group by b.item) f
)
)
sql:
select
a.name,c.humannum
from
sportitem a,
( select b.item bitem, count (
b.employee
) humanNum from
empchooseitem b
group by b.item ) c
where a.id = c.bitem
( select b.item bitem, count (
b.employee
) humanNum from
empchooseitem b
group by b.item ) c
where a.id = c.bitem