tblSetup结构不好这样,不好处理 这样比较好 no timebegin timeend mm 001 6:00 7:00 0.5 002 12:00 13:00 2 003 18:00 19:00 2 select tblSetup.mm where tblList.timeyc between timebegin and timeendinsert into 表(no name date1 m)思路
select T1.no,T1.name,T1.timeyc,T2.m1 from tblList T1, tblSetup T2 where (T2.time1 <= T1.timeyc) and (T2.time11 >= T1.timeyc)Unionselect T1.no,T1.name,T1.timeyc,T2.m2 from tblList T1, tblSetup T2 where (T2.time2 <= T1.timeyc) and (T2.time22 >= T1.timeyc)Unionselect T1.no,T1.name,T1.timeyc,T2.m3 from tblList T1, tblSetup T2 where (T2.time3 <= T1.timeyc) and (T2.time33 >= T1.timeyc) order by T1.no , T1.timeyc
搞错了一点select T1.no,T1.name,T1.timeyc,T2.m1 as mm from tblList T1, tblSetup T2 where (T2.time1 <= T1.timeyc) and (T2.time11 >= T1.timeyc)Unionselect T1.no,T1.name,T1.timeyc,T2.m2 as mm from tblList T1, tblSetup T2 where (T2.time2 <= T1.timeyc) and (T2.time22 >= T1.timeyc)Unionselect T1.no,T1.name,T1.timeyc,T2.m3 as mm from tblList T1, tblSetup T2 where (T2.time3 <= T1.timeyc) and (T2.time33 >= T1.timeyc) order by T1.no , T1.timeyc
To taidy() : 你的SQL好像應這樣: select mm from tblSetup,tblList where tblList.tt_time between timebegin and timeend 但tblList.tt_time類型是smalldatetime 而tblSetup.begin和tblSetup.begin類型是utime (char) 匹配不到呀
试试看 BEGIN TRANSACTION tmptran DECLARE @datetime datetime, @date varchar(10) , @time varchar(8) , @no varchar(10) , @name varchar(10) , @m varchar(10) DECLARE tmpcur SCROLL CURSOR FOR SELECT no,name,timeyc FROM tblList ORDER BY no FOR READ ONLY OPEN tmpcur FETCH FIRST FROM tmpcur INTO @no,@name,@datetime WHILE (@@fetch_status = 0) BEGIN SELECT @date = CONVERT(varchar(10),@datetime,120) SELECT @time = CONVERT(varchar(8),@datetime,108) SELECT @time = LTRIM(STR(CONVERT(int,LEFT(@time,2)))) SELECT @m = CASE WHEN @time IN (LEFT(time1,1),LEFT(time11,1) THEN m1 WHEN @time IN (LEFT(time2,2),LEFT(time22,2) THEN m2 WHEN @time IN (LEFT(time3,2),LEFT(time33,2) THEN m3 ELSE 'novalue' END FROM tblsetup WHERE no = @no IF (@m <> 'novalue') ADN (@m IS NOT NULL) INSERT INTO newtable(no,name,date1,m) VALUES (@no,@name,@date,@m) FETCH NEXT FROM tmpcur INTO @no,@name,@datetime END CLOSE tmpcur DEALLOCATE tmpcur IF (@@error = 0) COMMIT tmptran ELSE ROLLBACK tmptran
一条SQL语句即可搞定,可惜又臭又长,如下:SELECT [no], name, CONVERT(varchar(10), timeyc, 101) AS date1, CASE WHEN CONVERT(varchar(10), timeyc, 8) >= (SELECT CONVERT(varchar(10), time1, 8) FROM tblsetup) AND CONVERT(varchar(10), timeyc, 8) <= (SELECT CONVERT(varchar(10), time11, 8) FROM tblsetup) THEN (SELECT m1 FROM tblsetup) WHEN CONVERT(varchar(10), timeyc, 8) >= (SELECT CONVERT(varchar(10), time2, 8) FROM tblsetup) AND CONVERT(varchar(10), timeyc, 8) <= (SELECT CONVERT(varchar(10), time22, 8) FROM tblsetup) THEN (SELECT m2 FROM tblsetup) WHEN CONVERT(varchar(10), timeyc, 8) >= (SELECT CONVERT(varchar(10), time3, 8) FROM tblsetup) AND CONVERT(varchar(10), timeyc, 8) <= (SELECT CONVERT(varchar(10), time33, 8) FROM tblsetup) THEN (SELECT m3 FROM tblsetup) END AS m FROM tbllist
可以啊
能否把你的想法詳細一點?
这样比较好
no timebegin timeend mm
001 6:00 7:00 0.5
002 12:00 13:00 2
003 18:00 19:00 2 select tblSetup.mm where tblList.timeyc between
timebegin and timeendinsert into 表(no name date1 m)思路
select T1.no,T1.name,T1.timeyc,T2.m1
from tblList T1, tblSetup T2
where (T2.time1 <= T1.timeyc) and
(T2.time11 >= T1.timeyc)Unionselect T1.no,T1.name,T1.timeyc,T2.m2
from tblList T1, tblSetup T2
where (T2.time2 <= T1.timeyc) and
(T2.time22 >= T1.timeyc)Unionselect T1.no,T1.name,T1.timeyc,T2.m3
from tblList T1, tblSetup T2
where (T2.time3 <= T1.timeyc) and
(T2.time33 >= T1.timeyc)
order by T1.no , T1.timeyc
from tblList T1, tblSetup T2
where (T2.time1 <= T1.timeyc) and
(T2.time11 >= T1.timeyc)Unionselect T1.no,T1.name,T1.timeyc,T2.m2 as mm
from tblList T1, tblSetup T2
where (T2.time2 <= T1.timeyc) and
(T2.time22 >= T1.timeyc)Unionselect T1.no,T1.name,T1.timeyc,T2.m3 as mm
from tblList T1, tblSetup T2
where (T2.time3 <= T1.timeyc) and
(T2.time33 >= T1.timeyc)
order by T1.no , T1.timeyc
你的SQL好像應這樣:
select mm from tblSetup,tblList where tblList.tt_time between timebegin and timeend
但tblList.tt_time類型是smalldatetime
而tblSetup.begin和tblSetup.begin類型是utime (char)
匹配不到呀
BEGIN TRANSACTION tmptran
DECLARE @datetime datetime,
@date varchar(10) ,
@time varchar(8) ,
@no varchar(10) ,
@name varchar(10) ,
@m varchar(10)
DECLARE tmpcur SCROLL CURSOR
FOR SELECT no,name,timeyc
FROM tblList
ORDER BY no
FOR READ ONLY
OPEN tmpcur
FETCH FIRST FROM tmpcur INTO @no,@name,@datetime
WHILE (@@fetch_status = 0)
BEGIN
SELECT @date = CONVERT(varchar(10),@datetime,120)
SELECT @time = CONVERT(varchar(8),@datetime,108)
SELECT @time = LTRIM(STR(CONVERT(int,LEFT(@time,2))))
SELECT @m = CASE
WHEN @time IN (LEFT(time1,1),LEFT(time11,1) THEN m1
WHEN @time IN (LEFT(time2,2),LEFT(time22,2) THEN m2
WHEN @time IN (LEFT(time3,2),LEFT(time33,2) THEN m3
ELSE 'novalue'
END
FROM tblsetup
WHERE no = @no
IF (@m <> 'novalue') ADN (@m IS NOT NULL)
INSERT INTO newtable(no,name,date1,m)
VALUES (@no,@name,@date,@m)
FETCH NEXT FROM tmpcur INTO @no,@name,@datetime
END
CLOSE tmpcur
DEALLOCATE tmpcur
IF (@@error = 0)
COMMIT tmptran
ELSE
ROLLBACK tmptran
而tblSetup.timebegin和tblSetup.timeend只有時間 06:00
所以匹配不到呀
CASE WHEN CONVERT(varchar(10), timeyc, 8) >=
(SELECT CONVERT(varchar(10), time1, 8)
FROM tblsetup) AND CONVERT(varchar(10), timeyc, 8) <=
(SELECT CONVERT(varchar(10), time11, 8)
FROM tblsetup) THEN
(SELECT m1
FROM tblsetup) WHEN CONVERT(varchar(10), timeyc, 8) >=
(SELECT CONVERT(varchar(10), time2, 8)
FROM tblsetup) AND CONVERT(varchar(10), timeyc, 8) <=
(SELECT CONVERT(varchar(10), time22, 8)
FROM tblsetup) THEN
(SELECT m2
FROM tblsetup) WHEN CONVERT(varchar(10), timeyc, 8) >=
(SELECT CONVERT(varchar(10), time3, 8)
FROM tblsetup) AND CONVERT(varchar(10), timeyc, 8) <=
(SELECT CONVERT(varchar(10), time33, 8)
FROM tblsetup) THEN
(SELECT m3
FROM tblsetup) END AS m
FROM tbllist
某些考勤系统的数据库的确要这么设计,不过tblsetup表的记录不应该只有一条,原因很简单,一个公司员工的上下班时间不可能一样!
例如:普通员工、保安、清洁工、职员、仓管员的上下班时间都不一致,而且要考虑加班,情况就比价麻烦!还有其它很多问题......可以互相联系:[email protected]