解决方案 »
- 小孩打过乙肝预防针了,会不会还会被传染?
- 怎么从浪潮财务软件数据库里得到总资产SQL代码?
- 新数据库服务器,要怎么引用旧服务器里的用户、DTS和Job?
- 这条存储过程为什么没有体现出速度优势啊
- 求一简单SQL语句
- Execel2007 VBA 连接到SQL2008时出现无效的连接 提示
- 如何判断一个字符串,如果全部是数字,则返回该数值,如果有其它字符,返回0。有这样的函数么?
- 如何将类型分别为text和varchar的两列内容相加?
- SQL 导出File 时 Format选择 Ragged right 时,得到int类型占用11个字符,bit类型占用10个,为什么?能用T_SQL得到吗?
- 请教:用mdf怎么恢复或者建立一个完整的数据库,急,谢谢大家
- 关于sqlserver2005迁移数据的问题
- 求一条sql语句
if object_id('[tb]') is not null drop table [tb]
create table [tb] (empName varchar(6),sizName varchar(3),axeName int,thrumNum int,time datetime,value int)
insert into [tb]
select '杨爱忠','1号',532,4018,'2010-07-08',500 union all
select '余开保','1号',532,4018,'2010-07-09',400 union all
select '杨爱忠','1号',255,4018,'2010-07-10',300
;with cte as (select *,rn=ROW_NUMBER()over(order by empName)from tb)
select t1.empName ,t1.sizName,t1.axeName,t1.thrumNum,t1.time,t1.value,t2.empName ,t2.sizName,t2.axeName,t2.thrumNum,t2.time,t2.value
from (select * from cte where rn%2=1) t1
left join (select * from cte where rn%2=0) t2 on t1.rn=t2.rn-1/*
empName sizName axeName thrumNum time value empName sizName axeName thrumNum time value
杨爱忠 1号 532 4018 2010-07-08 00:00:00.000 500 杨爱忠 1号 255 4018 2010-07-10 00:00:00.000 300
余开保 1号 532 4018 2010-07-09 00:00:00.000 400 NULL NULL NULL NULL NULL NULL
*/
create table tb(empName varchar(10),sizName varchar(10),axeName int,thrumNum int,time datetime,value int)
insert into tb values('杨爱忠', '1号', 532 ,4018 ,'2010-07-08', 500)
insert into tb values('余开保', '1号', 532 ,4018 ,'2010-07-09', 400)
insert into tb values('杨爱忠', '1号', 255 ,4018 ,'2010-07-10', 300)
goselect empName,
max(case px when 1 then sizName end) sizName,
max(case px when 1 then axeName end) axeName,
max(case px when 1 then thrumNum end) thrumNum,
max(case px when 1 then time end) time,
max(case px when 1 then value end) value,
empName,
max(case px when 2 then sizName end) sizName,
max(case px when 2 then axeName end) axeName,
max(case px when 2 then thrumNum end) thrumNum,
max(case px when 2 then time end) time,
max(case px when 2 then value end) value
from
(
select t.* , px = (select count(1) from tb where empName = t.empName and time < t.time) + 1 from tb t
) m
group by empNamedrop table tb/*
empName sizName axeName thrumNum time value empName sizName axeName thrumNum time value
---------- ---------- ----------- ----------- ------------------------------------------------------ ----------- ---------- ---------- ----------- ----------- ------------------------------------------------------ -----------
杨爱忠 1号 532 4018 2010-07-08 00:00:00.000 500 杨爱忠 1号 255 4018 2010-07-10 00:00:00.000 300
余开保 1号 532 4018 2010-07-09 00:00:00.000 400 余开保 NULL NULL NULL NULL NULL(所影响的行数为 2 行)
*/
--sql 2000
create table tb(empName varchar(10),sizName varchar(10),axeName int,thrumNum int,time datetime,value int)
insert into tb values('杨爱忠', '1号', 532 ,4018 ,'2010-07-08', 500)
insert into tb values('余开保', '1号', 532 ,4018 ,'2010-07-09', 400)
insert into tb values('杨爱忠', '1号', 255 ,4018 ,'2010-07-10', 300)
goselect m.empName,m.sizName,m.axeName,m.thrumNum,m.time,m.value,
n.empName,n.sizName,n.axeName,n.thrumNum,n.time,n.value
from
(select t.* , px = (select count(1) from tb where empName = t.empName and time < t.time) + 1 from tb t) m
left join
(select t.* , px = (select count(1) from tb where empName = t.empName and time < t.time) + 1 from tb t) n
on m.empname = n.empname and n.px = 2
where m.px = 1drop table tb/*
empName sizName axeName thrumNum time value empName sizName axeName thrumNum time value
---------- ---------- ----------- ----------- ------------------------------------------------------ ----------- ---------- ---------- ----------- ----------- ------------------------------------------------------ -----------
杨爱忠 1号 532 4018 2010-07-08 00:00:00.000 500 杨爱忠 1号 255 4018 2010-07-10 00:00:00.000 300
余开保 1号 532 4018 2010-07-09 00:00:00.000 400 NULL NULL NULL NULL NULL NULL(所影响的行数为 2 行)
*/
create table tb(empName nvarchar(10),sizName nvarchar(10),axeName int,thrumNum int,time datetime,value int)
insert into tb values(N'杨爱忠', N'1号', 532 ,4018 ,'2010-07-08', 500)
insert into tb values(N'余开保', N'1号', 532 ,4018 ,'2010-07-09', 400)
insert into tb values(N'杨爱忠', N'1号', 255 ,4018 ,'2010-07-10', 300)
goselect m.empName,m.sizName,m.axeName,m.thrumNum,m.time,m.value,
n.empName,n.sizName,n.axeName,n.thrumNum,n.time,n.value
from
(select t.* , px = row_number() over(partition by empName order by time) from tb t) m
left join
(select t.* , px = row_number() over(partition by empName order by time) from tb t) n
on m.empname = n.empname and n.px = 2
where m.px = 1
order by m.timedrop table tb/*
empName sizName axeName thrumNum time value empName sizName axeName thrumNum time value
---------- ---------- ----------- ----------- ----------------------- ----------- ---------- ---------- ----------- ----------- ----------------------- -----------
杨爱忠 1号 532 4018 2010-07-08 00:00:00.000 500 杨爱忠 1号 255 4018 2010-07-10 00:00:00.000 300
余开保 1号 532 4018 2010-07-09 00:00:00.000 400 NULL NULL NULL NULL NULL NULL(2 行受影响)
*/