首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >选择多列,但只按一列分组

问选择多列,但只按一列分组
EN

Stack Overflow用户
提问于 2015-10-28 19:16:25
回答 2查看 125关注 0票数 0

使用Server 2008。我已经研究这个问题好几天了。考虑到CTE,并尝试在问题表上使用谓词。

我试过或研究过的东西都没有用,所以我就在这里。问题是它正在返回重复的OrderID。

我已经把问题标记出来了。

甚至尝试过OUTER APPLY,但它导致某些搜索无法工作。尝试了INNER APPLY,然后再次复制。

问题连接,表,有多个对OrderID的引用。

因此,tblRun有多个行,具有相同的OrderID,显示它在哪个时间运行,在什么日期运行,等等。

我真的需要所有专家的建议。

下面是SQL:

代码语言:javascript
复制
DECLARE @CompanyID INT = 22718,
        @StartDate DATETIME = '',
        @EndDate DATETIME = '',
        @SalesRepID INT = NULL,
        @AssignedTo INT = NULL,
        @ServiceDefID INT = NULL,
        @ProductName VARCHAR(512) = NULL,
        @IsCCOrder BIT = NULL,
        @OrderID INT = NULL,
        @LocationID INT = NULL,
        @SalesRepLocationID INT = NULL,
        @PONum VARCHAR(150) = NULL,
        @InvoiceID INT = NULL,
        @IsSplitOrder BIT = NULL,    
        @ContactID INT = NULL,
        @ContactName VARCHAR(150) = NULL,
        @JobName VARCHAR(200) = NULL,
        @Notes VARCHAR(MAX) = NULL,
        @CompanyName VARCHAR(255) = NULL,
        @DueDateFrom DATETIME = '',
        @DueDateTo DATETIME = '',
        @SubmitedDateFrom DATETIME = '',
        @SubmitedDateTo DATETIME = '',
        @IsRush BIT = NULL,
        @Msg VARCHAR(1000) = NULL

--@Stages               dbo.int_tbltype READONLY

DECLARE @Stages TABLE (ID INT)
--INSERT INTO @Stages (ID) VALUES (1)

DECLARE @DueDate DATETIME = NULL

SET NOCOUNT ON

DECLARE @OrderIDsTBL TABLE(OrderID INT)

IF @Msg IS NOT NULL
BEGIN
    INSERT INTO @OrderIDsTBL (OrderID)
        SELECT OrderID 
        FROM tblOrderLog
        WHERE Msg LIKE '%' + @Msg + '%'
END

IF @OrderID IS NOT NULL
BEGIN
    INSERT INTO @OrderIDsTBL (OrderID)
    VALUES (@OrderID)
END

DECLARE @OderIDsCnt INT = (SELECT COUNT(OrderID) FROM @OrderIDsTBL)
DECLARE @StageCnt INT = (SELECT COUNT(ID) FROM @Stages)

SELECT 
    o.OrderID,
    o.CompanyID,
    co.Name AS CompanyName,
    o.ContactID,
    o.JobName,
    p.FirstName + ' ' + p.LastName AS ContactName,    
    p2.FirstName + ' ' + p2.LastName AS SalesRep,
    o.DueDate,
    CASE WHEN MAX(oi.PriorityService) > 0 THEN 1 ELSE 0 END AS IsRush,
    ISNULL(s.StageID, 0) AS StageID,
    o.Notes, r.SubmittedComplete,
    dbo.fOrderRunLocationCSVByOrderID(o.OrderID) AS LocationCSV,
    (SELECT
        STUFF((SELECT DISTINCT ' ' + st.Name + '<br />' FROM tblStage st
        INNER JOIN tblOrderItem oi ON oi.OrderID = o.OrderID
        INNER JOIN tblRun r ON r.OrderItemID = oi.OrderItemID
        INNER JOIN tblStage s ON s.StageID = r.StageID
        LEFT JOIN tblRunService rs ON rs.RunID = r.RunID
        WHERE (s.StageID = st.StageID)
        AND (rs.AssignedTo = @AssignedTo OR @AssignedTo IS NULL)
        FOR XML PATH(''), TYPE).value('.','VARCHAR(max)'), 1, 1, '')) 
        AS Stages,
    Row_Number() Over(Order By o.OrderID Desc) As RowNum
FROM 
    tblOrder o
INNER JOIN 
    tblCompany co ON co.CompanyID = o.CompanyID
INNER JOIN 
    tblParty p ON p.PartyID = o.ContactID
-------- PROBLEM JOINS ------------
LEFT JOIN 
    tblOrderItem oi ON oi.OrderID = o.OrderID
LEFT JOIN 
    tblRun r ON r.OrderItemID = oi.OrderItemID
LEFT JOIN 
    tblService srv ON srv.OrderItemID = oi.OrderItemID
