Shulou(Shulou.com)05/31 Report--

This article mainly explains "the writing of mysql crosstab". The content of the explanation is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "the writing of mysql crosstab".

Create 2 tables, a t_shuiguo fruit table and a t_supermarket supermarket table

Now I want to check the summary of fruit prices in different districts of a supermarket.

As follows: table A

Well, first of all, the fruit table can be added dynamically, and all the columns in table An are dynamic, regardless of the consideration.

Let's take a look at the static if that's the four fruits.

Then SQL can write like this (refer to some columns on the Internet)

-- static sql

01

Select ifnull (groups,'total') as groups

02

03

Sum (if (name=' Apple', prices,0)) as' Apple'

04 www.2cto.com

05

Sum (if (name=' pear', prices,0)) as' pear'

06

07

Sum (if (name=' Orange', prices,0)) as' Orange'

08

09

Sum (if (name=' Cherry', prices,0)) as' Cherry'

ten

eleven

Sum (if (name='total',prices,0)) as' totals'

twelve

thirteen

From

fourteen

fifteen

(select A.groups as groups,IFNULL (A. name and A.price) as name, sum (A.price) as prices

sixteen

seventeen

From

eighteen

nineteen

(select

twenty

twenty-one

M.groups as groups, s.name as name,m.price as price

twenty-two

twenty-three

From t_supermarket m

twenty-four

twenty-five

Inner join t_shuiguo s

twenty-six

twenty-seven

On m.shuiguo = s.id

28 www.2cto.com

twenty-nine

) A

thirty

thirty-one

Group by groups, name

thirty-two

thirty-three

With rollup

thirty-four

thirty-five

Having groups is not null

thirty-six

thirty-seven

) B

thirty-eight

thirty-nine

Group by groups

forty

forty-one

With rollup

Then what is more difficult is the dynamic need to use stored procedures.

As follows:

001

Define stored procedure Terminator

002

003

Delimiter $$

004

005

-- there is a process of deleting and then creating

006

007

Drop procedure if exists searchShuiguo $$

008

009

Create procedure searchShuiguo ()

010

011

Begin

012

013

-- define the sql front end

014

015

Declare vault 1 varchar (1000) default 'SELECTIFNULL (groups,\' total\') as groups'

016

017

-- define the sql backend

018

019

Declare vault 2 varchar (1000) default 'from (select groups,IFNULL (code,\' total\') as code, sum (A.price) as prices www.2cto.com

020

021

From (

022

023

Selectm.groups as groups, s.code as code,m.price as price

024

025

From t_supermarket m inner join t_shuiguo s on m.shuiguo = s.id

026

027

) A

028

029

Group by groups

030

031

Code with rollup having groups is not null

032

033

) B

034

035

Group by groups

036

037

With rollup'

038

039

-- define temporary parameters

040

041

Declare v_temp varchar (2000)

042

043

-- define the variables to traverse

044 www.2cto.com

045

Declare v_shuiguo varchar (100)

046

047

-- define the end variable

048

049

Declare stop int default 0

050

051

-- define a cursor to check the fruit list

052

053

Declare cur cursor for select code from t_shuiguo

054

055

-- A callback setting that was not found

056

057

Declare continue handler for not found set stop = 1

058

059

-- Vernier traversing sql strings

060

061

OPEN cur

062

063

FETCH cur INTO v_shuiguo

064 www.2cto.com

065

WHILE stop = 0

066

067

DO

068

069

If v_temp =''then

070

071

Set v_temp = CONCAT (if (code =\'', vicious shuiguo'\'')

072

073

Set vaulting 1 = CONCAT (vicious tempdas page1)) as', v_shuiguo)

074

075

Else

076

077

Set v_temp =''

078

079

Set v_temp = CONCAT (if (code =\', vicious shuiguo'\','')

080

081

Set vaulting 1 = CONCAT (vicious tempdas page1)) as', v_shuiguo)

082 www.2cto.com

083

End if

084

085

FETCH cur INTO v_shuiguo

086

087

END WHILE

088

089

CLOSE cur

090

091

Set @ v_result = CONCAT (if (code=\ 'total\', prices,0) as\ 'total\')

092

093

Set @ v_result = CONCAT (@ vaught result)

094

095

-- execute sql

096

097

Prepare stmt from @ v_result

098

099

EXECUTE stmt

one hundred

Www.2cto.com

one hundred and one

Deallocate prepare stmt

one hundred and two

one hundred and three

End $$

Oh.

Call searchShuiguo () is fine.

Thank you for your reading, the above is the content of "the writing of mysql crosstab". After the study of this article, I believe you have a deeper understanding of the writing of mysql crosstab, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.