#mysql
#mysql
Вопрос:
Я хочу использовать имя псевдонима (Raseed), чтобы снова вычислить его с другим полем (d.MtbkRaseed) и поставить его в условие, чтобы узнать, будет ли его результат больше 0, он даст мне значение или даст мне другое значение. на самом деле, мне нужно использовать имя псевдонима вместо того, чтобы повторять код, который он извлекает (Raseed), так есть ли какой-нибудь способ сделать?
SELECT
d.RegDate,
d.RegID,
d.RegRef,
d.RegType,
d.RegdID,
d.RegdAccID,
d.RegdDetails,
d.RegdM2,
d.RegdD2,
d.RegdNo,
d.RegdMtbkRaseed,
d.RegdMtbkFark,
d.RegdMtbkStatus,
d.RegdMtbkNotes,
d.RecordID,
d.AccName,
d.CurrencyName,
IF(@w = d.RegdAccID,
CAST(@s := @s d.RegdM2-d.RegdD2 AS DECIMAL(10,2)),
CAST(@s := 0 d.RegdM2-d.RegdD2 AS DECIMAL(10,2))) AS Raseed,
(@w := d.RegdAccID) as x
FROM
(SELECT
a.RegDate,
a.RegID,
a.RegRef,
a.RegType,
b.RegdID,
b.RegdAccID,
b.RegdDetails,
b.RegdM2,
b.RegdD2,
b.RegdNo,
b.RegdMtbkRaseed,
b.RegdMtbkFark,
b.RegdMtbkStatus,
b.RegdMtbkNotes,
IF(a.RegType='daf',
CONCAT('Daf_',(SELECT SanadDafID FROM tblsanaddaf
WHERE
SanadDafRef = a.RegRef)),
IF(a.RegType='qabd',
CONCAT('Qbd_',(SELECT SanadQbdID FROM tblsanadqbd
WHERE
SanadQbdRef = a.RegRef)),
IF(a.RegType='tahmil',
CONCAT('Tah_',(SELECT FatoraID FROM tblfatora
WHERE
FatoraRef = a.RegRef)),
IF(a.RegType='qaid',
CONCAT('Tah_',a.RegID),0)))) as RecordID,
n.AccName,
o.CurrencyName
FROM tblregs2 b
INNER JOIN tblregs1 a ON b.RegRef = a.RegRef
INNER JOIN tblaccounts n ON n.AccID = b.RegdAccID
INNER JOIN tblcurrencies o ON o.CurrencyID = n.AccCurrID
WHERE
(b.RegdM2>0 or b.RegdD2>0)) d
CROSS JOIN (SELECT @s := 0) c
CROSS JOIN (SELECT @w := 0) i
ORDER BY
d.RegdAccID,
d.RegDate,
d.RegID,
d.RegdID
и я попробовал это решение, но оно неверно:
SELECT
d.RegDate,
d.RegID,
d.RegRef,
d.RegType,
d.RegdID,
d.RegdAccID,
d.RegdDetails,
d.RegdM2,
d.RegdD2,
d.RegdNo,
d.RegdMtbkRaseed,
d.RegdMtbkFark,
d.RegdMtbkStatus,
d.RegdMtbkNotes,
d.RecordID,
d.AccName,
d.CurrencyName,
IF(@w = d.RegdAccID,
CAST(@s := @s d.RegdM2-d.RegdD2 AS DECIMAL(10,2)),
CAST(@s := 0 d.RegdM2-d.RegdD2 AS DECIMAL(10,2))) AS Raseed,
IF(IF(@w = d.RegdAccID,
CAST(@ss := @ss d.RegdM2-d.RegdD2 AS DECIMAL(10,2)),
CAST(@ss := 0 d.RegdM2-d.RegdD2 AS DECIMAL(10,2))) AS xRaseed <> 0 ,'ok','No') as xxs,
(@w := d.RegdAccID) as x
FROM
(SELECT
a.RegDate,
a.RegID,
a.RegRef,
a.RegType,
b.RegdID,
b.RegdAccID,
b.RegdDetails,
b.RegdM2,
b.RegdD2,
b.RegdNo,
b.RegdMtbkRaseed,
b.RegdMtbkFark,
b.RegdMtbkStatus,
b.RegdMtbkNotes,
IF(a.RegType='daf',
CONCAT('Daf_',(SELECT SanadDafID FROM tblsanaddaf
WHERE
SanadDafRef = a.RegRef)),
IF(a.RegType='qabd',
CONCAT('Qbd_',(SELECT SanadQbdID FROM tblsanadqbd
WHERE
SanadQbdRef = a.RegRef)),
IF(a.RegType='tahmil',
CONCAT('Tah_',(SELECT FatoraID FROM tblfatora
WHERE
FatoraRef = a.RegRef)),
IF(a.RegType='qaid',
CONCAT('Tah_',a.RegID),0)))) as RecordID,
n.AccName,
o.CurrencyName
FROM tblregs2 b
INNER JOIN tblregs1 a ON b.RegRef = a.RegRef
INNER JOIN tblaccounts n ON n.AccID = b.RegdAccID
INNER JOIN tblcurrencies o ON o.CurrencyID = n.AccCurrID
WHERE
(b.RegdM2>0 or b.RegdD2>0)) d
CROSS JOIN (SELECT @s := 0) c
CROSS JOIN (SELECT @ss := 0) cc
CROSS JOIN (SELECT @w := 0) i
ORDER BY
d.RegdAccID,
d.RegDate,
d.RegID,
d.RegdID
Ответ №1:
SELECT
d.RegDate,
d.RegID,
d.RegRef,
d.RegType,
d.RegdID,
d.RegdAccID,
d.RegdDetails,
d.RegdM2,
d.RegdD2,
d.RegdNo,
d.RegdMtbkRaseed,
d.RegdMtbkFark,
d.RegdMtbkStatus,
d.RegdMtbkNotes,
d.RecordID,
d.AccName,
d.CurrencyName,
IF(@w = d.RegdAccID,
CAST(@s := @s d.RegdM2-d.RegdD2 AS DECIMAL(10,2)),
CAST(@s := 0 d.RegdM2-d.RegdD2 AS DECIMAL(10,2))) AS Raseed,
IF(IF(@w = d.RegdAccID,
CAST(@ss := @ss d.RegdM2-d.RegdD2 AS DECIMAL(10,2)),
CAST(@ss := 0 d.RegdM2-d.RegdD2 AS DECIMAL(10,2)))
<> d.RegdMtbkRaseed AND d.RegdMtbkStatus='MTBK',
'Cancel',d.RegdMtbkStatus) AS NewMtbkStatus
,(@w := d.RegdAccID) as x
FROM
(SELECT
a.RegDate,
a.RegID,
a.RegRef,
a.RegType,
b.RegdID,
b.RegdAccID,
b.RegdDetails,
b.RegdM2,
b.RegdD2,
b.RegdNo,
b.RegdMtbkRaseed,
b.RegdMtbkFark,
b.RegdMtbkStatus,
b.RegdMtbkNotes,
IF(a.RegType='daf',
CONCAT('Daf_',(SELECT SanadDafID FROM tblsanaddaf
WHERE
SanadDafRef = a.RegRef)),
IF(a.RegType='qabd',
CONCAT('Qbd_',(SELECT SanadQbdID FROM tblsanadqbd
WHERE
SanadQbdRef = a.RegRef)),
IF(a.RegType='tahmil',
CONCAT('Tah_',(SELECT FatoraID FROM tblfatora
WHERE
FatoraRef = a.RegRef)),
IF(a.RegType='qaid',
CONCAT('Tah_',a.RegID),0)))) as RecordID,
n.AccName,
o.CurrencyName
FROM tblregs2 b
INNER JOIN tblregs1 a ON b.RegRef = a.RegRef
INNER JOIN tblaccounts n ON n.AccID = b.RegdAccID
INNER JOIN tblcurrencies o ON o.CurrencyID = n.AccCurrID
WHERE
(b.RegdM2>0 or b.RegdD2>0)) d
CROSS JOIN (SELECT @s := 0) c
CROSS JOIN (SELECT @ss := 0) cc
CROSS JOIN (SELECT @w := 0) i
ORDER BY
d.RegdAccID,
d.RegDate,
d.RegID,
d.RegdID