select empid,case when empdate = + empdate + then empdate +'' + emptime
FROM (SELECT empdate,emptime FROM a)
解决方案 »
- bcp里面的format文件生成的问题
- SQL数据库引擎 删除用户登录名的痕迹
- 怎样给数据库增加用户,并设定权限?
- 在select的显示列中有一个ntext列,查询就很慢,这怎么办?
- 误删除了一个存储过程,能否恢复
- 一个表中有入库记录和出库记录,用收、发字段来标示,如何求余额
- 在用于GROUP BY子句分组依据列表的表达式中不能使用聚合或子查询。
- 跨表查询的问题1
- sqlserver 安装出错.......着急啊
- 单表的触发器问题 -- 在线等待大牛。。。
- 如何高效使用存储过程按需要读取数据?请各位高手讲解,小弟诚谢!在线等。。
- 数据库备份。假设有两个数据库sjk1和sjk2。sjk1中有admin表。我想把sjk1中的admin存到sjk2中,
min(case when (select count(*) from a where empid=x.empid and empno=x.empno and empdate=empdate and emptime<=x.emptime)=1 then empdate+' '+emptime end) as t1,
min(case when (select count(*) from a where empid=x.empid and empno=x.empno and empdate=empdate and emptime<=x.emptime)=2 then empdate+' '+emptime end) as t2,
min(case when (select count(*) from a where empid=x.empid and empno=x.empno and empdate=empdate and emptime<=x.emptime)=3 then empdate+' '+emptime end) as t3,
min(case when (select count(*) from a where empid=x.empid and empno=x.empno and empdate=empdate and emptime<=x.emptime)=4 then empdate+' '+emptime end) as t4
from a x
group by empid,empno,empdate
order by empid,empno,empdate
伺服器: 訊息 130,層級 15,狀態 1,行 1
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.
伺服器: 訊息 170,層級 15,狀態 1,行 3
Line 3: Incorrect syntax near '='.
伺服器: 訊息 170,層級 15,狀態 1,行 4
Line 4: Incorrect syntax near '='.
伺服器: 訊息 170,層級 15,狀態 1,行 5
Line 5: Incorrect syntax near '='.
declare @int int,@a int,@b varchar(20),@c datetime
update c set @int=case when @a=empid and @b=empno and @c=empdate then @int+1 else 1 end,@a=empid,@b=empno,@c=empdate,row=@intdeclare @sql varchar(8000)
set @sql='select empid,empno'
select @sql=@sql+',case row when ''' + cast(row as varchar(10)) + ''' then convert(varchar(11),empdate,120)+emptime else null end as C'+ cast(row as varchar(10))
from (select distinct row from c) as ccselect @sql=@sql+' from c group by empid,empno,convert(varchar(11),empdate,120)+emptime,empdate,emptime,row'
exec (@sql)
declare @int int,@a int,@b varchar(20),@c datetime
update c set @int=case when @a=empid and @b=empno and @c=empdate then @int+1 else 1 end,@a=empid,@b=empno,@c=empdate,row=@intdeclare @sql varchar(8000)
set @sql='select empid,empno'
select @sql=@sql+',case row when ''' + cast(row as varchar(10)) + ''' then convert(varchar(11),empdate,120)+emptime else null end as t'+ cast(row as varchar(10))
from (select distinct row from c) as ccselect @sql=@sql+' from c group by empid,empno,convert(varchar(11),empdate,120)+emptime,empdate,emptime,row'
exec (@sql)
....
表示所属的列在新加列中的位置 1表示在第一列,2表示在第2列....
create function getstr(@empid int,@empno varchar(20),@empdate datetime)
returns varchar(2000)
as
begin
declare @str varchar(2000)
set @str=''
select @str=@str+','+convert(varchar(11),empdate,120)+emptime from a where empid=@empid and empno=@empno and empdate=@empdate
set @str=right(@str,len(@str)-1)
return @str
end
go
select empid,empno,dbo.getstr(empid,empno,empdate) areas from a group by empid,empno,empdate
empid int,
empno varchar(20),
empdate varchar(20),
emptime varchar(20)
)
goinsert #a
select
1756, 'AA000042', '2002-05-19', '60328'
union all
select
1756, 'AA000042', '2002-05-19', '60332'
union all
select
1756, 'AA000042', '2002-05-19', '60336'
union all
select
1756, 'AA000042', '2002-05-19', '60340'
union all
select
1756, 'AA000042', '2002-05-20', '29755'
union all
select
1756, 'AA000042', '2002-06-11', '41864'
union all
select
1756, 'AA000042', '2002-06-11', '50900'
union all
select
1757, 'AA000021', '2002-05-20', '30200'
union all
select
1759, 'AA000010', '2002-12-14', '59400'
union all
select
1759, 'AA000010', '2002-12-28', '79740'
union all
select
1759, 'AA000010', '2003-04-03', '63360'
union all
select
1760, 'AA000011', '2002-05-22', '45005'
union all
select
1760, 'AA000011', '2002-05-22', '48408'
union all
select
1760, 'AA000011', '2002-05-22', '63005'
union all
select
1760, 'AA000011', '2002-05-22', '66441'
go
--语句
select empid,empno,
min(t1) as t1,
min(t2) as t2,
min(t3) as t3,
min(t4) as t4
from (
select empid,empno,empdate,
case when (select count(*) from #a where empid=x.empid and empno=x.empno and empdate=x.empdate and emptime<=x.emptime)=1 then empdate+' '+emptime end as t1,
case when (select count(*) from #a where empid=x.empid and empno=x.empno and empdate=x.empdate and emptime<=x.emptime)=2 then empdate+' '+emptime end as t2,
case when (select count(*) from #a where empid=x.empid and empno=x.empno and empdate=x.empdate and emptime<=x.emptime)=3 then empdate+' '+emptime end as t3,
case when (select count(*) from #a where empid=x.empid and empno=x.empno and empdate=x.empdate and emptime<=x.emptime)=4 then empdate+' '+emptime end as t4
from #a x
) as y
group by empid,empno,empdate
order by empid,empno,empdate
go结果:
empid empno t1 t2 t3 t4
----------- -------------------- ----------------------------------------- ----------------------------------------- ----------------------------------------- -----------------------------------------
1756 AA000042 2002-05-19 60328 2002-05-19 60332 2002-05-19 60336 2002-05-19 60340
1756 AA000042 2002-05-20 29755 NULL NULL NULL
1756 AA000042 2002-06-11 41864 2002-06-11 50900 NULL NULL
1757 AA000021 2002-05-20 30200 NULL NULL NULL
1759 AA000010 2002-12-14 59400 NULL NULL NULL
1759 AA000010 2002-12-28 79740 NULL NULL NULL
1759 AA000010 2003-04-03 63360 NULL NULL NULL
1760 AA000011 2002-05-22 45005 2002-05-22 48408 2002-05-22 63005 2002-05-22 66441(所影响的行数为 8 行)