时间戳

首页 > 解决方案 > 合并 2 个 SQL Server 查询

sql - 合并 2 个 SQL Server 查询

问题描述

我们必须每周运行 SQL Server 查询，考虑从最近的星期六 00:01 到星期六 23:59（科威特时间）的帐户事务。该过程考虑了数千行，因此非常繁重。我决定分两步完成，主要是因为我无法将它变成一个步骤；）

我首先必须将每个事务的 SQL Server 日期列偏移到科威特时间。

由于我通过 PHP 使用此脚本，因此我分两步进行：首先定义最新的星期六日期范围（例如，如果查询在星期六运行，它将计算上一个星期六的范围），然后使用变量对于第二个脚本。

在这里，我的第一个查询是上周六：

SELECT
    (datetime, CASE 
                  WHEN DATENAME(weekday, CAST(CURRENT_TIMESTAMP AS date)) = 'Sunday' 
                     THEN DATEADD(DAY, -1, CAST(CURRENT_TIMESTAMP AS date))
                  WHEN DATENAME(weekday, CAST(CURRENT_TIMESTAMP AS date)) = 'Monday' 
                     THEN DATEADD(DAY, -2, CAST(CURRENT_TIMESTAMP AS date))
                  WHEN DATENAME(weekday, CAST(CURRENT_TIMESTAMP AS date)) = 'Tuesday' 
                     THEN DATEADD(DAY, -3, CAST(CURRENT_TIMESTAMP AS date))
                  WHEN DATENAME(weekday, CAST(CURRENT_TIMESTAMP AS date)) = 'Wednesday' 
                     THEN DATEADD(DAY, -4, CAST(CURRENT_TIMESTAMP AS date))
                  WHEN DATENAME(weekday, CAST(CURRENT_TIMESTAMP AS date)) =  'Thursday' 
                     THEN DATEADD(DAY, -5, CAST(CURRENT_TIMESTAMP AS date))
                  WHEN DATENAME(weekday, CAST(CURRENT_TIMESTAMP AS date)) =  'Friday' 
                     THEN DATEADD(DAY, -6, CAST(CURRENT_TIMESTAMP AS date))
                  WHEN DATENAME(weekday, CAST(CURRENT_TIMESTAMP AS date)) = 'Saturday' 
                     THEN DATEADD(DAY, -0, CAST(CURRENT_TIMESTAMP AS date))
               END) at time zone 'Arab Standard Time' AS start_time,
 CONVERT(datetime, 
                     CASE 
                            WHEN datename(weekday,cast(CURRENT_TIMESTAMP AS date)) = 'Sunday' THEN dateadd(day,0,cast(CURRENT_TIMESTAMP AS date))
                            WHEN datename(weekday,cast(CURRENT_TIMESTAMP AS date)) = 'Monday' THEN dateadd(day,-1,cast(CURRENT_TIMESTAMP AS date))
                            WHEN datename(weekday,cast(CURRENT_TIMESTAMP AS date)) = 'Tuesday' THEN dateadd(day,-2,cast(CURRENT_TIMESTAMP AS date))
                            WHEN datename(weekday,cast(CURRENT_TIMESTAMP AS date)) = 'Wednesday' THEN dateadd(day,-3,cast(CURRENT_TIMESTAMP AS date))
                            WHEN datename(weekday,cast(CURRENT_TIMESTAMP AS date)) = 'Thursday' THEN dateadd(day,-4,cast(CURRENT_TIMESTAMP AS date))
                            WHEN datename(weekday,cast(CURRENT_TIMESTAMP AS date)) = 'Friday' THEN dateadd(day,-5,cast(CURRENT_TIMESTAMP AS date))
                            WHEN datename(weekday,cast(CURRENT_TIMESTAMP AS date)) = 'Saturday' THEN dateadd(day,-6,cast(CURRENT_TIMESTAMP AS date))
                     END) at time zone 'Arab Standard Time' as end_time

然后我在另一个查询中插入来自上述查询的变量 $start_date 和 $end_date 以获得最终结果：

SELECT
    USERID,
    DEPOSIT,
    RMwin,
