主页 / user-85087

Kaishu's questions

Martin Hope
Kaishu
Asked: 2017-03-10 05:53:13 +0800 CST
  • 1

任务的table列如下：

Task(TaskID, TaskName, CreatedDate)

并希望根据日期时间差执行代码块：

IF 任务 CreatedDate 已过 15 分钟 THEN

执行块#01

ELSE IF 任务 CreatedDate 还没有超过 15 分钟 THEN

执行块#02

我在下面的查询中进行了测试，但结果为61633440

SELECT DATEDIFF(MINUTE, CONVERT(VARCHAR(10), CreatedDate, 108), CURRENT_TIMESTAMP) AS MinuteDiff

我们如何在sql中实现上述目标？

sql-server sql-server-2012
Martin Hope
Kaishu
Asked: 2017-02-08 03:13:56 +0800 CST
  • 16

表#01 Status：

StatusID    Status
-----------------------
 1          Opened
 2          Closed
 3          ReOpened
 4          Pending

表#02 Claims：

ClaimID     CompanyName StatusID
--------------------------------------
1               ABC     1
2               ABC     1
3               ABC     2
4               ABC     4
5               XYZ     1
6               XYZ     1

预期结果：

CompanyName TotalOpenClaims TotalClosedClaims TotalReOpenedClaims TotalPendingClaims
--------------------------------------------------------------------------------
ABC                 2           1                      0               1
XYZ                 2           0                      0               0

我需要如何编写查询才能获得预期的结果？

sql-server pivot
Martin Hope
Kaishu
Asked: 2016-09-08 02:02:03 +0800 CST
  • 0

我有一个sql表，我试图根据以下标准从中获得结果：

在此处输入图像描述

从上表中，我想创建xml必须返回结果为：

在此处输入图像描述

这是我为

DECLARE
    @ResourceName VARCHAR(50)
, @XMLResult XML = NULL
, @xMSG XML = NULL

SET @ResourceName = 'Property'   
SET @xmsg = '<Results ReplyCode="0" ReplyText="Operation successful"><Result></Result></Results>'          

SET @xMSG = 
(
    SELECT 0 AS [@ReplyCode]
    , 'Operation Successfull' AS [@ReplyText]
    , @ResourceName AS ResourceName
    , ( SELECT *
        FROM ( 
                SELECT SystemName
                        , DBName
                        , ShortName
                        , ClassSortOrder
                        , Pcode
                        , TabNumber
                        , ColumnNumber
                        , RowNumber
                FROM ScreenOrder
            ) SearchFieldsList
        ORDER BY ClassSortOrder
        FOR XML PATH('Field'), TYPE
     )
    FOR
    XML PATH('Results')
)

SET @XMLResult = @xMSG
SELECT   @XMLResult

但是这个查询没有返回预期的结果。此查询返回结果为：

在此处输入图像描述

我试图将所有这些字段作为类明智的放在 xml 中。我需要在哪里修改查询？

sql-server sql-server-2012
Martin Hope
Kaishu
Asked: 2016-03-11 05:12:20 +0800 CST
  • 1

我将值插入tablefromxml作为：

DECLARE @XMLRESULT XML

DECLARE @tbl_XMLResult TABLE
(
    RowID INT IDENTITY(1,1),
    Status VARCHAR(50),
    Address VARCHAR(250),
    ListPrice INT,
    SoldPrice INT,
    YearBuilt INT   
)

EXEC usp_GetReportResult @query = @query, @ReportName = @ReportName, @XMLResult = @XMLRESULT OUTPUT

INSERT INTO @tbl_XMLResult
(
    Status,
    Address,
    ListPrice,
    SoldPrice,
    YearBuilt
)
SELECT
    L.value('(ListingStatus/text())[1]','VARCHAR(50)') AS Status,
    L.value('(PMCReportData/Listing_StreetAddress/text())[1]','VARCHAR(250)') AS Address,
    L.value('(ListPrice/text())[1]','INT') AS ListPrice,
    L.value('(ClosePrice/text())[1]','INT')AS SoldPrice,
    L.value('(YearBuilt/text())[1]','INT')AS YearBuilt
FROM @XMLRESULT.nodes('/Results/Report/Listings/Listing')AS Result(L)

