动态创建表基于另一个表？ [英] Dynamic Create table based on another Table ?
本文介绍了动态创建表基于另一个表？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
HI，
iam使用拇指设备。他们为数据库提供了一些表格... iam使用一个表格来表示。 event_log表..
这是表
my table [access_event_logs]
，[USERID] nvarchar （ 50 ）
，[TIMESTAMPS] datetime
，[EVENTID] nvarchar （ 50 ）
，[LOCALTIMESTAMP] datetime
这样的表
[USERID] [TIMESTAMPS] [EVENTID]
1 019 2014-03-06 07:50:48 。 000 IN
2 019 2014-03-06 17:02:39 。 000 OUT
3 019 2014-03-09 07:43:37 。 000 IN
4 019 2014-03-09 14:34:59 。 000 OUT
5 019 2014-03-10 07:43:34 。 000 IN
6 019 2014-03-10 14:30:24 。 000 OUT
所有现成员工[in]和[检查此查询...
选择 distinct
E.USERID，
转换（ date ，LOCALTIMESTAMP） as ATTDate，
（ select min（转换（ TIME ，TIMESTAMPS））来自 access_event_logs < span class =code-keyword>作为 MINCE 其中 CAST （MINCE.TIMESTAMPS as DATE ）= CAST（E.TIMESTAMPS 正如 DATE ） AND MINCE.EVENTID = ' 在' 和 MINCE.USERID = E.USERID） as InTime，
（选择 max（转换（ TIME ，TIMESTAMPS））来自 access_event_logs< a href = >< / a>作为MAXCE 其中 CAST（MAXCE.TIMESTAMPS < span class =code-keyword> as DATE ）= CAST（E.TIMESTAMPS As DATE ） AND MAXC E.EVENTID = ' Out' 和 MAXCE.USERID = E.USERID） as OutTime
，（ select DATEDIFF（MINUTE，< span class =code-string>' 8:30:00'，（ select min（转换（ TIME ，TIMESTAMPS））来自 access_event_logs 作为 MINCE2 其中 CAST（MINCE2.TIMESTAMPS as DATE ）= CAST（E.TIMESTAMPS As DATE ） AND MINCE2.EVENTID = ' 在' 和 MINCE2.USERID = E.USERID））） as LateTime
，（ select DATEDIFF（MINUTE，（选择 max（转换（ TIME ，TIMESTAMPS））来自 access_event_logs 作为 MAXCE 其中 CAST（MAXCE.TIMESTAMPS as DATE ）= CAST（E.TIMESTAMPS As DATE ） AND MAXCE.EVENTID = ' Out' 和 MAXCE.USERID = E.USERID），' 14:30'）） as EarlyTime
，（选择 DATEDIFF（MINUTE，' 8:30:00'，（选择 min（转换（ TIME ，TIMESTAMPS））来自 access_event_logs As MINCE2 其中 CAST（MINCE2.TIMESTAMPS as DATE ）= CAST（E.TIMESTAMPS As DATE ） AND MINCE2.EVENTID = ' 在' 和 MINCE2.USERID = E.USERID）））+
（选择 DATEDIFF（MINUTE，（选择 max（转换（ TIME ，TIMESTAMPS））来自 access_event_logs 作为 MA XCE 其中 CAST（MAXCE.TIMESTAMPS as DATE ）= CAST（E.TIMESTAMPS As DATE ） AND MAXCE.EVENTID = ' Out' 和 MAXCE.USERID = E.USERID），' 14:30'）） as TotalLate
来自 access_event_logs 作为 E
WHERE E.USERID = ' 012' AND 转换（ date ，LOCALTIMESTAMP） ' 03/2/2014' 和 ' 03/16/2014'
Userid 日期 InTime < span class =code-leadattribute> OutTime LateTime EarilyOutTime TotalLateTime
012 2014-03-02 08:47:07 14:49:57 17 - 19 - 2
012 2014-03-03 08:27:01 14:31:58 - 3 - 1 - 4
012 2014-03-04 08:29： 43 14:32:12 - 1 -2 - 3
012 2014-03-05 08:34:29 14:23:29 4 7 11
012 2014-03-06 08:16： 55 14:31:12 - 14 -1 - 15
012 2014-03-09 08:36:04 14:36:59 6 - 6 0
012 2014-03-10 08:20:11 NULL - 1 NULL NULL
012 2014-03-11 NULL 14:31:35 NULL - 1 NULL
012 2014-03-12 08:55:45 14:30:29 25 0 25
012 2014-03-13 08:35:52 14:38:58 5 - 8 - 3
012 2014-03-16 08:46:06 14:27:56 16 3 19
像这样出来......
所以我想要这样的动态表...基于event_log表，
所有准备好的每个事件都会给出更新数据..这个表基于那个表...
如何创建动态表...
请帮帮我....请我需要这张桌子....
解决方案
< blockquote>试试这个：
DECLARE @ tmp TABLE （[USERID] VARCHAR （ 5 ），[TIMESTAMPS] DATETIME ，[ EVENTID] VARCHAR （ 5 ））
INSERT INTO @ tmp （[USERID]，[TIMESTAMPS]，[EVENTID ]）
VALUES （' 019'，' 2014-03-06 07：50：48.000'，' IN'），
（' 019'，' 2014-03-06 17：02：39.000 '，' OUT'），
（' 019'，' 2014-03-09 07：43：37.000'， ' IN'），
（' 019'，' 2014-03-09 14：34：59.000'，' OUT'），
（< span class =code-string>' 019'，' 2014-03-10 07：43：34.000'，' IN'），
（' 019'，' 2014-03-10 14：30：24.000'，' OUT'）
SELECT t1.RowNo，t1。[USERID]，t1。[ IN ]，t2。[OUT]
FROM （
SELECT ROW_NUMBER（） OVER （ PARTITION BY USERID ORDER BY TIMESTAMPS） AS RowNo，[USERID]，[TIMESTAMPS] AS [ IN ]
FROM @ tmp
WHERE [EVENTID] = ' IN'
） AS t1 INNER JOIN （
SELECT ROW_NUMBER（） OVER （ PARTITION BY USERID ORDER BY TIMESTAMPS） AS RowNo，[USERID]，[TIMESTAMPS] AS [OUT]
FROM @ tmp
WHERE [EVENTID] = ' OUT'
） AS t2 ON t1.USERID = t2.USERID AND t1.RowNo = t2.RowNo
结果：
RNo UID IN OUT
1 019 2014-03-06 07:50:48 。 000 2014-03-06 17:02:39 。 000
2 019 2014 -03-09 07:43:37 。 000 2014-03-09 14:34:59 。 000
3 019 2014-03-10 07:43:34 。 000 2014-03-10 14:30:24 。 000
如何添加下一张桌子？
使用Join's。
SELECT t1.Field1，t2.Field1
FROM Table1 作为 t1 INNER JOIN 表2 AS t2 ON t1.PK = t2.FK
看看这里： SQL连接的可视化表示 [ ^ ]为了更好地理解Join'工作。
[/ EDIT]
HI,
iam using thumb device.they give data base some tables... iam using one table by attendace. event_log table..
this is the table
my table [access_event_logs ]
,[USERID]nvarchar(50)
,[TIMESTAMPS]datetime
,[EVENTID]nvarchar(50)
,[LOCALTIMESTAMP]datetime
table like this
[USERID] [TIMESTAMPS] [EVENTID]
1 019 2014-03-06 07:50:48.000 IN
2 019 2014-03-06 17:02:39.000 OUT
3 019 2014-03-09 07:43:37.000 IN
4 019 2014-03-09 14:34:59.000 OUT
5 019 2014-03-10 07:43:34.000 IN
6 019 2014-03-10 14:30:24.000 OUT
all ready employee [in] and [out] check for this query...
select distinct
E.USERID,
Convert(date,LOCALTIMESTAMP) as ATTDate,
(select min(Convert(TIME,TIMESTAMPS)) from access_event_logs As MINCE where CAST(MINCE.TIMESTAMPS as DATE)=CAST(E.TIMESTAMPS As DATE) AND MINCE.EVENTID ='In' and MINCE.USERID=E.USERID) as InTime,
(select max(Convert(TIME,TIMESTAMPS)) from access_event_logs <a href=""></a>As MAXCE where CAST(MAXCE.TIMESTAMPS as DATE)=CAST(E.TIMESTAMPS As DATE) AND MAXCE.EVENTID ='Out' and MAXCE.USERID=E.USERID) as OutTime
,(select DATEDIFF(MINUTE, '8:30:00', (select min(Convert(TIME,TIMESTAMPS)) from access_event_logs As MINCE2 where CAST(MINCE2.TIMESTAMPS as DATE)=CAST(E.TIMESTAMPS As DATE) AND MINCE2.EVENTID ='In' and MINCE2.USERID=E.USERID))) as LateTime
,(select DATEDIFF(MINUTE, (select max(Convert(TIME,TIMESTAMPS)) from access_event_logs As MAXCE where CAST(MAXCE.TIMESTAMPS as DATE)=CAST(E.TIMESTAMPS As DATE) AND MAXCE.EVENTID ='Out' and MAXCE.USERID=E.USERID), '14:30')) as EarlyTime
,(select DATEDIFF(MINUTE, '8:30:00', (select min(Convert(TIME,TIMESTAMPS)) from access_event_logs As MINCE2 where CAST(MINCE2.TIMESTAMPS as DATE)=CAST(E.TIMESTAMPS As DATE) AND MINCE2.EVENTID ='In' and MINCE2.USERID=E.USERID))) +
(select DATEDIFF(MINUTE, (select max(Convert(TIME,TIMESTAMPS)) from access_event_logs As MAXCE where CAST(MAXCE.TIMESTAMPS as DATE)=CAST(E.TIMESTAMPS As DATE) AND MAXCE.EVENTID ='Out' and MAXCE.USERID=E.USERID), '14:30')) as TotalLate
from access_event_logs As E
WHERE E.USERID='012' AND Convert(date,LOCALTIMESTAMP) between '03/2/2014' and '03/16/2014'
Userid Date InTime OutTime LateTime EarilyOutTime TotalLateTime
012 2014-03-02 08:47:07 14:49:57 17 -19 -2
012 2014-03-03 08:27:01 14:31:58 -3 -1 -4
012 2014-03-04 08:29:43 14:32:12 -1 -2 -3
012 2014-03-05 08:34:29 14:23:29 4 7 11
012 2014-03-06 08:16:55 14:31:12 -14 -1 -15
012 2014-03-09 08:36:04 14:36:59 6 -6 0
012 2014-03-10 08:20:11 NULL -1 NULL NULL
012 2014-03-11 NULL 14:31:35 NULL -1 NULL
012 2014-03-12 08:55:45 14:30:29 25 0 25
012 2014-03-13 08:35:52 14:38:58 5 -8 -3
012 2014-03-16 08:46:06 14:27:56 16 3 19
out put like this...
so i want dynamic table like this ... based on event_log table,
all ready every event they give update data.. this table based on that table ...
how create dynamic table...
please help me.... please i need this table....
解决方案
Try this:
DECLARE @tmp TABLE([USERID] VARCHAR(5), [TIMESTAMPS] DATETIME, [EVENTID] VARCHAR(5)) INSERT INTO @tmp ([USERID], [TIMESTAMPS], [EVENTID]) VALUES('019', '2014-03-06 07:50:48.000', 'IN'), ('019', '2014-03-06 17:02:39.000', 'OUT'), ('019', '2014-03-09 07:43:37.000', 'IN'), ('019', '2014-03-09 14:34:59.000', 'OUT'), ('019', '2014-03-10 07:43:34.000', 'IN'), ('019', '2014-03-10 14:30:24.000', 'OUT') SELECT t1.RowNo, t1.[USERID], t1.[IN], t2.[OUT] FROM ( SELECT ROW_NUMBER() OVER(PARTITION BY USERID ORDER BY TIMESTAMPS) AS RowNo, [USERID], [TIMESTAMPS] AS [IN] FROM @tmp WHERE [EVENTID] = 'IN' ) AS t1 INNER JOIN ( SELECT ROW_NUMBER() OVER(PARTITION BY USERID ORDER BY TIMESTAMPS) AS RowNo, [USERID], [TIMESTAMPS] AS [OUT] FROM @tmp WHERE [EVENTID] = 'OUT' ) AS t2 ON t1.USERID = t2.USERID AND t1.RowNo = t2.RowNo
Result:
RNo UID IN OUT 1 019 2014-03-06 07:50:48.000 2014-03-06 17:02:39.000 2 019 2014-03-09 07:43:37.000 2014-03-09 14:34:59.000 3 019 2014-03-10 07:43:34.000 2014-03-10 14:30:24.000
[EDIT]
How to "add" next table?
Using Join's.
SELECT t1.Field1, t2.Field1 FROM Table1 As t1 INNER JOIN Table2 AS t2 ON t1.PK = t2.FK
Have a look here: Visual Representation of SQL Joins[^] for better understanding how Join's work.
[/EDIT]
这篇关于动态创建表基于另一个表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