-------- END PROBLEM JOINS ------------
LEFT JOIN 
    tblStage s ON s.StageID = r.StageID    
LEFT JOIN 
    tblParty p2 ON p2.PartyID = o.SalesRepID
LEFT JOIN 
    tblEmployee e ON e.EmployeeID = o.SalesRepID
LEFT JOIN 
    tblShipTo st ON o.ShipToID = st.ShipToID
WHERE 
    (@CompanyID IS NULL OR (o.CompanyID = @CompanyID )) AND
    (@IsCCOrder IS NULL OR (ISNULL(o.IsCreditCardOrder, 0) = @IsCCOrder )) AND
    (@SalesRepID IS NULL OR o.SalesRepID = @SalesRepID) AND
    (@ServiceDefID IS NULL OR (srv.ServiceDefID = @ServiceDefID)) AND
    (@ProductName IS NULL OR (oi.Name LIKE '%' + @ProductName + '%')) AND 
    (@IsSplitOrder IS NULL OR (@IsSplitOrder = 1 AND oi.SplitOrderID IS NOT NULL)) AND
    (
      (@StartDate = '' OR @EndDate = '') OR 
      (@StartDate >= CreatedDate AND @EndDate <= COALESCE(CancelledDate, ClosedDate, GetDate())) OR
      (@StartDate  <= COALESCE(CancelledDate, ClosedDate, GETDATE()) AND @EndDate >= COALESCE(CancelledDate, ClosedDate, GetDate()) ) OR
      (@StartDate <= CreatedDate AND @EndDate >= CreatedDate ) 
    ) AND 
    (@LocationID IS NULL OR (@LocationID = srv.LocationID OR srv.LocationID IS NULL)) AND
    (@SalesRepLocationID IS NULL OR (@SalesRepLocationID = e.LocationID OR e.LocationID IS NULL))
    AND (@InvoiceID IS NULL OR o.InvoiceID = @InvoiceID ) 
    AND (@PONum IS NULL OR o.PONum LIKE '%' + @PONum + '%')
    AND (COALESCE(s.StageID, 0) IN (SELECT ID FROM @Stages) OR @StageCnt = 0)
    AND (o.ContactID = @ContactID OR @ContactID IS NULL)
    AND (p.FirstName + ' ' + p.LastName LIKE '%' + @ContactName + '%' OR @ContactName IS NULL)
    AND (o.JobName LIKE '%' + @JobName + '%' OR @JobName IS NULL)
    AND (o.Notes LIKE '%' + @Notes + '%' OR @Notes IS NULL)
    AND (co.Name LIKE '%' + @CompanyName + '%' OR @CompanyName IS NULL)
    AND (o.DueDate >= @DueDateFrom OR @DueDateFrom = '')
    AND (o.DueDate <= @DueDateTo OR @DueDateTo = '')
    AND (r.SubmittedComplete >= @SubmitedDateFrom OR @SubmitedDateFrom = '')
    AND (r.SubmittedComplete <= @SubmitedDateTo OR @SubmitedDateTo = '')
    AND (@IsRush = (CASE WHEN oi.PriorityService > 0 THEN 1 ELSE 0 END)
         OR @IsRush IS NULL)
    AND (o.OrderID IN (SELECT OrderID FROM @OrderIDsTBL) OR @OderIDsCnt = 0)
GROUP BY 
    o.OrderID, o.CompanyID,
    co.Name,
    o.ContactID, o.JobName,
    p.FirstName, p.LastName, p2.FirstName, p2.LastName,
    o.DueDate, o.Notes,
    r.SubmittedComplete,
    s.StageID

谢谢你的建议。我已经做了很长一段时间了,只是不能让它正常工作。

EN

回答 2

Stack Overflow用户

回答已采纳

发布于 2015-10-29 19:11:12

谢谢大家的建议,但我找到了自己的解决办法。对于整个sql,我将其放入临时表中。然后用这个来整理重复的OrderIds..。

代码语言:javascript
复制
    SELECT OrderID, CompanyID, ContactID, CompanyName, JobName, ContactName, 
    SalesRep, DueDate, IsRush , StageID, Notes, SubmittedComplete, 
    LocationCSV, Stages
    FROM (SELECT *, ROW_NUMBER() OVER(PARTITION BY OrderID ORDER BY OrderID DESC) 'RowRank'
          FROM @SearchTbl
         )sub 
    WHERE RowRank = 1
票数 0
EN

Stack Overflow用户

发布于 2015-10-28 19:30:52

看起来,使用单个SELECT语句做的工作太多了。如果您希望每个唯一的OrderID都有一行,那么不要与具有相同OrderID的多行的表连接。删除GROUP子句。使用一个或多个单独的SELECT语句从每个OrderID具有多行的表中获取详细信息。

票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/33399699

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档