MYSQL存储过程-Concat行值 [英] MYSQL Stored Procedure - Concat Row Value

问题描述

更新

Update

抱歉，回复延迟.昨天，当我发送邮件时，我正在旅行.我使用的是5.6版.这就是我的想法，它确实标记了何时发生更改，但是问题是排序导致结果集出现故障.我想我将需要创建一个临时表，并在设置订单后立即应用条件格式.

Sorry for the delay in responding. I was traveling yesterday when I sent that in. I am on version 5.6. This is what I was thinking of and it does mark when there is change, but the issue is the sort causes the result set to fall out of order. I guess I will need to create a temporary table and just apply the conditional formatting once the order has been set.

SET @previousstate ='';

SET @previousstate = '';

选择frm.fi_details.PortfolioCode AS'Portfolio Code'，id，if(@previousstate！= frm.fi_details.PortfolioCode，concat('***'，@ previousstate:= frm.fi_details.PortfolioCode，'****')，frm.fi_details.PortfolioCode)AS测试

SELECT frm.fi_details.PortfolioCode AS 'Portfolio Code', id, if(@previousstate != frm.fi_details.PortfolioCode, concat('***',@previousstate:= frm.fi_details.PortfolioCode,'****'), frm.fi_details.PortfolioCode) AS test

从frm.fi_details

FROM frm.fi_details

在哪里frm.fi_details.manager ='Shartz'并且frm.fi_details.PortfolioCode Like('Brown％')

WHERE frm.fi_details.manager = 'Shartz' And frm.fi_details.PortfolioCode Like ('Brown%')

组别frm.fi_details.PortfolioCode，frm.fi_details.Symbol

GROUP BY frm.fi_details.PortfolioCode, frm.fi_details.Symbol

订购frm.fi_details.PortfolioCode，frm.fi_details.WorstDate_Final;

ORDER BY frm.fi_details.PortfolioCode, frm.fi_details.WorstDate_Final;

brownfmi 143753 brownfmi

brownfmi 143753 brownfmi

brownfmi 143748 brownfmi

brownfmi 143748 brownfmi

brownfmi 143744 brownfmi

brownfmi 143744 brownfmi

brownfmi 143739 brownfmi

brownfmi 143739 brownfmi

brownfmi 143740 brownfmi

brownfmi 143740 brownfmi

brownfmi 143738 brownfmi

brownfmi 143738 brownfmi

brownfmi 143747 brownfmi

brownfmi 143747 brownfmi

brownfmi 143749 brownfmi

brownfmi 143749 brownfmi

brownfmi 143761 brownfmi

brownfmi 143761 brownfmi

brownhcb 143769 brownhcb

brownhcb 143769 brownhcb

brownhcb 143774 brownhcb

brownhcb 143774 brownhcb

brownhcb 143780 brownhcb

brownhcb 143780 brownhcb

brownhcb 143781 brownhcb

brownhcb 143781 brownhcb

brownhcb 143773 brownhcb

brownhcb 143773 brownhcb

brownhcb 143784 brownhcb

brownhcb 143784 brownhcb

brownhcb 143783 brownhcb

brownhcb 143783 brownhcb

brownhcb 143785 brownhcb

brownhcb 143785 brownhcb

brownhcb 143786 brownhcb

brownhcb 143786 brownhcb

brownhcb 143762 **** brownhcb ****

brownhcb 143762 ****brownhcb****

