谢谢现在这个语句保存为视图时出现这个错误了,怎么解决?
SELECT TOP 10000000 ygbh, ygxm, CONVERT(varchar(10), punchtime, 120) AS punchtime,
MIN(punchtime) AS '打卡记录'
FROM kaoqin_1
GROUP BY ygbh, ygxm, CONVERT(varchar(10), punchtime, 120)
UNION ALL
SELECT TOP 10000000 ygbh, ygxm, CONVERT(varchar(10), punchtime, 120) AS time,
MAX(punchtime) AS '打卡记录'
FROM kaoqin_1
GROUP BY ygbh, ygxm, CONVERT(varchar(10), punchtime, 120)
ORDER BY ygbh, punchtime
Error:
-------------------------------------------------------------
10415如果语句中包含UNION运算符,那么ORDERBY子句中的项就必须出现在选择列表中。 (o%be[!
SELECT TOP 10000000 ygbh, ygxm, CONVERT(varchar(10), punchtime, 120) AS punchtime,
MIN(punchtime) AS '打卡记录'
FROM kaoqin_1
GROUP BY ygbh, ygxm, CONVERT(varchar(10), punchtime, 120)
UNION ALL
SELECT TOP 10000000 ygbh, ygxm, CONVERT(varchar(10), punchtime, 120) AS time,
MAX(punchtime) AS '打卡记录'
FROM kaoqin_1
GROUP BY ygbh, ygxm, CONVERT(varchar(10), punchtime, 120)
ORDER BY ygbh, punchtime
Error:
-------------------------------------------------------------
10415如果语句中包含UNION运算符,那么ORDERBY子句中的项就必须出现在选择列表中。 (o%be[!
(
SELECT TOP 10000000 ygbh, ygxm, CONVERT(varchar(10), punchtime, 120) AS punchtime,
MIN(punchtime) AS '打卡记录'
FROM kaoqin_1
GROUP BY ygbh, ygxm, CONVERT(varchar(10), punchtime, 120)
UNION ALL
SELECT TOP 10000000 ygbh, ygxm, CONVERT(varchar(10), punchtime, 120) AS time,
MAX(punchtime) AS '打卡记录'
FROM kaoqin_1
GROUP BY ygbh, ygxm, CONVERT(varchar(10), punchtime, 120)
)
ORDER BY ygbh, punchtime
select name, case datediff(hour, convert(varchar, time, 111), time) when 9 then
convert(varchar, time, 111) else
dateadd(second, -1, dateadd(day, 1, time)) end
from Table1 awhere (select count(1) from Table1 where name = a.name and
convert(varchar, time, 111) = convert(varchar, a.time, 111)) = 1
UNION ALL
SELECT TOP 10000000 ygbh, ygxm, CONVERT(varchar(10), punchtime, 120) AS time,
不是As time而应该改为As punchtime
select 1, '李' , '2006-10-10 09:00:00'
union all select 2, '李' , '2006-10-10 18:00:00'
union all select 3, '李' , '2006-10-11 09:00:00'
union all select 4, '李' , '2006-10-12 09:00:00'
union all select 5, '李' , '2006-10-12 18:00:00'select * from @tselect a.id,a.name , a.time into #temp from @t a,
(
select name , convert(varchar(10) , time , 120) as time , count(*) as times from @t
group by name , convert(varchar(10) , time , 120)
having count(*) = 1
) b
where a.name = b.name and convert(varchar(10) , a.time , 120) = b.timeselect * from #tempdrop table #tempid name time
----------- ---------- ------------------------------------------------------
3 李 2006-10-11 09:00:00.000(所影响的行数为 1 行)
我只能做到这一步,后面的不知道怎么搞了.
建议用程序写吧.
select name, case when datediff(hour, convert(varchar, time, 111), time) >= 12 then
dateadd(second, 1, cast(time as int)) else
dateadd(second, -1, dateadd(day, 1, cast(time as int))) end
from Table1 a
where (select count(1) from Table1 where name = a.name and
convert(varchar, time, 111) = convert(varchar, a.time, 111)) = 1
insert into @t
select 1, '李' , '2006-10-10 09:00:00'
union all select 2, '李' , '2006-10-10 18:00:00'
union all select 3, '李' , '2006-10-11 09:00:00'
union all select 4, '李' , '2006-10-12 09:00:00'
union all select 5, '李' , '2006-10-12 18:00:00'
union all select 6, '李' , '2006-10-13 18:00:00'
--select * from @t
--生成一个带ID1自增加的临时表
select id1=identity(int,1,1) , a.id,a.name , a.time into #temp from @t a,
(
select name , convert(varchar(10) , time , 120) as time , count(*) as times from @t
group by name , convert(varchar(10) , time , 120)
having count(*) = 1
) b
where a.name = b.name and convert(varchar(10) , a.time , 120) = b.time--select * from #temp
--循环获取数据并判断时间大小
declare @i as int
declare @max1 as int
declare @max2 as int
declare @name as varchar(10)
declare @mydatetime as datetime
declare @mydatetimestring as varchar(20)set @i = 1
select @max1 = count(*) from #temp
select @max2 = count(*) from @twhile @i <= @max1
begin
set @max2 = @max2 + 1
select @name = name from #temp where id1 = @i
select @mydatetime = time from #temp where id1 = @i
if convert(varchar(10),@mydatetime ,114) < '12:00:00'
begin
set @mydatetimestring = convert(varchar(10) , @mydatetime , 120) + ' 23:59:59'
insert into @t select @max2 , @name , @mydatetimestring
end
else
begin
set @mydatetimestring = convert(varchar(10) , @mydatetime , 120) + ' 00:00:00'
insert into @t select @max2 , @name , @mydatetimestring
end
set @i = @i + 1
endselect * from @t order by name ,time
drop table #tempid name time
----------- ---------- ------------------------------------------------------
1 李 2006-10-10 09:00:00.000
2 李 2006-10-10 18:00:00.000
3 李 2006-10-11 09:00:00.000
7 李 2006-10-11 23:59:59.000
4 李 2006-10-12 09:00:00.000
5 李 2006-10-12 18:00:00.000
8 李 2006-10-13 00:00:00.000
6 李 2006-10-13 18:00:00.000(所影响的行数为 8 行)