mysql - MySQL 联合查询：在一个联合查询中将 TEMP ClaimID 字段值传递给 TEMP1 表 ClaimID

Question

CREATE DEFINER=`root`@`localhost` PROCEDURE `SP_DupAuditCriteria_1`()
BEGIN
    set @RowNbr=concat(date_format(curdate(),'%Y%m%d'),'0000000');

select temp.* from
(SELECT c.*, concat(c.VendorID,a.VendorID) as MergeH200A, concat(a.VendorID,c.VendorID) as MergeH200B, 'New' as Record_Type, @RowNbr:= @RowNbr + 1 AS ClaimID
FROM tbldupaudit_currentitems AS c
    inner join tbldupaudit_archiveitems a 
        on c.InvoiceID = a.InvoiceID
        and c.GrossAmount = a.GrossAmount) as temp
    inner join tbldupaudit_archiveitems b
        on temp.InvoiceID = b.InvoiceID
        and temp.GrossAmount = b.GrossAmount
        and temp.VendorID = b.VendorID
        and temp.VoucherID <> b.VoucherID
Union all
select temp1.* from
(SELECT f.*, concat(f.VendorID,d.VendorID) as MergeH200A, concat(d.VendorID,f.VendorID) as MergeH200B, 'ARCHIVE' as Record_Type, 1 AS ClaimID
FROM tbldupaudit_archiveitems AS f
    inner join tbldupaudit_currentitems d 
        on f.InvoiceID = d.InvoiceID
        and f.GrossAmount = d.GrossAmount) as temp1
    inner join tbldupaudit_currentitems e
        on temp1.InvoiceID = e.InvoiceID
        and temp1.GrossAmount = e.GrossAmount
        and temp1.VendorID = e.VendorID
        and temp1.VoucherID <> e.VoucherID
order by InvoiceID, Record_Type DESC;

END 

尝试为每对创建唯一的 ClaimID。我能够为前半联合所有生成序列号，但相同的 ClaimID 不能在另一半联合全部生成。请帮助我了解如何为匹配的项目创建/生成一个唯一 ID。

谢谢！] 1

标签： mysqlparameter-passingunionunion-all

Answer 1

在第二次选择中（在 UNION ALL 之后），您不会增加 var，因此如果您希望两个值都增加，请尝试在第二次选择中使用 var

select temp.* 
from (
    SELECT c.*, concat(c.VendorID,a.VendorID) as MergeH200A, concat(a.VendorID,c.VendorID) as MergeH200B, 'New' as Record_Type
        , @RowNbr:= @RowNbr + 1 AS ClaimID
    FROM tbldupaudit_currentitems AS c
    inner join tbldupaudit_archiveitems a on c.InvoiceID = a.InvoiceID
        and c.GrossAmount = a.GrossAmount
    ) as temp
inner join tbldupaudit_archiveitems b on temp.InvoiceID = b.InvoiceID
    and temp.GrossAmount = b.GrossAmount
        and temp.VendorID = b.VendorID
            and temp.VoucherID <> b.VoucherID
Union all
select temp1.* 
from ( 
    SELECT f.*, concat(f.VendorID,d.VendorID) as MergeH200A, concat(d.VendorID,f.VendorID) as MergeH200B, 'ARCHIVE' as Record_Type
        , @RowNbr:= @RowNbr + 1 AS ClaimID
    FROM tbldupaudit_archiveitems AS f
    inner join tbldupaudit_currentitems d on f.InvoiceID = d.InvoiceID
        and f.GrossAmount = d.GrossAmount
) as temp1
inner join tbldupaudit_currentitems e on temp1.InvoiceID = e.InvoiceID
      and temp1.GrossAmount = e.GrossAmount
        and temp1.VendorID = e.VendorID
            and temp1.VoucherID <> e.VoucherID
order by InvoiceID, Record_Type DESC;