如何操作透视数据并插入缺少的日期。 [英] How do I manipulate the pivoted data and insert the missing dates.
本文介绍了如何操作透视数据并插入缺少的日期。的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我们知道如果该日期有交易,记录将存储到数据库中。但是如果某个日期我们没有交易,那么交易日期将不会记录在数据库中。现在,我将做一个报告,我需要显示日期,包括没有交易。包括周六和周日。我在我的sql查询中使用了pivot方法。
我尝试了什么:
As we know that the record will stored to the database if there is a transaction on that date. But if there is certain date that we had no transactions, No date of transactions will be keep on record to the database. Now, I will make a report, I need to display the dates including no transactions. Including Saturday and Sunday. I used pivot method to my sql query.
What I have tried:
SELECT *
FROM (
SELECT
A.employee_id,B.firstname, B.branch_code , A.trans_date as [Date],A.bioinoutmode as [Type],
A.trans_time as Time
FROM biometricdata A
LEFT JOIN Employees B ON A.employee_id = B.employee_id
WHERE A.employee_id = B.employee_id
) as s
PIVOT
(
MAX(Time)
FOR [Type] IN ([0],[1],[2],[3],[4],[5])
)AS pvt Order by DATE
推荐答案
我通常通过生成一个列表来执行此操作我需要的日期(参见在SQL中生成序列 [ ^ ])然后使用该表在LEFT JOIN到我的实际数据表。
例如
I usually do this by generating a list of the dates that I need (see Generating a Sequence in SQL[^] ) and then using that table in a LEFT JOIN to my actual data table.
E.g.
SELECT ....-- column list
FROM MyListOfDates D
LEFT OUTER JOIN biometricdata A ON D.DesiredDate = A.[DATE] -- or whatever your column is
LEFT OUTER JOIN Employees B ON A.employee_id = B.employee_id
只需记得处理从A& A返回的结果为NULL B表示空日期
Just remember to handle the resulting NULLs returned from A & B for the empty dates
这篇关于如何操作透视数据并插入缺少的日期。的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文