declare @t table(name1 varchar(10), subject1 varchar(10), subject2 varchar(10), score1 int, score2 int)
insert @t
select '张三', '数学', '语文', 80, 60 union all
select '李四', '数学', '语文', 90, 40 union all
select '王五', '数学', '语文', 50, 70select 科目,姓名 from
(select 科目='数学',姓名='',编号=1 union all
select '语文','',2 union all
select '',name1,1 from @T where score1>=60 union all
select '',name1,2 from @t where score2>=60)t
order by t.编号,t.姓名/*结果
数学
张三
李四
语文
张三
王五*/
insert @t
select '张三', '数学', '语文', 80, 60 union all
select '李四', '数学', '语文', 90, 40 union all
select '王五', '数学', '语文', 50, 70select 科目,姓名 from
(select 科目='数学',姓名='',编号=1 union all
select '语文','',2 union all
select '',name1,1 from @T where score1>=60 union all
select '',name1,2 from @t where score2>=60)t
order by t.编号,t.姓名/*结果
数学
张三
李四
语文
张三
王五*/
(select top 1 科目=subject1,姓名='',编号=1 from @t
union all
select top 1 科目=subject2,'',2 from @t
union all
select '',name1,1 from @T where score1>=60
union all
select '',name1,2 from @t where score2>=60)t
order by t.编号,t.姓名