mysql 5.5 declare,在MYSQL 5.1中使用@DECLARE

just playing around with some sql statements on MYSQL Server 5.1

I already asked a question how to make a specific count on two tables (see here)

and I also found an answer how to transpose my result (see here) but I cant use it on my local MYSQL Server 5.1.

This is table one: test

id|name|test_type

-------------

1|FirstUnit|1

2|FirstWeb|2

3|SecondUnit|1

The second table: test_type

id|type

--------

1|UnitTest

2|WebTest

The following result will be written into "yourtable" (a temporary table)

type|amount

-----------

UnitTest|2

WebTest|1

What I want to have at the end is:

UnitTest|WebTest

-----------------

2|1

(The problem is I think, the the last part is from an MS-SQL example, so it will not work on MYSQL)

This are my sql statements:

--create a temporary table

create temporary table IF NOT EXISTS yourtable

(

test_type varchar(255),

amount varchar(255)

);

--make a selecten into the temporary table

INSERT INTO yourtable

SELECT

t.test_type ,

COUNT(*) AS amount

FROM test_types AS t

JOIN test AS te ON t.id= te.test_type

GROUP BY test_type

ORDER BY t.test_type;

--just for debugging

select * from yourtable;

-- transpose result

DECLARE @cols AS NVARCHAR(MAX),

@query AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(Type)

from yourtable

FOR XML PATH(''), TYPE

).value('.', 'NVARCHAR(MAX)')

,1,1,'')

set @query = 'SELECT ' + @cols + ' from

(

select Type, Amount,

row_number() over(partition by Type order by Type, Amount) rn

from yourtable

) x

pivot

(

max(Amount)

for Type in (' + @cols + ')

) p '

execute(@query)

--drop temporary table

drop table yourtable;

I can't run the last part where I want to transpose my temporary result. I get an error for "DECLARE"

/* SQL Error (1064): You have an error in your SQL syntax; check the

manual that corresponds to your MySQL server version for the right

syntax to use near 'DECLARE @cols AS NVARCHAR(MAX),

@query AS NVARCHAR(MAX)

select @cols = ' at line 2 / / 2 rows affected, 2 rows found.

Duration for 3 of 4 queries: 0,000 sec. */

Can anybody help?

解决方案

MySQL Does not have a pivot function so you will have to transpose the data from rows into columns using an aggregate function with a CASE expression:

select

sum(case when tt.type = 'UnitTest' then 1 else 0 end) UnitTest,

sum(case when tt.type = 'WebTest' then 1 else 0 end) WebTest

from test t

inner join test_type tt

on t.test_type = tt.id

If you are going to have an unknown number of types that you want to convert to columns, you can use a prepared statement to generate dynamic SQL:

SET @sql = NULL;

SELECT

GROUP_CONCAT(DISTINCT

CONCAT(

'sum(CASE WHEN tt.type = ''',

type,

''' THEN 1 else 0 END) AS `',

type, '`'

)

) INTO @sql

FROM test_type;

SET @sql

= CONCAT('SELECT ', @sql, '

from test t

inner join test_type tt

on t.test_type = tt.id');

PREPARE stmt FROM @sql;

EXECUTE stmt;

DEALLOCATE PREPARE stmt;