嘗試根據最近的時間戳*在*特定日期之前加入2個表-有解無憂

這是我的 SQL 陳述句，在我們創建新資料庫之前，它似乎運行良好。這種方法似乎適用于另一對結構類似的表。

SELECT *
FROM tele2_details AS d
    INNER JOIN 
    tele2_usage AS u
    ON    
        d.iccid = u.iccid
    AND
        u.timestamp = (
        SELECT MAX(u.timestamp)
        FROM tele2_usage 
        WHERE u.timestamp <= DATE_FORMAT('2022-01-08 09:30:00','%Y-%m-%d %H:%i:%s') 
      )
WHERE
    accountCustom1='Horizon'

我在這里嘗試做的是將詳細資訊表與使用情況表連接起來，使用情況行只包含 sim 卡的 iccid、時間戳以及它們當前的使用情況（以位元組為單位）。這應該做的是在指定日期（2022-01-08 09:30:00）之前找到最近的使用記錄。這應該給我一組模擬人生，每個模擬人生在指定時間之前加入了它最近的使用記錄，但是我通常在這個特定的表格組合上得到零結果。

具體來說，它確實匹配日期與查詢中指定的日期完全相同的任何記錄，但不匹配早于或等于指定日期的日期。任何人都可以幫我解決我要去的地方。這個查詢在以前的資料庫中運行良好，我們正在重建我們的系統，現在這已經成為一個問題。

提前感謝您的幫助。

編輯這里有更多資訊，我希望能讓這個問題更有意義。所以這里是詳細資訊表的大綱，我洗掉了一些列，但這至少是表的說明。

CREATE TABLE `tele2_details` (
    `iccid` VARCHAR(255) NOT NULL,
    `msisdn` VARCHAR(255) NOT NULL,
    `status` VARCHAR(255) NOT NULL,
    `ratePlan` VARCHAR(255) NOT NULL,
    `communicationPlan` VARCHAR(255) NOT NULL,
    PRIMARY KEY (`iccid`)
)
COLLATE='utf8mb4_0900_ai_ci'
ENGINE=InnoDB
;

然后我們還有一個使用表，它存盤了 sim 卡的資料使用示例，以及時間戳......

CREATE TABLE `tele2_usage` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `iccid` VARCHAR(255) NOT NULL COLLATE 'latin1_swedish_ci',
    `msisdn` VARCHAR(255) NOT NULL COLLATE 'latin1_swedish_ci',
    `timestamp` DATETIME NOT NULL,
    `ctd_data_usage` BIGINT NOT NULL,
    `ctd_sms_usage` BIGINT NOT NULL,
    `ctd_voice_usage` BIGINT NOT NULL,
    `session_count` INT NOT NULL,
    PRIMARY KEY (`id`)
)
COLLATE='utf8mb4_hr_0900_ai_ci'
ENGINE=InnoDB
AUTO_INCREMENT=10116319
;

我嘗試創建的查詢應回傳一組 sim 詳細資訊，并與最接近但不是在特定時間之后的使用記錄相結合。

因此，如果您查看頂部的原始查詢，我正在嘗試將詳細資訊加入到**最接近但不是在 2022-01-08 09:30:00 ** 之后的使用記錄中

我希望這是有道理的。

讓我們看看一個特定的模擬

SELECT iccid, msisdn, status, ratePlan, communicationPlan FROM tele2_details WHERE iccid='xxxx203605100034xxxx'

結果 1 場比賽

"xxxx203605100034xxxx" "xxxx9120012xxxx" "ACTIVATED" "Pay as use - Existing Business" "Data LTE"

如果我查看同一個 sim 的使用表，我可以看到許多應該滿足我的條件的記錄

SELECT id, iccid, TIMESTAMP, ctd_data_usage FROM tele2_usage WHERE iccid='xxxx203605100034xxxx' AND TIMESTAMP <= '2022-01-08 09:30:00'

結果是

"10096279"  "xxxx203605100034xxxx"  "2022-01-08 09:01:00"   "77517560"
"10092271"  "xxxx203605100034xxxx"  "2022-01-08 08:01:03"   "77002733"
"10088263"  "xxxx203605100034xxxx"  "2022-01-08 07:01:11"   "76270445"
"10084255"  "xxxx203605100034xxxx"  "2022-01-08 06:01:05"   "76270445"

其中我想選擇第一條記錄（帶有 09:01 時間戳）來加入詳細資訊記錄。我可以通過以下查詢獲得該時間戳

SELECT MAX(timestamp)
FROM tele2_usage 
WHERE TIMESTAMP <= DATE_FORMAT('2022-01-08 09:30:00','%Y-%m-%d %H:%i:%s') AND iccid='xxxx203605100034xxxx'

這導致'2022-01-08 09:01:00'，這正是我想要的。所以現在我把它們放在一起......

SELECT *
FROM tele2_details AS d
    INNER JOIN 
    tele2_usage AS u
    ON    
      d.iccid = u.iccid
    AND
      u.timestamp = (
        SELECT MAX(timestamp)
        FROM tele2_usage 
        WHERE timestamp <= DATE_FORMAT('2022-01-08 09:30:00','%Y-%m-%d %H:%i:%s') 
      )
WHERE
    accountCustom1='Horizon' AND iccid='xxxx203605100034xxxx'

And i get nothing! I would expect to get back the details record joined with that particular sim, but actually I get zero results and I don't understand why.

Ideally I would remove the final AND for the iccid and I would expect to recieve a set of all the sims for that client with the usage from closest to but not after the specified date.

So with that explanation, does anyone know why I'm not getting any records? I have a similar table for another sim provider that structured exactly the same, with a details table and a usage table and this query works just fine on that table. I simply can't understand why this doesn't work.

Edit 2 @Serg suggested trying to alias the subquery (I think that's what it's called) which resulted in the following code...

SELECT *
FROM tele2_details AS d
    INNER JOIN 
    tele2_usage AS u
    ON    
      d.iccid = u.iccid
    AND
      u.timestamp = (
        SELECT MAX(u2.timestamp)
        FROM tele2_usage AS u2
        WHERE u2.timestamp <= DATE_FORMAT('2022-01-08 09:30:00','%Y-%m-%d %H:%i:%s') 
      )
WHERE
    accountCustom1='Horizon'

Unfortunately this still resulted in zero results.

uj5u.com熱心網友回復：

您應該關聯子查詢：

SELECT *
FROM tele2_details AS d INNER JOIN tele2_usage AS u
ON d.iccid = u.iccid
AND u.timestamp = (
        SELECT MAX(u2.timestamp)
        FROM tele2_usage u2 
        WHERE d.iccid = u2.iccid AND u2.timestamp <= DATE_FORMAT('2022-01-08 09:30:00','%Y-%m-%d %H:%i:%s') 
      )
WHERE d.accountCustom1='Horizon';

或者，加入聚合查詢：

SELECT *
FROM tele2_details AS d 
INNER JOIN tele2_usage AS u ON d.iccid = u.iccid
INNER JOIN (
  SELECT iccid, MAX(timestamp) timestamp
  FROM tele2_usage 
  WHERE timestamp <= DATE_FORMAT('2022-01-08 09:30:00','%Y-%m-%d %H:%i:%s') 
  GROUP BY iccid
) m ON m.iccid = u.iccid AND m.timestamp = u.timestamp
WHERE d.accountCustom1='Horizon';

轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/406050.html

標籤：

上一篇：Mysql：用什么代替WITH陳述句或TEMPORARY表？

下一篇：由于外鍵約束，創建表失敗