我有這個查詢：
SELECT r.record_id,
r.part_id,
r.record_dt,
a.association_part_a,
a.association_part_b,
a.association_type,
a.association_id,
r.product_id
FROM dbo.synfact_association AS a
INNER JOIN dbo.synfact_record AS r
ON a.record_id = r.record_id
WHERE ( r.part_id IN (SELECT part_id
FROM dbo.synfact_record AS synfact_record_1
WHERE ( record_status = 1 )
AND ( record_type = 0 )) )
AND ( r.product_id IN ( 38, 39, 40, 41,
42, 43, 44, 45,
46, 37, 47, 48,
49, 50, 51, 52,
53, 54, 58, 59 ) )
AND ( r.record_id > 499 )
AND ( r.record_status = 1 )
我想使用第二個查詢的 record_dt 作為第一個查詢中的 record_dt 的替換。我想通過鏈接 part_id 上的值來做到這一點，因為它們都有。第一個查詢給出一個包含 part_id 的 record_id 串列，第二個查詢為每個 record_id 獲取最新的 record_dt。我的最終目標是從第二個查詢中獲取最新的 record_dt 以替換第一個查詢。我可以在 part_id 上鏈接它們。
我需要用這個 SELECT 替換 r.record_dt：
SELECT DISTINCT RECORD_DT FROM synfact_record WHERE PROCESS_STEP_LIST_ID IN (21,22,23) ORDER BY RECORD_DT DESC
這是第一個查詢的作用
| 記錄 ID | PART_ID | RECORD_DT | ASSOCIATION_PART_A | ASSOCIATION_PART_B | 協會_TYPE | ASSOCIATION_ID | 產品編號 |
|---|---|---|---|---|---|---|---|
| 286660 | SYN12021020100018 | 2021-02-15 11:18:11.840 | SYN12021020100018 | 21-02-01-000003 | 唯一身份 | 452028 | 39 |
| 287146 | SYN12021020300773 | 2021-02-17 07:30:59.603 | SYN12021020300773 | 2102-00-005218 | 唯一身份 | 455735 | 38 |
| 287147 | SYN12021020300774 | 2021-02-17 07:31:04.780 | SYN12021020300774 | 2102-00-005219 | 唯一身份 | 455736 | 38 |
The second query returns:
| RECORD_DT |
|---|
| 2021-10-20 11:36:02.670 |
| 2021-10-20 11:35:29.263 |
| 2021-10-20 11:34:59.583 |
I want to replace the record_dt by the one with the second SELECT.
I know I must append it to the first query, I just don't know how...
Which method is best to do this?
uj5u.com熱心網友回復：
如果您可以 LIMIT 1 到您的 select 子句，您可以使用這樣的查詢：
SELECT r.record_id,
r.part_id,
CASE
WHEN r.PROCESS_STEP_LIST_ID IN (21 , 22, 23) THEN
(SELECT DISTINCT RECORD_DT FROM synfact_record WHERE PROCESS_STEP_LIST_ID IN (21,22,23) ORDER BY RECORD_DT DESC LIMIT 1)
ELSE r.RECORD_DT
END as record_dt,
a.association_part_a,
a.association_part_b,
a.association_type,
a.association_id,
r.product_id
FROM dbo.synfact_association AS a
INNER JOIN dbo.synfact_record AS r
ON a.record_id = r.record_id
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/346749.html
標籤：sql sql-server tsql
上一篇：使用SQL根據特定列中相似單元格的數量對表的行進行重新排序
下一篇：需求和供應Python演算法