gpt4 book ai didi

sql - 为每条记录选择多条记录

转载 作者:行者123 更新时间:2023-12-01 14:01:15 25 4
gpt4 key购买 nike

我正在编写一条 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

提前致谢。

enter image description here

最佳答案

试试这个 -

查询:

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/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com