brownhcb 143763 brownhcb布朗HCB 143788布朗HCBbrownhcb 143777 brownhcb布朗HCB 143767布朗HCB布朗HCB 143764布朗HCB布朗HCB 143779布朗HCB布朗HCB 143778布朗HCB布朗HCB 143789布朗HCB布朗HCB 143772布朗HCB布朗菲143799布朗菲布朗尼143808布朗尼布朗菲143805布朗菲布朗菲143806布朗菲布朗菲143793布朗菲布朗菲143795布朗菲布朗菲143797布朗菲布朗菲143791布朗菲布朗菲143801布朗菲布朗菲143807布朗菲布朗菲143798布朗菲布朗尼143804布朗尼布朗菲143796布朗菲brownhfi 143790 brownhfi 布朗菲143800布朗菲布朗菲143794布朗菲布朗菲143802布朗菲布朗尼143792布朗尼布朗菲143803布朗菲brownira 143809 brownira browntrf 143838 browntrf棕色trf 143860棕色trfbrowntrf 143905 browntrfbrowntrf 143891 browntrfbrowntrf 143846 browntrfbrowntrf 143877 browntrfbrowntrf 143857 browntrfbrowntrf 143872 browntrfbrowntrf 143834 browntrfbrowntrf 143898 browntrfbrowntrf 143841 browntrfbrowntrf 143817 browntrfbrowntrf 143826 browntrfbrowntrf 143822 browntrf棕色trf 143868棕色trfbrowntrf 143883 browntrfbrowntrf 143899 browntrf棕色trf 143840棕色trf棕色trf 143900棕色trfbrowntrf 143850 browntrf棕色trf 143869棕色trfbrowntrf 143865 browntrfbrowntrf 143837 browntrfbrowntrf 143844 browntrfbrowntrf 143851 browntrfbrowntrf 143901 browntrfbrowntrf 143833 browntrfbrowntrf 143856 browntrf棕色trf 143870棕色trfbrowntrf 143875 browntrfbrowntrf 143887 browntrfbrowntrf 143903 browntrf棕色trf 143828棕色trfbrowntrf 143847 browntrf棕色trf 143820棕色trf 143890棕色trfbrowntrf 143889 browntrf棕褐色143867棕褐色browntrf 143866 browntrfbrowntrf 143830 browntrfbrowntrf 143896 browntrfbrowntrf 143836 browntrfbrowntrf 143852 browntrf棕色trf 143876browntrf 143904 browntrfbrowntrf 143874 browntrfbrowntrf 143813 browntrfbrowntrf 143854 browntrfbrowntrf 143815 browntrfbrowntrf 143878 browntrfbrowntrf 143842 browntrfbrowntrf 143897 browntrfbrowntrf 143863 browntrfbrowntrf 143879 browntrf棕褐色143880棕褐色棕褐色143849棕褐色browntrf 143816 browntrfbrowntrf 143862 browntrf棕色trf 143858棕色trfbrowntrf 143831 browntrfbrowntrf 143832 browntrfbrowntrf 143835 browntrfbrowntrf 143873 browntrf棕褐色143848棕褐色browntrf 143839 browntrfbrowntrf 143827 browntrfbrowntrf 143864 browntrfbrowntrf 143823 browntrfbrowntrf 143895 browntrfbrowntrf 143884 browntrfbrowntrf 143871 browntrfbrowntrf 143810 browntrf browntrf 143824 browntrfbrowntrf 143821 browntrf棕色trf 143812棕色trfbrowntrf 143892 browntrf棕褐色143881棕褐色棕褐色143861棕褐色browntrf 143829 browntrf棕色trf 143819棕色trfbrowntrf 143811 browntrfbrowntrf 143894 browntrf棕色trf 143818棕色trfbrowntrf 143882 browntrfbrowntrf 143843 browntrfbrowntrf 143859 browntrfbrowntrf 143855 browntrfbrowntrf 143885 browntrfbrowntrf 143902 browntrfbrowntrf 143893 browntrfbrowntrf 143886 browntrfbrowntrf 143845 browntrfbrowntrf 143825 browntrfbrowntrf 143888 browntrfbrowntrf 143853 browntrfbrowntrf 143814 browntrf

