解决方案 »
- SQL联合查询 怎样将两张表中的相关信息更新第三张表中
- 对查询增加序列号的办法(晒晒,是否方便呢)
- 要實現動態的參數表,如何在SQL Server上建表啊?
- 小弟初学,问一个数据库导入的问题
- 求写一个sql函数,获取时间最大值
- 简单问题,初学者,mssql中,存储过程、视图、自定义函数,有什么分别呢?还有其使用各有什么限制
- 求SQL统计语句
- sql server错误提示信息
- 我在调用SQL2000的存储过程时,提示“开头的 标识符 太长。最大长度为 128”,但在SQL70没有这样的问题。2000上怎么解决?(在线等待)
- EF Model First 如何在模型中加入非主键或外键的 索引和约束???
- 有关基于Wince中的数据库数据显示问题
- 存储过程问题
select a.sno,a.scgrade as 课程1,b.scgrade as 课程2
from
(select * from tablename where cno = 1) a inner join
(select * from tablename where cno = 2) b on a.sno = b.sno
where a.scgrade > b.scgrade
order by SNO,CNO,SCGrade desc
if object_id('tempdb.dbo.#tb') is not null drop table #tb
go
create table #tb (SNO int,CNO int,SCGrade int)
insert into #tb
select 1,1,55 union all
select 1,2,51 union all
select 2,1,40 union all
select 2,2,30 union all
select 3,3,90--1、
select sno,cj1=max(case when cno=1 then SCGrade end),
cj2=max(case when cno=2 then SCGrade end)
from #tb
group by sno
having(max(case when cno=1 then SCGrade end)>max(case when cno=2 then SCGrade end))--2、
select * from
(
select sno,cj1=max(case when cno=1 then SCGrade end),
cj2=max(case when cno=2 then SCGrade end)
from #tb
group by sno
)t
where cj1>cj2--sno cj1 cj2
------------- ----------- -----------
--1 55 51
--2 40 30
--警告: 聚合或其他 SET 操作消除了空值。
--
--(2 行受影响)
create table tb(sno int,cno int ,score int)
insert into tb
select 1,1,55 union all
select 1,2,51 union all
select 2,1,40 union all
select 2,2,30 union all
select 3,3,90
*/
select tb.* from (
select case when score-(select score from tb where cno=2 and sno=t.sno)>0 then sno end as sno from tb t where cno=1)a
inner join tb
on a.sno=tb.sno/*1 1 55
1 2 51
2 1 40
2 2 30*/
from (select * from #tb where CNO=1) a, (select * from #tb where CNO=2) b where a.SNO=b.SNO and a.SCGrade>b.SCGrade
(
SELECT SNO,SUM(CASE CNO WHEN 1 THEN SCGRADE ELSE 0 END) as SCGRADE1
,SUM(CASE CNO WHEN 2 THEN SCGRADE ELSE 0 END) as SCGRADE2
FROM T
GROUP BY SNO
) T
WHERE T.SCGRADE1> T.SCGRADE2这个也行