在MYSQL 5.1中使用@DECLARE [英] Using @DECLARE in MYSQL 5.1

问题描述

仅在MYSQL Server 5.1上使用一些sql语句 我已经问了一个问题，如何对两个表进行特定计数(请参阅这里)，但是我不能在本地的MySQL SQL Server 5.1上使用它.

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.

这是表一:测试

id|name|test_type
-------------
1|FirstUnit|1
2|FirstWeb|2
3|SecondUnit|1

第二个表:test_type

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

我最后想拥有的是:

UnitTest|WebTest
-----------------
2|1

(问题是我认为，最后一部分来自MS-SQL示例，因此它不适用于MYSQL)

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

这是我的sql语句:

--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;

我无法在最后一部分中转位我的临时结果.我收到"DECLARE"的错误

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

/* SQL错误(1064):您的SQL语法有错误；检查 与您的MySQL服务器版本相对应的手册 'DECLARE @cols AS NVARCHAR(MAX)附近使用的语法， @query AS NVARCHAR(MAX)

/* 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 ='，受影响的2行，找到2行. 4个查询中的3个的持续时间:0,000秒. */

select @cols = ' at line 2 / / 2 rows affected, 2 rows found. Duration for 3 of 4 queries: 0,000 sec. */

有人可以帮忙吗?

推荐答案

MySQL没有 pivot 函数，因此您必须使用带有CASE表达式:

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

请参见带演示的SQL提琴.

如果要转换为列的数量未知的types，则可以使用准备好的语句来生成动态SQL:

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;

请参见带有演示的SQL提琴

这篇关于在MYSQL 5.1中使用@DECLARE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！