T_student
------------------
student_id names
1001 王二
1002 张三
1003 李四
------------------
feedback
------------------------
student_id dqcs yxjg
1001 1 2
1002 1 3
1001 2 1
1001 3 4
1002 2 1
1002 3 1
1002 4 4
1003 1 1
----------------------------结果是:
------------------------------
student_id names dqcs yxjg
1001 王二 3 4
1002 张三 4 4
1003 李四 1 1
--------------------------------先行谢过了!!!
------------------
student_id names
1001 王二
1002 张三
1003 李四
------------------
feedback
------------------------
student_id dqcs yxjg
1001 1 2
1002 1 3
1001 2 1
1001 3 4
1002 2 1
1002 3 1
1002 4 4
1003 1 1
----------------------------结果是:
------------------------------
student_id names dqcs yxjg
1001 王二 3 4
1002 张三 4 4
1003 李四 1 1
--------------------------------先行谢过了!!!
解决方案 »
- 触发器问题
- SQL中,针对字符型字段、和非主键字段如何制定分区方案
- bcp导入文本数据时,想让表中的第3列不装载数据, 我在格式文件中将第3个字段的服务器列名设为0, 但发现第3列依然有数据?
- 求助,好像比较难,谢谢!
- 存储过程提取表中的某条记录
- 大哥帮忙看看,难题求大家共解,小弟恭候
- 视图里面用sum
- 关于SQL2005数据库数据同步更新的问题!求教!
- 这个错误怎么解决?[Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]未能为数据库 'TEMPDB' 中的对象 '(SYSTEM table id: -79616
- SOS!紧急求助!
- 跟据EXECL数据更新SQLSERVER数据库中数据怎么实现?
- use db-name ??
t.*,f.dqcs,f.yxjg
from
T_student t,feedback f
where
t.student_id=f.student_id
and
not exists(select 1 from feedback where student_id=t.student_id and dqcs>f.dqcs)
select a.*,max(b.dqcs) as dqcs,max(b.yxjg) as yxjg
from T_student a,feedback b
where a.student_id=b.student_id
group by a.student_id,a.Names
select a.*,b.dqcs,b.yxjg
from T_student a,feedback b
where a.student_id=b.student_id
and not exists (
select 1 from feedback where student_id=b.student_id
and yxjg>b.yxjg)select a.*,b.dqcs,b.yxjg
from T_student a,feedback b
where a.student_id=b.student_id
and not exists (
select 1 from feedback where student_id=b.student_id
and dqcs>b.dqcs)
Select a.*,b.dqcs,b.yxjg
from T_student a
left join (select * from
feedback c
where not exists(select 1 from feedback
where c.student_id = student_id and dqcs > c.dqcs) b
on a.student_id = b.student_id
insert into @a select 1001,'王二'
insert into @a select 1002,'张三'
insert into @a select 1003,'李四'
declare @b table (stuid int,dqcs int,yxjg int)
insert into @b select 1001,1,2
insert into @b select 1002,1,3
insert into @b select 1001,2,1
insert into @b select 1001,3,4
insert into @b select 1002,2,1
insert into @b select 1002,3,1
insert into @b select 1002,4,4
insert into @b select 1003,1,1select a.stuid,stuname,max(dqcs) as dqcs,max(yxjg) as yxjg from
@a a left join @b b on a.stuid=b.stuid
group by a.stuid,stuname
order by a.stuidstuid stuname dqcs yxjg
1001 王二 3 4
1002 张三 4 4
1003 李四 1 1
from T_student a left join feedback b on a.student_id=b.student_id
group by a.student_id, a.Names
insert into @a select 1001,'王二'
insert into @a select 1002,'张三'
insert into @a select 1003,'李四'
declare @b table (stuid int,dqcs int,yxjg int)
insert into @b select 1001,1,2
insert into @b select 1002,1,3
insert into @b select 1001,2,1
insert into @b select 1001,3,4
insert into @b select 1002,2,1
insert into @b select 1002,3,1
insert into @b select 1002,4,4
--增加一行干扰数据
insert into @b select 1002,3,6
insert into @b select 1003,1,1--求出都是最大的数据
select a.stuid,stuname,max(dqcs) as dqcs,max(yxjg) as yxjg from
@a a left join @b b on a.stuid=b.stuid
group by a.stuid,stuname
order by a.stuid--求出最大dqcs 所在行的yxjg
select a.stuid,a.stuname,d.dqcs,d.yxjg from @a a,(
select b.stuid,b.dqcs,max(b.yxjg) yxjg from @b b,
(select stuid,max(dqcs) dqcs from @b group by stuid) c
where b.stuid = c.stuid and b.dqcs = c.dqcs
group by b.stuid,b.dqcs ) d
where a.stuid = d.stuid
/*
stuid stuname dqcs yxjg
----------- ---------- ----------- -----------
1001 王二 3 4
1002 张三 4 6
1003 李四 1 1(所影响的行数为 3 行)stuid stuname dqcs yxjg
----------- ---------- ----------- -----------
1003 李四 1 1
1001 王二 3 4
1002 张三 4 4(所影响的行数为 3 行)*/
select a.stuid,stuname,max(dqcs) as dqcs,max(yxjg) as yxjg from
T_student a left join feedback b on a.stuid=b.stuid
group by a.stuid,stuname
order by a.stuid
--把@a换成T_student,@b换成feedback
insert into t_student values(1001, '王二')
insert into t_student values(1002, '张三')
insert into t_student values(1003, '李四')
create table feedback (student_id int, dqcs int, yxjg int)
insert into feedback values(1001, 1, 2 )
insert into feedback values(1002, 1, 3 )
insert into feedback values(1001, 2, 1 )
insert into feedback values(1001, 3, 4 )
insert into feedback values(1002, 2, 1 )
insert into feedback values(1002, 3, 1 )
insert into feedback values(1002, 4, 4 )
insert into feedback values(1003, 1, 1 )
goselect a.* , max(b.dqcs) dqcs , max(yxjg) yxjg from t_student a,feedback b where a.student_id = b.student_id group by a.student_id , a.names order by a.student_iddrop table t_student,feedback/*
student_id names dqcs yxjg
----------- ---------- ----------- -----------
1001 王二 3 4
1002 张三 4 4
1003 李四 1 1(所影响的行数为 3 行)
*/
drop table t_student,feedback完整如下:create table t_student (student_id int, names varchar(10))
insert into t_student values(1001, '王二')
insert into t_student values(1002, '张三')
insert into t_student values(1003, '李四')
create table feedback (student_id int, dqcs int, yxjg int)
insert into feedback values(1001, 1, 2 )
insert into feedback values(1002, 1, 3 )
insert into feedback values(1001, 2, 1 )
insert into feedback values(1001, 3, 4 )
insert into feedback values(1002, 2, 1 )
insert into feedback values(1002, 3, 1 )
insert into feedback values(1002, 4, 4 )
insert into feedback values(1003, 1, 1 )
goselect a.* , max(b.dqcs) dqcs , max(yxjg) yxjg from t_student a,feedback b where a.student_id = b.student_id group by a.student_id , a.names order by a.student_id--drop table t_student,feedback/*
student_id names dqcs yxjg
----------- ---------- ----------- -----------
1001 王二 3 4
1002 张三 4 4
1003 李四 1 1(所影响的行数为 3 行)
*/
insert into t_student values(1001, '王二')
insert into t_student values(1002, '张三')
insert into t_student values(1003, '李四')
create table feedback (student_id int, dqcs int, yxjg int)
insert into feedback values(1001, 1, 2 )
insert into feedback values(1002, 1, 3 )
insert into feedback values(1001, 2, 1 )
insert into feedback values(1001, 3, 4 )
insert into feedback values(1002, 2, 1 )
insert into feedback values(1002, 3, 1 )
insert into feedback values(1002, 4, 4 )
insert into feedback values(1003, 1, 1 )
goselect a.* , max(b.dqcs) dqcs , max(yxjg) yxjg from t_student a,feedback b where a.student_id = b.student_id group by a.student_id , a.names order by a.student_id
from T_student a inner join feedback b on a.student_id=b.student_id
create table t_student
(
student_id varchar2(10),
names varchar2(10)
);create table feedback
(
student_id varchar2(10),
dpcs varchar2(5),
yxjg varchar2(5)
)insert into t_student values ('1001','wanger');
insert into t_student values ('1002','zhangsan');
insert into t_student values ('1003','lisi');insert into feedback values ('1001','1','2');
insert into feedback values ('1002','1','3');
insert into feedback values ('1001','2','1');
insert into feedback values ('1001','3','4');
insert into feedback values ('1002','2','1');
insert into feedback values ('1002','3','1');
insert into feedback values ('1002','4','4');
insert into feedback values ('1003','1','1');select t.student_id as student_id,names as names, count(f.dpcs) as dpcs,max(f.yxjg) as yxjg from
t_student t left join feedback f on t.student_id = f.student_id
group by t.student_id,t.names
order by t.student_id;
from T_student a inner join feedback b on a.student_id=b.student_id
group by a.studentid, a.names
order by a.studentid
t.*,f.dqcs,f.yxjg
from
T_student t,feedback f
where
t.student_id=f.student_id
and
not exists(select 1 from feedback where student_id=t.student_id and dqcs>f.dqcs)
IsNull((select count(*) from feedback
where student_id=t.student_id),0) as dqcs,
IsNull((select max(yxjg) from feedback
where student_id=t.sutdent_id),0) as yxjg
from T_student t
declare @T_student table
(
student_id int
,names nvarchar(20)
)
insert into @T_student
select 1001,'王二'
union all
select 1002, '张三'
union all
select 1003, '李四'declare @feedback table
(
student_id int
,dqcs int
,yxjg int
)
insert into @feedback
select 1001, 1, 2
union all
select 1002, 1, 3
union all
select 1001, 2, 1
union all
select 1001, 3, 4
union all
select 1002, 2, 1
union all
select 1002, 3, 1
union all
select 1002, 4, 4
union all
select 1003, 1, 1 select a.student_id
,a.names
,count(a.student_id) as sudqcs
,max(b.yxjg) as yxjg
from @T_student as a
inner join @feedback as b
on a.student_id=b.student_id
group by a.student_id,a.names