但是如上列所示，YearBuilt数据类型是int我们存储的只是year建立在哪个属性上的数据类型。并据此进行后续calculations操作。但有些时候 for 的值YearBuilt来自xml于format

  • “新的”
  • “0-5岁”
  • “70+”

但是当出现上述类型的结果时YearBuilt，xml我们得到了conversion error。需要从上面的结果中获取值，就好像 YearBuilt 一样：

  • “新”- 在这里，我们要将当前年份和存储结果视为 2016 当年。
  • “0-5 岁”- 0-5 岁。这里考虑上限，即 5。因此将结果存储为 2011 as(2016 - 5 = 2011)
  • “70+” - 将 70 视为 => 2016 - 70 年 = 1946

我们如何才能将适当的结果存储到@tbl_xmlResult

sql-server-2005 xml
Martin Hope
Kaishu
Asked: 2016-03-03 03:47:11 +0800 CST
  • 1

接下来function我写了哪个接受xml并table作为结果返回。

CREATE FUNCTION FunctionTest(@ID INT,@XML_Details xml)
RETURNS @RESULT TABLE
(
    Value1 INT,
    Value2 INT
)
AS
BEGIN
    DECLARE @tbl_Xml_Result Table
    (
        Value1 INT,
        Value2 INT
    )

    INSERT INTO @RESULT(Value1,Value2)
    SELECT 
        l.v.value('Value2[1]','INT'),
        l.v.value('Value1[1]','INT')
    FROM @XML_Details.nodes('/Listings/listing')l(v)
    RETURN
END

以下是我用来在上面运行的代码，function但它总是返回Empty结果。

DECLARE @tbl_Xml_Result Table
(
        Value1 INT,
        Value2 INT
)
INSERT INTO @tbl_xml_Result
values(1,2),(2,3),(3,4),(4,5),(5,6)


DECLARE @xml_Temp xml

SET @xml_Temp = (   SELECT * 
                    FROM @tbl_xml_Result
                    FOR XML PATH('Listing'),ROOT('Listings')
                )

DELETE FROM @tbl_xml_Result
INSERT INTO @tbl_xml_Result(Value1,Value2)
Select 
    T.Value1,
    T.Value2
FROM FunctionTest(1,@xml_Temp) T

select * from @tbl_Xml_Result
sql-server sql-server-2012
Martin Hope
Kaishu
Asked: 2016-01-30 07:03:31 +0800 CST
  • -4

我创建了一个定义如下的类型：

CREATE TYPE [dbo].[TypeListingDatePrice] AS TABLE(
    [RowID] [int] NULL,
    [ListPrice] [int] NULL,
    [SoldPrice] [int] NULL,
    [PendStatusDOM] [int] NULL,
    [ListingDate] [int] NULL,
    [StatusDate] [int] NULL,
    [PendDate] [int] NULL,
    [SaleDate] [int] NULL
)

这在 SQL Server 2012 上运行良好，但我无法在 SQL Server 2005 上执行相同的操作。它在执行时出错：

消息 156，级别 15，状态 1，第 1 行
关键字“AS”附近的语法不正确。

sql-server sql-server-2005
Martin Hope
Kaishu
Asked: 2016-01-29 23:28:58 +0800 CST
  • 1

我有以下结构，我SET在一个子句中进行操作，CASE但它产生了一个错误：

附近的语法不正确SET

询问：

DECLARE @tbl_Result TABLE
(
    RowID INT IDENTITY(1,1),
    TtlNOfCmpSale7to12 INT,
    TtlNOfCmpSale4to6 INT,
    TtlNOfCmpSale0to3 INT,
    TtlNOfCmpSaleSpanFirst SMALLINT DEFAULT 0,
    TtlNOfCmpSaleSpanSecond SMALLINT DEFAULT 0,
    TtlNOfCmpSaleSpanThird SMALLINT DEFAULT 0
);

DECLARE 
    @Declining FLOAT,
    @StableStart SMALLINT,
    @StableEnd SMALLINT,
    @Increasing SMALLINT;

SELECT
    @Declining = 0.90,
    @StableStart = 90,
    @StableEnd = 110,
    @Increasing = 110;

DECLARE @TrendValue FLOAT; 

