第一个表User
UserID UserName Gender
------------------------
1 "张" 1
2 "李" 0
3 "王" 1
第二个表Exam
UserID Score
------------------
1 90
1 96
2 88
3 90
3 80
3 70
想要得到的结果是:
UserID ExamAmount
--------------------
1 2
2 1
3 3
就是得到User中每个用户的考试次数
UserID UserName Gender
------------------------
1 "张" 1
2 "李" 0
3 "王" 1
第二个表Exam
UserID Score
------------------
1 90
1 96
2 88
3 90
3 80
3 70
想要得到的结果是:
UserID ExamAmount
--------------------
1 2
2 1
3 3
就是得到User中每个用户的考试次数
解决方案 »
- sql求优化
- sqlserver2008 开发版本的企业管理器哪儿有啊?请前辈指导!
- sql语句或存储过程怎么写
- nvarchar值在select时找不到???
- 一台服务器的一个数据库中的表通过存储过程如何写入另一台服务器的一个数据库中的表
- 请教
- sql2005 编码 汉字显示为??急,急,急
- 大家遇到处理钱的问题时候,会用什么字段
- 征求软件升级的意见:dephi3--->5 , MS SQLServer6.5--->2000 (有用的建议都有分)
- 菜鸟认为是超高难问题:请问用ado+sql server 2000做的c/s大程序的高手帮帮忙,我使用ado+sql server 2000做的c/s程序的网络情况比较复杂
- 求sql语句
- sql语句的简单问题
from Exam
group by userid
第一个表User
UserID UserName Gender
------------------------
1 "张" 1
2 "李" 0
3 "王" 1
4 "赵" 1
第二个表Exam
UserID Score
------------------
1 90
1 96
2 88
3 90
3 80
3 70
想要得到的结果是:
UserID ExamAmount UserName
--------------------
1 2 张
2 1 李
3 3 王
4 0 赵
a.userid,
isnull(b.ExamAmount,0) as ExamAmount,
a.username
from
a
jion
(select userid,count(1) as ExamAmount from Exam group by userid) b
on
a.userid=b.userid
left join
(
select UserID,count(Score) ExamAmount from Exam group by UserID
) E
on U.UserID=E.UserID
left join
(
select UserID,count(Score) ExamAmount from Exam group by UserID
) E
on U.UserID=E.UserID
FROM [User] A LEFT JOIN (SELECT UserId,COUNT(*) ExamAmount FROM Exam GROUP BY UserID) B
ON A.Userid=B.UserId
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-11-16 12:36:01
-- 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)
--
----------------------------------------------------------------
--> 测试数据:[User]
if object_id('[User]') is not null drop table [User]
go
create table [User]([UserID] int,[UserName] varchar(2),[Gender] int)
insert [User]
select 1,'张',1 union all
select 2,'李',0 union all
select 3,'王',1 union all
select 4,'赵',1
--> 测试数据:[Exam]
if object_id('[Exam]') is not null drop table [Exam]
go
create table [Exam]([UserID] int,[Score] int)
insert [Exam]
select 1,90 union all
select 1,96 union all
select 2,88 union all
select 3,90 union all
select 3,80 union all
select 3,70
--------------开始查询--------------------------
select
isnull(a.UserID,b.UserID) as UserID,isnull(b.ExamAmount,0) as ExamAmount,a.UserName
from
[User] a
left join
(select UserID,count(1) as ExamAmount from Exam group by UserID)b
on
a.UserID=b.UserID
----------------结果----------------------------
/* UserID ExamAmount UserName
----------- ----------- --------
1 2 张
2 1 李
3 3 王
4 0 赵(4 行受影响)
*/
from Exam
group by userid
from Exam
group by userid
from Exam a right join user b on a.userid=b.userid
group by b.userid
create table [user]
(
UserID int,
UserName varchar(8),
Gender varchar(4)
)
gocreate table Exam
(
UserID INT,
Score int
)
goinsert into [user]
select 1, '张', 1 union all
select 2, '李', 0 union all
select 3, '王', 1
go
select * from exam
insert into [Exam]
select 1, 90 union all
select 1, 96 union all
select 2, 88 union all
select 3, 90 union all
select 3, 80 union all
select 3, 70
go
select e.userid,count(e.score),u.UserName from exam e left join [user] u on e.UserID=u.UserID group by e.userid,u.UserName order by e.userid
第三个表xuehao id
----------------
2001 1
2002 2
2003 3
2004 4xuehao sex
----------------
2001 "1"
2002 "2"
2003 "2"
2004 "1"
查询结果UserID ExamAmount UserName sex
----------------------------------------
1 2 张 1
2 1 李 2
3 3 王 2
4 0 赵 1
又该如何查询呢??谢谢!!
补充一下,现整理如下:
第一个表User
UserID UserName
----------------
1 "张"
2 "李"
3 "王"
4 "赵"
第二个表Exam
UserID Score
------------------
1 90
1 96
2 88
3 90
3 80
3 70
4 50第三张表
xuehao id
----------------
2001 1
2002 2
2003 3
2004 4 第四张表
xuehao sex
----------------
2001 "1"
2002 "2"
2003 "2"
2004 "1"
查询结果 UserID ExamAmount UserName sex xuehao
----------------------------------------
1 2 张 1 2001
2 1 李 2 2002
3 3 王 2 2003
4 0 赵 1 2004
from Exam
group by userid
left join
(select UserID,count(UserID) sum_userid from [Exam] group by UserID) as b
on a.UserID = b.UserID
select a.UserID ,a.UserName ,
(select count(UserID) sum_userid from [Exam] where UserID=a.UserID)
from [User] as a
select 1, '张', 1 union all
select 2, '李', 0 union all
select 3, '王', 1
insert [Exam]
select 1,90 union all
select 1,96 union all
select 2,88 union all
select 3,90 union all
select 3,80 union all
select 3,70select user.id,count(*)as ExamAmount ,user.UserName from user inner join Exam on(user.id=Exam.id)
groud by user.id user.UserName