仅仅在MYSQL Server5.1上处理一些sql语句时,我已经问了一个问题,如何在两个表上进行特定计数,我也找到了一个如何对结果进行转置的答案,但是我不能在它上使用它我的本地MYSQLServer 5.1。
这是表一:测试
id|name|test_type ------------- 1|FirstUnit|1 2|FirstWeb|2 3|SecondUnit|1
第二张表:test_type
id|type -------- 1|UnitTest 2|WebTest
以下结果将写入“您的表”(临时表)中
type|amount ----------- UnitTest|2 WebTest|1
最后我想拥有的是:
UnitTest|WebTest ----------------- 2|1
(问题是我认为,最后一部分来自MS-SQL示例,因此它不适用于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”的错误
/ * SQL错误(1064):您的SQL语法有错误;检查与您的MySQL服务器版本相对应的手册以获取正确的语法,以在’DECLARE @cols AS NVARCHAR(MAX),@query AS NVARCHAR(MAX)附近使用 在线路选择@cols =‘2 / / 2行的影响,2行找到。4个查询中的3个的持续时间:0,000秒。* /
/ * SQL错误(1064):您的SQL语法有错误;检查与您的MySQL服务器版本相对应的手册以获取正确的语法,以在’DECLARE @cols AS NVARCHAR(MAX),@query AS NVARCHAR(MAX)附近使用
在线路选择@cols =‘2 / / 2行的影响,2行找到。4个查询中的3个的持续时间:0,000秒。* /
有人可以帮忙吗?
MySQL没有 枢轴 函数,因此您必须使用带有CASE表达式的聚合函数将行中的数据转置为列:
CASE
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 Fiddle。
如果types要转换为列的数量未知,则可以使用准备好的语句来生成动态SQL:
types
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 Fiddle