UPDATE R
SET R.TtlNOfCmpSaleSpanFirst =
    CASE
        WHEN R.TtlNOfCmpSale7to12 != 0 THEN
            SET @TrendValue = R.TtlNOfCmpSale4to6*2/R.TtlNOfCmpSale7to12
            (
                CASE 
                    WHEN @TrendValue > @Increasing THEN 0
                    WHEN @TrendValue >  @StableStart 
                        AND @TrendValue <  @StableEND THEN 1
                    WHEN @TrendValue < @Declining THEN 2
                END
            )
    END,
    R.TtlNOfCmpSaleSpanSecond = 
        CASE 
            WHEN R.TtlNOfCmpSale4to6!= 0 THEN
                SET @TrendValue = R.TtlNOfCmpSale0to3/R.TtlNOfCmpSale4to6
                (
                    CASE 
                        WHEN @TrendValue > @Increasing THEN 0
                        WHEN @TrendValue >  @StableStart 
                            AND @TrendValue <  @StableEND THEN 1
                        WHEN @TrendValue < @Declining THEN 2
                    END
                )
        END,
    R.TtlNOfCmpSaleSpanThird = 
        CASE 
            WHEN R.TtlNOfCmpSale7to12 != 0 THEN
                SET @TrendValue = R.TtlNOfCmpSale0to3/R.TtlNOfCmpSale7to12
                (
                    CASE 
                        WHEN @TrendValue > @Increasing THEN 0
                        WHEN @TrendValue >  @StableStart 
                            AND @TrendValue <  @StableEND THEN 1
                        WHEN @TrendValue < @Declining THEN 2
                    END
                )
        END
FROM Result R;

如果有另一种方法将表达式的结果存储到@TrendValue 中，这样就无需每次都计算该表达式。

避免除以零错误也很好（这就是我添加各种R.TtlNOfCmpSale7to12 != 0检查的原因）。

sql-server sql-server-2012
Martin Hope
Kaishu
Asked: 2016-01-28 05:44:07 +0800 CST
  • 0

我已经根据传递给它的值定义function了table value parameterwhich 。定义为：returns medianFunction

    CREATE FUNCTION [dbo].[fn_GetMedian](@List TypeMedian READONLY)
    RETURNS INT
    AS
    BEGIN
    <function body>
    END

Table Type TypeMedian定义为：

CREATE TYPE [dbo].[TypeMedian] AS TABLE(
    [VALUE] [int] NULL
)

现在我有一个table Listing带有填充值的 as 和一个table RESULT要根据以下内容填充的值table Listing：

Tables structure作为

LISTING(ListingCol1,ListingCol2,ListingCol3,ListingCol4,ListingCol5)
RESULT(Col1,Col2,Col3,Col4,Col5)

列表表有超过 1000 行数据。

column两者都tables属于 type int。现在我想填写columnsofRESULT table并且column可以计算为：

Col1 = SUM(ListingCol1)
Col2 = SUM(ListingCol2)
Col3 = dbo.fn_GetMedian(ListingCol3)
Col4 = dbo.fn_GetMedian(ListingCol4)
Col5 = dbo.fn_GetMedain(ListingCol5)

我这样做是为了：

INSERT INTO RESULT(Col1)
SELECT SUM(ListingCol1)

Update RESULT
SET Col2 = SUM(ListingCol2)

DECLARE @tbl_Median TypeMedian

INSERT INTO @tbl_Median
SELECT ListingCol3
FROM Listing

UPDATE RESULT
SET Col3 = dbo.fn_GetMedian(@tbl_Median)

-- For next column
DELETE FROM @tbl_Median

INSERT INTO @tbl_Median
SELECT ListingCol4
FROM Listing

UPDATE RESULT
SET Col4 = dbo.fn_GetMedian(@tbl_Median);

-- 我为剩余的列重复此更新查询。我怎么能在单个查询中做到这一点？

sql-server sql-server-2012
Martin Hope
Kaishu
Asked: 2016-01-26 23:01:55 +0800 CST
  • -2

这是我声明的表值函数：

CREATE FUNCTION fn_GetMedian(@List TypeMedian READONLY)
RETURNS INT
AS
BEGIN
    RETURN 
    (
        Select 
        (
            (
                Select Top 1 Value
                From   
                (
                    Select  Top 50 Percent Value
                    From    @List
                    Where   Value Is NOT NULL
                    Order By Value
                ) As A
                Order By Value DESC
            ) + 
            (
                Select Top 1 Value
                From   
                (
                    Select  Top 50 Percent Value
                    From    @List
                    Where   Value Is NOT NULL
                    Order By Value DESC
                ) As A
                Order By Value Asc
            )
        ) / 2
    )
