在加入和条件方面需要帮助

debugcn 发表于 Dev

科技班加罗尔

客户表：

customerId  customerName    startTime   projectDateOfCompletion
1            ANZ             2015-12-05       NULL
2            Barklays        2015-11-25     2016-02-10
3            Grindlays       2016-02-06       NULL

报告表：

customerId  repId   fromDateTime    toDateTim
1           1       2015-12-05      2016-02-05
1           2       2016-02-06         NULL
2           3       2015-11-25      2016-02-10
3           1       2016-02-08         NULL

客户支持表：

repId   customerSupportRep
1       Jim Daniel      
2       Mark Chad       
3       Juan Maximo

不确定如何以表格形式格式化表格。希望看起来像桌子。我正在寻找以下查询的帮助

SELECT 
    t.customerName AS 'Customer Name', 
    u.customerSupportRep AS 'Customer   Support Representative', 
    t.startTime AS 'Start Date/Time'
FROM Customer t
    JOIN CustomerSupport u 
        ON u.repId = ( SELECT repId 
                       FROM Reports 
                       WHERE  
                            customerId = t.customerId AND  
                            (CASE 
                                WHEN (@archiveStartTime IS NOT NULL )
                                    then  @archiveStartTime BETWEEN fromDateTime AND toDateTime
                                ELSE toDateTime IS NULL 
                            END)
                    )                        
    WHERE 
        t.projectDateOfCompletion IS NULL OR 
        t.projectDateOfCompletion = '';

如果@archiveStartTime是NULL，我想记录projectDateOfCompletion为NULL，如果@archiveStartTime不为空，然后我想列出所有记录中，其中@archiveStartTime间的谎言fromDateTime和toDateTime和projectDateOfCompletion为NULL。

任何帮助将不胜感激。

萨加斯

尝试这个：

`SELECT t.customerName AS 'Customer Name', u.customerSupportRep AS 'Customer   Support Representative', t.startTime AS 'Start Date/Time'
FROM Customer t
    JOIN CustomerSupport u ON u.repId = ( SELECT repId FROM Reports WHERE  customerId = t.customerId AND  
        ((@archiveStartTime IS NOT NULL
          and @archiveStartTime BETWEEN fromDateTime AND toDateTime)
        or (@archiveStartTime IS NULL AND toDateTime IS NULL )
        ) )
    WHERE t.projectDateOfCompletion IS NULL OR t.projectDateOfCompletion = '';`

该查询很难阅读，所以也许我把条件弄错了。我的想法是：代替您在那里所做的案例，像这样使用OR（如果查询未在运行，请自行调整而不是案例）

AND  
        ((@archiveStartTime IS NOT NULL
          and @archiveStartTime BETWEEN fromDateTime AND toDateTime)
        or (@archiveStartTime IS NULL AND toDateTime IS NULL ))

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。