mysql - 如何在此查询中重新启动 MYSQL 增量编号

Question

我希望我的查询显示列有一个序列号，当有新名称时，该序列号再次从 1 开始。

谁能帮我查询MYSQL？

这是我的 vtiger 查询

select 
date_format(vtiger_crmentity.createdtime,'%d-%m-%Y') as Tanggal_Transaksi,
vendorname as Machine_Type,
vtiger_leaddetails.lastname as "Tipe Kartu",
vtiger_contactdetails.contactid as "Member",
vtiger_contactdetails.firstname as "Nama Depan",
vtiger_contactdetails.lastname as "Nama Belakang",
vtiger_servicecontractscf.cf_1085 as "Nama Bank",
campaignname as "No. Kartu",
cf_1095 as Total_Gesek,
" " as Saldo,
vtiger_accounting_payments.amount as Gsk,
" " as Kurang_Gesek,
rolename as Cabang
FROM
vtiger_accounting_payments, vtiger_accounting, vtiger_servicecontracts,       
vtiger_servicecontractscf, vtiger_crmentity,vtiger_sqlreports,  
vtiger_sqlreportscf,vtiger_user2role, vtiger_role, vtiger_users, vtiger_vendor, vtiger_vendorcf, vtiger_leaddetails, vtiger_contactdetails,  vtiger_campaign
WHERE vtiger_accounting.accountingrelated2=vtiger_servicecontracts.servicecontractsid
and vtiger_campaign.campaignid=cf_1155
and vtiger_contactdetails.contactid=vtiger_servicecontracts.contactid
and vtiger_leaddetails.leadid=cf_1091
and vtiger_vendor.vendorid=vtiger_vendorcf.vendorid
and vtiger_vendorcf.cf_1077=vtiger_accounting_payments.machinename
and vtiger_accounting.accountingid=vtiger_accounting_payments.idtransaction
and vtiger_servicecontracts.servicecontractsid=vtiger_servicecontractscf.servicecontractsid
and vtiger_crmentity.crmid=vtiger_servicecontracts.servicecontractsid
and vtiger_crmentity.deleted=0
and vtiger_sqlreports.sqlreportsid=216
and vtiger_user2role.userid=vtiger_users.id
and vtiger_crmentity.smcreatorid=vtiger_user2role.userid
and vtiger_user2role.roleid=vtiger_role.roleid
and rolename=cf_1205
and vtiger_sqlreports.sqlreportsid=vtiger_sqlreportscf.sqlreportsid  
and vtiger_sqlreports.sqlreportsid=216
and date_format(vtiger_crmentity.createdtime,'%Y-%m-%d') >= date_format(cf_1157,'%Y-%m-%d') and date_format(vtiger_crmentity.createdtime,'%Y-%m-%d') <=date_format(cf_1159,'%Y-%m-%d')

Answer 1

为计数器使用一个用户变量，并在名称更改时将其重置为 1。

SELECT @no := IF(name = @prevName, @no + 1, 1) AS no,
       @prevName := name AS name, hobby
FROM (SELECT name, hobby
      FROM kids AS k
      JOIN kidshobby AS kh ON k.NameID = kh.NameID
      JOIN hobby AS h ON hk.HobbyID = h.HobbyID
      ORDER BY name, hobby) AS nh
CROSS JOIN (SELECT @no = 0, @prevName = '') AS vars