brownhcb 143763 brownhcb brownhcb 143788 brownhcb brownhcb 143777 brownhcb brownhcb 143767 brownhcb brownhcb 143764 brownhcb brownhcb 143779 brownhcb brownhcb 143778 brownhcb brownhcb 143789 brownhcb brownhcb 143772 brownhcb brownhfi 143799 brownhfi brownhfi 143808 brownhfi brownhfi 143805 brownhfi brownhfi 143806 brownhfi brownhfi 143793 brownhfi brownhfi 143795 brownhfi brownhfi 143797 brownhfi brownhfi 143791 brownhfi brownhfi 143801 brownhfi brownhfi 143807 brownhfi brownhfi 143798 brownhfi brownhfi 143804 brownhfi brownhfi 143796 brownhfi brownhfi 143790 brownhfi brownhfi 143800 brownhfi brownhfi 143794 brownhfi brownhfi 143802 brownhfi brownhfi 143792 brownhfi brownhfi 143803 brownhfi brownira 143809 brownira browntrf 143838 browntrf browntrf 143860 browntrf browntrf 143905 browntrf browntrf 143891 browntrf browntrf 143846 browntrf browntrf 143877 browntrf browntrf 143857 browntrf browntrf 143872 browntrf browntrf 143834 browntrf browntrf 143898 browntrf browntrf 143841 browntrf browntrf 143817 browntrf browntrf 143826 browntrf browntrf 143822 browntrf browntrf 143868 browntrf browntrf 143883 browntrf browntrf 143899 browntrf browntrf 143840 browntrf browntrf 143900 browntrf browntrf 143850 browntrf browntrf 143869 browntrf browntrf 143865 browntrf browntrf 143837 browntrf browntrf 143844 browntrf browntrf 143851 browntrf browntrf 143901 browntrf browntrf 143833 browntrf browntrf 143856 browntrf browntrf 143870 browntrf browntrf 143875 browntrf browntrf 143887 browntrf browntrf 143903 browntrf browntrf 143828 browntrf browntrf 143847 browntrf browntrf 143820 browntrf browntrf 143890 browntrf browntrf 143889 browntrf browntrf 143867 browntrf browntrf 143866 browntrf browntrf 143830 browntrf browntrf 143896 browntrf browntrf 143836 browntrf browntrf 143852 browntrf browntrf 143876 browntrf browntrf 143904 browntrf browntrf 143874 browntrf browntrf 143813 browntrf browntrf 143854 browntrf browntrf 143815 browntrf browntrf 143878 browntrf browntrf 143842 browntrf browntrf 143897 browntrf browntrf 143863 browntrf browntrf 143879 browntrf browntrf 143880 browntrf browntrf 143849 browntrf browntrf 143816 browntrf browntrf 143862 browntrf browntrf 143858 browntrf browntrf 143831 browntrf browntrf 143832 browntrf browntrf 143835 browntrf browntrf 143873 browntrf browntrf 143848 browntrf browntrf 143839 browntrf browntrf 143827 browntrf browntrf 143864 browntrf browntrf 143823 browntrf browntrf 143895 browntrf browntrf 143884 browntrf browntrf 143871 browntrf browntrf 143810 browntrf browntrf 143824 browntrf browntrf 143821 browntrf browntrf 143812 browntrf browntrf 143892 browntrf browntrf 143881 browntrf browntrf 143861 browntrf browntrf 143829 browntrf browntrf 143819 browntrf browntrf 143811 browntrf browntrf 143894 browntrf browntrf 143818 browntrf browntrf 143882 browntrf browntrf 143843 browntrf browntrf 143859 browntrf browntrf 143855 browntrf browntrf 143885 browntrf browntrf 143902 browntrf browntrf 143893 browntrf browntrf 143886 browntrf browntrf 143845 browntrf browntrf 143825 browntrf browntrf 143888 browntrf browntrf 143853 browntrf browntrf 143814 browntrf

由于某种原因，如果存储行中的行与上一行不同，我将难以思考如何联系行.例如，如果我从我的表中选择状态，则选择到新的状态时，它会附加'**'以使其脱颖而出.我知道我必须设置一个变量，如果不是，还可以通过它来设置，但是我尝试的所有操作都失败了.:(

For some reason, I am having trouble thinking through how I would contact a row if it is different than the previous in a stored procedure. For example if I select states from my table, when the select gets to a new state have it append '**' to make it stand out. I know I have to set a variable and if else through it, but everything I tried has failed. :(

感谢您的帮助.

例如

** AR **

AR

AR

AR

** CA **

CA

CA

CA

推荐答案

您尚未显示现有代码，因此尚不清楚您的问题与存储过程之间的关系.如果运行的是MySQL 8.0，则可以执行 select 语句和窗口函数.为了在连续执行中获得一致结果，您需要一列来定义行的顺序-我假设 id :

You have not shown your existing code, so it is unclear how your question relates to stored procedure. If you are running MySQL 8.0, you can do this a select statement and window functions. To get a consistent result over consecutive executions, you would need a column that defines the ordering of the rows - I assumed id:

select
    id,
    case when row_number() over(partition by state order by id) = 1
        then concat('**', state, '**')
        else state
    end
from mytable
order by state, id

这篇关于MYSQL存储过程-Concat行值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！