34,588
社区成员
发帖
与我相关
我的任务
分享
create table exam(
examid int,
examname varchar(20),
examPrice int,
begin_time datetime,
end_time datetime
)
create table exam_user(
euId int,
userId varchar(20),
examId int
)
insert exam
select 1,'j2SE',20,'2008-05-04','2008-08-20' union all
select 2,'j2EE',40,'2008-02-15','2008-7-05' union all
select 3,'j2ME',50,'2008-01-18','2008-3-30' union all
select 4,'C',30,'2009-05-20','2009-08-20' union all
select 5,'C++',50,'2009-03-22','2009-08-29' union all
select 6,'C#',40,'2009-12-20','2010-08-20'
insert exam_user
select 1,'张三',1 union all
select 2,'张三',2 union all
select 3,'张三',3 union all
select 4,'李四',1 union all
select 5,'李四',2 union all
select 6,'李四',3 union all
select 7,'王五',1 union all
select 8,'王五',4 union all
select 9,'王五',3 union all
select 10,'张三',6 union all
select 11,'王五',5
select * from exam
select * from exam_user
select A.examname,COUNT(*) countPerson from (select examname from exam inner join exam_user on exam.examid=exam_user.examId) A
group by A.examname
-------------------
examname countPerson
C 1
C# 1
C++ 1
j2EE 2
j2ME 3
j2SE 3
--借用下数据
select exam.*,t.countperson
from exam
join
(
select examId,count(*) as countperson
from exam_user
group by examId
having count(*) >=2
) t on exam.examid=t.examId
/*
examId examName examPrice beginTime endTime countperson
----------- -------- ----------- ----------------------- ----------------------- -----------
1 j2SE 20 2008-05-04 00:00:00.000 2008-08-20 00:00:00.000 3
2 j2EE 40 2008-02-15 00:00:00.000 2008-07-05 00:00:00.000 2
3 j2ME 50 2008-01-18 00:00:00.000 2008-03-30 00:00:00.000 3
(3 行受影响)
*/
--借用下数据
select exam.*,t.countperson
from exam
join
(
select examId,count(*) as countperson
from exam_user
group by examId
) t on exam.examid=t.examId and t.countperson>=2
/*
examId examName examPrice beginTime endTime countperson
----------- -------- ----------- ----------------------- ----------------------- -----------
1 j2SE 20 2008-05-04 00:00:00.000 2008-08-20 00:00:00.000 3
2 j2EE 40 2008-02-15 00:00:00.000 2008-07-05 00:00:00.000 2
3 j2ME 50 2008-01-18 00:00:00.000 2008-03-30 00:00:00.000 3
(3 行受影响)
*/
----------------------------------------------------------------
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2010-02-04 11:15:24
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[exam]
if object_id('[exam]') is not null drop table [exam]
go
create table [exam]([examId] int,[examName] varchar(4),[examPrice] int,[beginTime] datetime,[endTime] datetime)
insert [exam]
select 1,'j2SE',20,'2008-05-04','2008-08-20' union all
select 2,'j2EE',40,'2008-02-15','2008-7-05' union all
select 3,'j2ME',50,'2008-01-18','2008-3-30' union all
select 4,'C',30,'2009-05-20','2009-08-20' union all
select 5,'C++',50,'2009-03-22','2009-08-29' union all
select 6,'C#',40,'2009-12-20','2010-08-20'
--> 测试数据:[exam_user]
if object_id('[exam_user]') is not null drop table [exam_user]
go
create table [exam_user]([euId] int,[userId] varchar(4),[examId] int)
insert [exam_user]
select 1,'张三',1 union all
select 2,'张三',2 union all
select 3,'张三',3 union all
select 4,'李四',1 union all
select 5,'李四',2 union all
select 6,'李四',3 union all
select 7,'王五',1 union all
select 8,'王五',4 union all
select 9,'王五',3 union all
select 10,'张三',6 union all
select 11,'王五',5
--------------开始查询--------------------------
select
examName,count(1) as countPerson
from
exam a
left join exam_user b
on
a.examid=b.examid
group by
examName
having count(1)>=2
----------------结果----------------------------
/*examName countPerson
-------- -----------
j2EE 2
j2ME 3
j2SE 3
(3 行受影响)
*/
---测试数据---
if object_id('[exam]') is not null drop table [exam]
go
create table [exam]([examId] int,[examName] varchar(4),[examPrice] int,[beginTime] datetime,[endTime] datetime)
insert [exam]
select 1,'j2SE',20,'2008-05-04','2008-08-20' union all
select 2,'j2EE',40,'2008-02-15','2008-7-05' union all
select 3,'j2ME',50,'2008-01-18','2008-3-30' union all
select 4,'C',30,'2009-05-20','2009-08-20' union all
select 5,'C++',50,'2009-03-22','2009-08-29' union all
select 6,'C#',40,'2009-12-20','2010-08-20'
if object_id('[exam_user]') is not null drop table [exam_user]
go
create table [exam_user]([euId] int,[userId] varchar(4),[examId] int)
insert [exam_user]
select 1,'张三',1 union all
select 2,'张三',2 union all
select 3,'张三',3 union all
select 4,'李四',1 union all
select 5,'李四',2 union all
select 6,'李四',3 union all
select 7,'王五',1 union all
select 8,'王五',4 union all
select 9,'王五',3 union all
select 10,'张三',6 union all
select 11,'王五',5
---查询---
SELECT A.examName,COUNT(1) AS countPerson
FROM exam A
JOIN exam_user B
ON A.examId=B.examId
GROUP BY A.examName
HAVING COUNT(1)>=2
---结果---
examName countPerson
-------- -----------
j2EE 2
j2ME 3
j2SE 3
(所影响的行数为 3 行)
--写少了
select t.examName,isnull(r.countPerson,0) as countPerson
from exam t left join
(select examId,count(1) as countPerson from exam_user group by examId) r
on t.examId=r.examId
where isnull(r.countPerson,0) >= 2
select exam.*,t.countperson
from exam
join
(
select examId,count(*) as countperson
from exam_user
group by examId
) t on exam.examid=t.examId and t.countperson>=2
select t.examName,isnull(r.countPerson,0) as countPerson
from exam t left join
(select examId,count(1) as countPerson from exam_user) r
on t.examId=r.examId
where isnull(r.countPerson,0) >= 2
SELECT A.examName,COUNT(1) AS countPerson
FROM exam A
JOIN exam_user B
ON A.examId=B.examId
HAVING COUNT(1)>=2
SELECT examName,count(1) from
exam a INNER JOIN exam_user b
ON a.examid=b.examid
GROUP BY examName
HAVING count(1)>2