SQL查询中的空列输出 [英] Output of Empty Columns in SQL Query

查看:417
本文介绍了SQL查询中的空列输出的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我有一个相当大的SQL查询,该查询将数字日期数据类型转换为日期时间值,并根据我的要求进一步指定搜索.但是,当我运行此查询时,我看到的是列名,但列中没有数据,它只是该列的标题,其顺序为:数据集,日期,时间,消息ID,参数,数据集 顺序应为:数据,时间,数据集,媒体(带有子字符串"的单独列).同样,这只是显示我过去两天的数据,而不是整个数据库的数据,这也是一个问题.

I have a fairly large SQL Query that converts a numeric date data type to a date time value and further specifies the search based on my requirements. However when I run this query I'm seeing the column names but there is no data in the columns, it is just the titles of the columns in this order: Dataset, Date, Time, MsgID, Parms, Dataset When the order should be: Data, Time, Dataset, Media (Separate column with Substring of Parms). Also this is only showing my data from the past 2 days instead of the entire database, which also is a problem.

我需要一些帮助,以显示所有列中的数据的顺序显示列.如果有人可以对我现有的SQL查询提出任何建议或修改以获取所需的输出,将不胜感激.我知道这对于专业的程序员来说是个快速修复,但是我仍然在学习技巧,需要一些帮助.

I need some help in displaying the columns in that order with the data present from all of the columns. If anyone can make any suggestions or modificiations to my existing SQL query to get the required output, it would be greatly appreciated. I know this is a quick fix for an expert programmer, but I'm still learning the ropes and require some assistance.

这是我的SQL查询:

    SELECT [Object] AS [Dataset],
    CAST(DATEADD(HOUR,-4,CONVERT(DATETIME,LEFT([Date],8)+' '+
    SUBSTRING([Date],10,2)+':'+
    SUBSTRING([Date],12,2)+':'+
    SUBSTRING([Date],14,2)+'.'+
    SUBSTRING([Date],15,3))) AS DATE) 'Date',
    LEFT(CAST(DATEADD(HOUR,-4,CONVERT(DATETIME,LEFT([Date],8)+' '+
    SUBSTRING([Date],10,2)+':'+
    SUBSTRING([Date],12,2)+':'+
    SUBSTRING([Date],14,2)+'.'+
    SUBSTRING([Date],15,3))) AS TIME),8) 'Time',
    MsgId,
    Parms,
    CASE WHEN MsgID = '61' THEN SUBSTRING(Parms,35,6)
    ELSE '' --Optional ELSE
    END  AS [Dataset]
    FROM ( SELECT  ItemId,
    CONVERT(VARCHAR(18),[Date]) [Date],
    [Object],
    MsgID,
    Parms
    FROM JnlDataSection
    WHERE CAST(substring(convert(varchar(50), [Date]), 0, 5) + '-' +
    substring(convert(varchar(50), [Date]), 5, 2) + '-' +
    substring(convert(varchar(50), [Date]), 7, 2) AS DATETIME) =
    CONVERT(date, DATEADD(day, -1, getdate()))) A --Converting to date again to          remove     the time part
    WHERE SUBSTRING(Parms,35,6) = 'X05219' AS [Media]
    ORDER BY [DATE] DESC;

请注意:我正在使用SQL Server Management Studio 2008.

Please Note: I'm using SQL Server Management Studio 2008.

推荐答案

现在,从[DATE]提取到日期和时间似乎不正确.您将相同的字符串放入'Date'和'Time'

Now, it looks like your extraction from [DATE] into Date and Time is incorrect. You are putting the same string into 'Date' and 'Time'

这篇关于SQL查询中的空列输出的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