作者热门文章
- html - 出于某种原因,IE8 对我的 Sass 文件中继承的 html5 CSS 不友好?
- JMeter 在响应断言中使用 span 标签的问题
- html - 在 :hover and :active? 上具有不同效果的 CSS 动画
- html - 相对于居中的 html 内容固定的 CSS 重复背景?
我正在编写一条 sql 语句,用户应在其中输入“起始日期”和“截止日期”(少于 31 天)对于每个日期,我需要从与员工相关的表中选择字段
我需要一个简单方法的逻辑。应该等一会儿吧?
我通过这个查询得到了日期列表:
DECLARE @date TABLE(d DATETIME)
DECLARE @d DATETIME
SET @d = '20090101'
WHILE @d <= '20090102' BEGIN
INSERT INTO @date VALUES (@d)
SET @d = @d + 1
END
SELECT d AS DateCol, dbo.ta_DayOfWeek(d) AS Day,
DATENAME(weekday, DATEADD(day,0,d)) AS DayName FROM @date
这些是我需要为每条记录使用的一些字段:
SELECT ta_timecard.EmpID ,
emp.name ,
emp.code ,
ta_timecard.schedindate--,
FROM ta_timecard
INNER JOIN emp ON ta_timecard.empid = emp.id
WHERE ta_timecard.schedindate BETWEEN @FromDate AND @ToDate
GROUP BY ta_timecard.EmpID ,
emp.name ,
emp.code ,
ta_timecard.schedindate
提前致谢。
最佳答案
试试这个 -
查询:
DECLARE
@DateFrom DATETIME
, @DateTo DATETIME
SELECT
@DateFrom = '20130101'
, @DateTo = '20130202'
SELECT
DateCol
, [DayName] = DATENAME(WEEKDAY, DateCol)
, [Day] = DATEPART(WEEKDAY, DateCol)
FROM (
SELECT DateCol = DATEADD(DAY, sv.number, t.DateFrom)
FROM (
SELECT
DateFrom = @DateFrom
, diff = DATEDIFF(DAY, @DateFrom, @DateTo)
) t
JOIN [master].dbo.spt_values sv ON sv.number <= diff
WHERE sv.[type] = 'p'
) t2
输出:
DateCol DayName Day
----------------------- ------------------------------ -----------
2013-01-01 00:00:00.000 Tuesday 3
2013-01-02 00:00:00.000 Wednesday 4
2013-01-03 00:00:00.000 Thursday 5
2013-01-04 00:00:00.000 Friday 6
2013-01-05 00:00:00.000 Saturday 7
2013-01-06 00:00:00.000 Sunday 1
2013-01-07 00:00:00.000 Monday 2
2013-01-08 00:00:00.000 Tuesday 3
....
2013-01-31 00:00:00.000 Thursday 5
2013-02-01 00:00:00.000 Friday 6
2013-02-02 00:00:00.000 Saturday 7
更新(PIVOT + DYNAMIC SQL):
SET NOCOUNT ON;
DECLARE
@DateFrom DATETIME
, @DateTo DATETIME
SELECT
@DateFrom = '20130501'
, @DateTo = '20130515'
DECLARE @SQL_Pivot NVARCHAR(MAX)
SELECT @SQL_Pivot = STUFF((
SELECT ', [' +
CONVERT(VARCHAR(8), DateCol, 1) +
' ' +
LEFT(DATENAME(WEEKDAY, DateCol), 3) + ']'
FROM (
SELECT DateCol = DATEADD(DAY, sv.number, t.DateFrom)
FROM (
SELECT
DateFrom = @DateFrom
, diff = DATEDIFF(DAY, @DateFrom, @DateTo)
) t
JOIN [master].dbo.spt_values sv ON sv.number <= diff
WHERE sv.[type] = 'p'
) t
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '')
IF OBJECT_ID(N'tempdb.dbo.#emp') IS NOT NULL
DROP TABLE tempdb.dbo.#emp
CREATE TABLE #emp
(
EmpID INT PRIMARY KEY
, Name NVARCHAR(50)
)
INSERT INTO #emp (EmpID, Name)
VALUES (1, N'Nejthe'), (2, N'DevArt')
IF OBJECT_ID(N'tempdb.dbo.#timecard') IS NOT NULL
DROP TABLE tempdb.dbo.#timecard
CREATE TABLE #timecard
(
EmpID INT
, ScheduleDate DATETIME
)
INSERT INTO #timecard (EmpID, ScheduleDate)
VALUES
(1, '2013-05-02 13:20:08')
, (2, '2013-05-01 14:24:34')
, (1, '2013-05-01 13:34:07')
, (1, '2013-05-05 12:20:48')
, (2, '2013-05-08 17:20:48')
DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL = '
SELECT *
FROM (
SELECT
t.EmpID
, e.Name
, OnlyTime = CONVERT(VARCHAR(10), t.ScheduleDate, 108)
, OnlyDate = CONVERT(VARCHAR(8), t.ScheduleDate, 1) + '' '' + LEFT(DATENAME(WEEKDAY, ScheduleDate), 3)
FROM #timecard t
JOIN #emp e ON t.EmpID = e.EmpID
) o
PIVOT
(
MIN(o.OnlyTime)
FOR o.OnlyDate IN (' + @SQL_Pivot + ')
) pt
ORDER BY EmpID'
EXEC sys.sp_executesql @SQL
输出:
EmpID Name 05/01/13 Wed 05/02/13 Thu 05/03/13 Fri 05/04/13 Sat 05/05/13 Sun 05/06/13 Mon 05/07/13 Tue 05/08/13 Wed 05/09/13 Thu 05/10/13 Fri 05/11/13 Sat 05/12/13 Sun 05/13/13 Mon 05/14/13 Tue 05/15/13 Wed
------ --------- ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------
1 Nejthe 13:34:07 13:20:08 NULL NULL 12:20:48 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2 DevArt 14:24:34 NULL NULL NULL NULL NULL NULL 17:20:48 NULL NULL NULL NULL NULL NULL NULL
关于sql - 为每条记录选择多条记录,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/16686464/
我刚开始使用 Gnu Plot 并创建了一些简单的绘图。但是现在我遇到了一个新问题。 输入是这样的 csv 文件: name;n0;n1;n2 Benj;1;3;2 Silv;6;1;2 Steffi
我在 MongoDB 中有 2700 条记录。每个文档的大小约为 320KB。我使用的引擎是wiredTiger,集合的总大小约为885MB。 我的 MongoDB 配置如下: systemLog:
我是一名优秀的程序员,十分优秀!