CASE
    WHEN DEPOSIT > 0 
        AND RMwin <= DEPOSIT 
        AND RMwin > 0 THEN
            RMWIN * 0.3 
            WHEN RMwin > DEPOSIT 
            AND DEPOSIT > 0 THEN
                DEPOSIT *.3 
                WHEN RMwin <= 0 THEN
                0 ELSE 0 
            END AS CB,
        CASE
                
                WHEN DEPOSIT + RMwin = 0 THEN
                0 ELSE 1 
            END AS check_,
            FORMAT ( min_lt, 'dd/MM/yy HH:mm' ) AS min_lt,
            format ( min_kuw, 'dd/MM/yy HH:mm' ) AS min_kuw,
            format ( max_lt, 'dd/MM/yy HH:mm' ) AS max_lt,
            format ( max_kuw, 'dd/MM/yy HH:mm' ) AS max_kuw,
            min_id,
            max_id 
        FROM
            (
            SELECT
                UserID,
                SUM ( DEPOSIT ) AS DEPOSIT,
                SUM ( RMwin ) AS RMwin,
                MIN ( AccountTranID ) AS min_id,
                MAX ( AccountTranID ) AS max_id,
                MIN ( local_time ) AS min_lt,
                MIN ( kuwait_date ) AS min_kuw,
                MAX ( local_time ) AS max_lt,
                MAX ( kuwait_date ) AS max_kuw 
            FROM
                (
                SELECT
                    AccountTranID,
                    TranType,
                    UserID,
                    datetime,
                    datetime AT TIME ZONE 'Romance Standard Time' AS local_time,
                    datetime AT TIME ZONE 'Romance Standard Time' AT TIME ZONE 'Arab Standard Time' AS kuwait_date,
                CASE
                        TranType 
                        WHEN 'GAME_B' THEN
                        - AmountReal 
                        WHEN 'GAME_W' THEN
                        - AmountReal 
                        WHEN 'REFUND' THEN
                        - AmountReal ELSE 0 
                    END AS RMwin,
                CASE
                        
                        WHEN TranType = 'DEPOSIT' THEN
                        AmountReal ELSE 0 
                    END AS DEPOSIT 
                FROM
                    admin_all.DataFeed 
                WHERE
                    datetime AT TIME ZONE 'Romance Standard Time' AT TIME ZONE 'Arab Standard Time' >= ( convert(datetime,'".$start_date."') AT TIME ZONE 'Arab Standard Time' ) 
                    AND datetime AT TIME ZONE 'Romance Standard Time' AT TIME ZONE 'Arab Standard Time' < ( convert(datetime,'".$end_date."') AT TIME ZONE 'Arab Standard Time' ) 
                ) trans 
            GROUP BY
                UserID 
            ) ok 
    ORDER BY
    CB DESC

不幸的是，这个查询对于我们的服务器资源来说非常繁重，我正试图找到一种方法来简化它以达到相同的结果。

有没有人可以解决这个问题？

标签： sqlsql-server

解决方案

考虑DATEPART工作日数字并应用算术来检索相对于当前日期的上周六到上周日。以下两个表达式可以替换CASE第一个查询的长语句：

DATEADD(DAY, -1*(DATEPART(DW, CURRENT_TIMESTAMP)), 
        CAST(CURRENT_TIMESTAMP AS DATE)) AS LAST_SATURDAY
    
DATEADD(DAY, -1*(DATEPART(DW, CURRENT_TIMESTAMP)-1), 
        CAST(CURRENT_TIMESTAMP AS DATE)) AS LAST_SUNDAY

从那里，轻松集成到大型查询的WHERE子句：

...
WHERE
 datetime AT TIME ZONE 'Romance Standard Time' AT TIME ZONE 'Arab Standard Time' >= 
  ( DATEADD(DAY, -1*(DATEPART(DW, CURRENT_TIMESTAMP)), 
            CAST(CURRENT_TIMESTAMP AS DATE)) AT TIME ZONE 'Arab Standard Time' ) 
AND datetime AT TIME ZONE 'Romance Standard Time' AT TIME ZONE 'Arab Standard Time' < 
  ( DATEADD(DAY, -1*(DATEPART(DW, CURRENT_TIMESTAMP)-1), 
            CAST(CURRENT_TIMESTAMP AS DATE))AT TIME ZONE 'Arab Standard Time' )
...

PHP

在 PHP 中运行时，考虑参数化，您甚至可以在 PHP 中计算日期（无需单独的仅返回常量的 SQL 查询）：

$start_date = date('Y-m-d H:i:s', strtotime("last Saturday"));
$end_date = date('Y-m-d H:i:s', strtotime("last Sunday"));

$sql = "...
        WHERE 
            datetime AT TIME ZONE 'Romance Standard Time' AT TIME ZONE 'Arab Standard Time' >= ?
        AND datetime AT TIME ZONE 'Romance Standard Time' AT TIME ZONE 'Arab Standard Time' < ?
        ..."

$params = array($start_date , $end_date);

# sqlsrv API
$stmt = sqlsrv_query($conn, $sql, $params);

# odbc API
$stmt    = odbc_prepare($conn, $sql);
$success = odbc_execute($stmt, $params);

推荐阅读