END

这是我正在执行的代码临时表和查询：

DECLARE @Temp Table
(ID INT,ID2 INT)

select fn_GetMedian(ID)
from @Temp

但是，我收到以下错误：

消息 195、级别 15、状态 10、第 9 行
“fn_GetMedian”不是可识别的函数名称。

这个问题与：

如何将列传递给sql中的函数？

sql-server sql-server-2012
Martin Hope
Kaishu
Asked: 2016-01-26 21:59:57 +0800 CST
  • 5

我计算中位数为：

DECLARE @TEMP TABLE
(
    ID INT
)

Select 
(
        (
            Select Top 1 ID
            From   
            (
                Select  Top 50 Percent ID
                From    @Temp
                Where   ID Is NOT NULL
                Order By ID
            ) As A
            Order By ID DESC
        ) + 
        (
            Select Top 1 ID
            From   
            (
                Select  Top 50 Percent ID
                From    @Temp
                Where   ID Is NOT NULL
                Order By ID DESC
            ) As A
            Order By ID Asc
        )
) / 2

以上查询我想使用。columns但是，就我而言，我想计算的人太多了MEDIAN。但我认为重复上面的代码块会很糟糕column。所以，我试图定义单独的函数来接受column值、处理和返回中值。我应该为此定义table-value-funtion还是有另一种optimized方法？

这个问题与以下问题有关：

sql-server sql-server-2012
Martin Hope
Kaishu
Asked: 2016-01-25 22:00:56 +0800 CST
  • 5

我正在改变function：

ALTER FUNCTION [dbo].[fn_CalculateListing](@Listing TypeListingDatePrice READONLY)
RETURNS TypePreviousListingResult 
AS 
BEGIN
    DECLARE @tbl_ListingDateDetails TypePreviousListingResult   
    RETURN @tbl_ListingDateDetails
END
GO

但抛出错误为：

Msg 137, Level 16, State 1, Procedure fn_CalculateListing, Line 6
Must declare the scalar variable "@tbl_ListingDateDetails".

即使我在使用该变量之前已经声明过，为什么还会出现错误并要求声明？

TypePreviousListingResult 是创建为 的表类型CREATE TYPE TypePreviousListingResult AS TABLE...。

上一个问题：如何在sql中编写接受表作为输入并将结果作为表返回的函数？

sql-server sql-server-2012
Martin Hope
Kaishu
Asked: 2016-01-23 23:08:38 +0800 CST
  • 0

我想将一个表变量作为输入传递给一个函数（在不同时间包含不同的值），以另一个表的形式返回计算结果，该表将在函数中声明。

是这样的：

CREATE FUNCTION fn_CalculateListing(@ListingData TABLE(ROWID INT,value1 INT, value2 INT))
RETURNS TABLE
AS
BEGIN
    DECLARE @result TABLE
    (
        result1 INT,
        result2 INT
    )

    INSERT INTO @result(result1,result2)
    SELECT value1*value2, value1/value2
    FROM ListingDate

    RETURN @result
END

但是上面的语法在声明附近给我一个错误@ListingDate TABLE，因为语法不正确。

以上语法是否正确，还是我应该使用另一种语法？

sql-server sql-server-2012
Martin Hope
Kaishu
Asked: 2016-01-22 23:01:58 +0800 CST
  • -1
CREATE TABLE TEST2
(
    RowID INT IDENTITY(1,1),
    Ratio INT
)
CREATE TABLE TEST
(
    RowID INT IDENTITY(1,1),
    Col1 INT,
    Col2 INT,
    Col3 INT
)

现在，我想在table TESTas中插入值

    INSERT INTO TEST
        (Col1, Col2, Col3)
    values
         (10, 
          col1, 
          col2 * (select Ratio 
                  from TEST2 T2 INNER JOIN TEST T 
                       ON T2.RowID = T.RowID)
         );

以上query是我想要的。我们如何insert使用优化方式完成上述操作？

sql-server sql-server-2012
Martin Hope
Kaishu
Asked: 2016-01-22 02:17:09 +0800 CST
  • 1

