先写第一个把:create table T_STUDENT( STUDENT_ID int , NAME varchar(20), SEX int, AGE int)insert into T_STUDENT select 1 ,'张三', 0 , 20 union all select 2 ,'李四', 1 , 18
create table T_CLASS(CLASS_ID int, NAME varchar(20))insert into T_CLASS select 1 ,'语文' union all select 2 ,'数学' union all select 3 ,'英语'create table T_SCORE(SCORE_ID int,STUDENT_ID int, CLASS_ID int, SCORE int)insert into T_SCORE select 1 , 1 , 1 , 96 union all select 2 , 1 , 2 , 90 union all select 3 , 2 , 1 , 96 goselect * from ( select ts.STUDENT_ID,tc.CLASS_ID from T_STUDENT ts inner join T_CLASS tc on 1=1 except select STUDENT_ID,CLASS_ID from T_SCORE )t inner join T_STUDENT ts on ts.STUDENT_ID = t.STUDENT_ID inner join T_CLASS tc on tc.CLASS_ID = t.CLASS_ID order by t.STUDENT_ID /* STUDENT_ID CLASS_ID STUDENT_ID NAME SEX AGE CLASS_ID NAME 1 3 1 张三 0 20 3 英语 2 3 2 李四 1 18 3 英语 2 2 2 李四 1 18 2 数学 */
等着小当家你来啊 ,,一个就写了这么多, 看来40分少了点 呵呵,简化一下: select * from T_STUDENT ts inner join T_CLASS tc on 1=1 where not exists(select 1 from T_SCORE t where t.STUDENT_ID = ts.STUDENT_ID and t.CLASS_ID = tc.CLASS_ID)/* STUDENT_ID NAME SEX AGE CLASS_ID NAME 1 张三 0 20 3 英语 2 李四 1 18 2 数学 2 李四 1 18 3 英语 */
create table T_STUDENT( STUDENT_ID int , NAME varchar(20), SEX int, AGE int)insert into T_STUDENT select 1 ,'张三', 0 , 20 union all select 2 ,'李四', 1 , 18
create table T_CLASS(CLASS_ID int, NAME varchar(20))insert into T_CLASS select 1 ,'语文' union all select 2 ,'数学' union all select 3 ,'英语'create table T_SCORE(SCORE_ID int,STUDENT_ID int, CLASS_ID int, SCORE int)insert into T_SCORE select 1 , 1 , 1 , 96 union all select 2 , 1 , 2 , 90 union all select 3 , 2 , 1 , 96 godeclare @sql varchar(max)='';select @sql=@sql+ N', sum(case when className=N'''+name+''' then score else 0 end) as '+namefrom [dbo].[T_CLASS]set @sql=' with t as ( select b.NAME,c.NAME className,a.SCORE from [dbo].[T_SCORE] a join [T_STUDENT] b on a.STUDENT_ID=b.STUDENT_ID join [dbo].[T_CLASS] c on a.CLASS_ID=c.CLASS_ID ) select name '+@sql+',sum(score) as [总分] from t group by name';Exec (@sql) 我来写一下第二道
第2个:declare @sql varchar(1000)set @sql = ''select @sql = @sql + ',max(case when tc.name= '''+ NAME +''' then SCORE else null end) as ' + NAME from T_CLASSset @sql = 'select ts.name '+@sql+',sum(SCORE) as 总分 '+ 'from T_STUDENT ts inner join T_SCORE t on ts.student_id = t.student_id inner join T_CLASS tc on tc.class_id = t.class_id group by ts.student_id,ts.name order by 总分 desc'exec(@sql) /* name 语文 数学 英语 总分 张三 96 90 NULL 186 李四 96 NULL NULL 96 */
等着小当家你来啊 ,,一个就写了这么多, 看来40分少了点 呵呵,简化一下: select * from T_STUDENT ts inner join T_CLASS tc on 1=1 where not exists(select 1 from T_SCORE t where t.STUDENT_ID = ts.STUDENT_ID and t.CLASS_ID = tc.CLASS_ID)/* STUDENT_ID NAME SEX AGE CLASS_ID NAME 1 张三 0 20 3 英语 2 李四 1 18 2 数学 2 李四 1 18 3 英语 */ 辛苦啦,, 好吧, 我再加30分
呵呵,第二个:declare @sql varchar(1000)set @sql = ''select @sql = @sql + ',max(case when tc.name= '''+ NAME +''' then SCORE else null end) as ' + NAME from T_CLASSset @sql = 'select ts.name '+@sql+',sum(SCORE) as 总分 '+ 'from T_STUDENT ts inner join T_SCORE t on ts.student_id = t.student_id inner join T_CLASS tc on tc.class_id = t.class_id group by ts.student_id,ts.name order by 总分 desc'exec(@sql) /* name 语文 数学 英语 总分 张三 96 90 NULL 186 李四 96 NULL NULL 96 */
--就在这里记录啦, 谢谢几位大牛啦 --问题一: select ts.name,t.name from T_STUDENT ts inner join T_class t on 1=1 where not exists (select 1 from T_score where STUDENT_ID=ts.STUDENT_ID and Class_ID=t.Class_ID) --问题二declare @sql varchar(1000)
set @sql = ''
select @sql = @sql + ',max(case when tc.name= '''+ NAME +''' then SCORE else '''' end) as ' + NAME from T_CLASS
set @sql = 'select ts.name '+@sql+',sum(SCORE) as 总分 '+ 'from T_STUDENT ts inner join T_SCORE t on ts.student_id = t.student_id inner join T_CLASS tc on tc.class_id = t.class_id group by ts.student_id,ts.name order by 总分 desc' print @sql exec(@sql)
STUDENT_ID int ,
NAME varchar(20),
SEX int,
AGE int)insert into T_STUDENT
select 1 ,'张三', 0 , 20 union all
select 2 ,'李四', 1 , 18
create table T_CLASS(CLASS_ID int, NAME varchar(20))insert into T_CLASS
select 1 ,'语文' union all
select 2 ,'数学' union all
select 3 ,'英语'create table T_SCORE(SCORE_ID int,STUDENT_ID int, CLASS_ID int, SCORE int)insert into T_SCORE
select 1 , 1 , 1 , 96 union all
select 2 , 1 , 2 , 90 union all
select 3 , 2 , 1 , 96
goselect *
from
(
select ts.STUDENT_ID,tc.CLASS_ID
from T_STUDENT ts
inner join T_CLASS tc
on 1=1
except
select STUDENT_ID,CLASS_ID
from T_SCORE
)t
inner join T_STUDENT ts
on ts.STUDENT_ID = t.STUDENT_ID
inner join T_CLASS tc
on tc.CLASS_ID = t.CLASS_ID
order by t.STUDENT_ID
/*
STUDENT_ID CLASS_ID STUDENT_ID NAME SEX AGE CLASS_ID NAME
1 3 1 张三 0 20 3 英语
2 3 2 李四 1 18 3 英语
2 2 2 李四 1 18 2 数学
*/
呵呵,简化一下:
select *
from T_STUDENT ts
inner join T_CLASS tc
on 1=1
where not exists(select 1 from T_SCORE t
where t.STUDENT_ID = ts.STUDENT_ID and
t.CLASS_ID = tc.CLASS_ID)/*
STUDENT_ID NAME SEX AGE CLASS_ID NAME
1 张三 0 20 3 英语
2 李四 1 18 2 数学
2 李四 1 18 3 英语
*/
create table T_STUDENT(
STUDENT_ID int ,
NAME varchar(20),
SEX int,
AGE int)insert into T_STUDENT
select 1 ,'张三', 0 , 20 union all
select 2 ,'李四', 1 , 18
create table T_CLASS(CLASS_ID int, NAME varchar(20))insert into T_CLASS
select 1 ,'语文' union all
select 2 ,'数学' union all
select 3 ,'英语'create table T_SCORE(SCORE_ID int,STUDENT_ID int, CLASS_ID int, SCORE int)insert into T_SCORE
select 1 , 1 , 1 , 96 union all
select 2 , 1 , 2 , 90 union all
select 3 , 2 , 1 , 96
godeclare @sql varchar(max)='';select @sql=@sql+ N', sum(case when className=N'''+name+''' then score else 0 end) as '+namefrom [dbo].[T_CLASS]set @sql='
with t as
(
select b.NAME,c.NAME className,a.SCORE
from [dbo].[T_SCORE] a
join [T_STUDENT] b on a.STUDENT_ID=b.STUDENT_ID
join [dbo].[T_CLASS] c on a.CLASS_ID=c.CLASS_ID
)
select name
'+@sql+',sum(score) as [总分]
from t
group by name';Exec (@sql)
我来写一下第二道
',max(case when tc.name= '''+ NAME +''' then SCORE else null end) as ' + NAME
from T_CLASSset @sql = 'select ts.name '+@sql+',sum(SCORE) as 总分 '+
'from T_STUDENT ts
inner join T_SCORE t
on ts.student_id = t.student_id
inner join T_CLASS tc
on tc.class_id = t.class_id
group by ts.student_id,ts.name
order by 总分 desc'exec(@sql)
/*
name 语文 数学 英语 总分
张三 96 90 NULL 186
李四 96 NULL NULL 96
*/
呵呵,简化一下:
select *
from T_STUDENT ts
inner join T_CLASS tc
on 1=1
where not exists(select 1 from T_SCORE t
where t.STUDENT_ID = ts.STUDENT_ID and
t.CLASS_ID = tc.CLASS_ID)/*
STUDENT_ID NAME SEX AGE CLASS_ID NAME
1 张三 0 20 3 英语
2 李四 1 18 2 数学
2 李四 1 18 3 英语
*/
辛苦啦,, 好吧, 我再加30分
呵呵,第二个:declare @sql varchar(1000)set @sql = ''select @sql = @sql +
',max(case when tc.name= '''+ NAME +''' then SCORE else null end) as ' + NAME
from T_CLASSset @sql = 'select ts.name '+@sql+',sum(SCORE) as 总分 '+
'from T_STUDENT ts
inner join T_SCORE t
on ts.student_id = t.student_id
inner join T_CLASS tc
on tc.class_id = t.class_id
group by ts.student_id,ts.name
order by 总分 desc'exec(@sql)
/*
name 语文 数学 英语 总分
张三 96 90 NULL 186
李四 96 NULL NULL 96
*/
--就在这里记录啦, 谢谢几位大牛啦
--问题一:
select ts.name,t.name from T_STUDENT ts inner join T_class t on 1=1 where not exists (select 1 from T_score where STUDENT_ID=ts.STUDENT_ID and Class_ID=t.Class_ID)
--问题二declare @sql varchar(1000)
set @sql = ''
select @sql = @sql +
',max(case when tc.name= '''+ NAME +''' then SCORE else '''' end) as ' + NAME
from T_CLASS
set @sql = 'select ts.name '+@sql+',sum(SCORE) as 总分 '+
'from T_STUDENT ts
inner join T_SCORE t
on ts.student_id = t.student_id
inner join T_CLASS tc
on tc.class_id = t.class_id
group by ts.student_id,ts.name
order by 总分 desc'
print @sql
exec(@sql)