我已经从 date 获取日期平均值并尝试获取平均日期的结果，但它会引发错误，例如：Conversion failed when converting date and/or time from character string.

sql查询执行为：

select 
CONVERT
(
    DATE,
    LEFT(
          convert(
                  varchar(10),
                  AVG(
                        convert(
                                 int, 
                                 convert(
                                           varchar(10), 
                                           OnMarkDate, 
                                           112
                                         )
                                 )
                       )
                  ),8
          )
)
from @tbl_XMLResult

列值如下：

   '2001-10-11',
   '2001-10-03',
   '2001-09-24',
   '2000-05-31',
   '2001-10-15',
   '2008-01-01'

数据类型日期。

sql-server sql-server-2012
Martin Hope
Kaishu
Asked: 2016-01-21 03:34:22 +0800 CST
  • 3
DECLARE @tbl_XMLResult table
(
    Status varchar(50),
    Address varchar(100),
    ListPrice varchar(25),
    SoldPrice varchar(25),
    YearBuilt varchar(25),
    SF varchar(25),
    Acres varchar(25),
    OnMarkDate varchar(25),
    PendDate varchar(25),
    SaleDate varchar(25),
    DOM varchar(25)
)

DECLARE @XMLRESULT xml

exec usp_ReportResults @query = "759,905,1048,170,725,80129", @ReportName = "GenReport", @XMLResult = @XMLRESULT OUTPUT

INSERT INTO @tbl_XMLResult(Status, Address, ListPrice, SoldPrice,
        YearBuilt, SF, Acres, OnMarkDate, PendDate, SaleDate, DOM)
    SELECT
        Listing.value('(/ListingStatus/text())[1]', 'varchar(50)') AS Status, 
        ReportData.value('(/Listing_StreetAddress/text())[1]', 'varchar(50)') AS Address,
        Listing.value('(/ListPrice/text())[1]', 'varchar(50)') AS ListPrice,
        Listing.value('(/ClosePrice/text())[1]', 'varchar(50)')AS SoldPrice,
        Listing.value('(/YearBuilt/text())[1]', 'varchar(50)') AS YearBuilt,
        Listing.value('(/TotalLvSpace/text())[1]', 'varchar(50)') AS SF,
        Listing.value('(/AcresApx/text())[1]', 'varchar(50)') AS Acres,
        Listing.value('(/ListDate/text())[1]', 'varchar(50)') OnMarkDate,
        Listing.value('(/PendingDate/text())[1]', 'varchar(50)') AS PendDate,
        Listing.value('(/CloseDate/text())[1]', 'varchar(50)') AS SaleDate,
        Listing.value('(/DaysOnMarket/text())[1]', 'varchar(50)') DOM
    FROM 
        @XMLRESULT.nodes('/Results/Report/Listings/Listing') AS ListingTable(Listing),
        @XMLRESULT.nodes('/Results/Report/Listings/Listing/PMCReportData') AS TempTable(ReportData)

SELECT *
FROM @tbl_XMLResult

我正在尝试以 xml 格式从存储过程中获取结果，并通过读取该结果将其存储到上面创建的临时表中。但是我得到的结果是null当前存储过程在 xml 中仅返回六条记录，但它在临时表中显示了 36 行，其中包含null值。如何解决？

这是xml格式：

xml格式

sql-server sql-server-2012
Martin Hope
Kaishu
Asked: 2016-01-21 01:54:41 +0800 CST
  • 0

我正在调用这样的存储过程：

usp_ReportResults @query = '759,905,1048,170,725,80129', 
                  @ReportName = 'GenRepot'

其中usp_ReportResult定义为

CREATE PROCEDURE [dbo].[usp_ReportResults]
     @query VARCHAR(MAX), 
     @ReportName VARCHAR(100), 
     @AutoSelectXML BIT = 1, 
     @XMLResult XML = NULL OUTPUT

我正在尝试获取xml变量结果以进行进一步处理：

DECLARE @XMLRESULT xml

SET @XMLRESULT = exec usp_ReportResults @query = '759,905,1048,170,725,80129', @ReportName = 'GenRepot'

但我无法获得结果，@XMLRESULT也无法从xml表中读取和存储数据结果。

sql-server sql-server-2012