将 mssql 查询转换为 mysql 查询 [英] convert mssql query to mysql query

问题描述

我在网上找到了以下 mssql 查询，它应该可以帮助我解决一个复杂的 mysql 查询，我已经为此苦苦挣扎了几天.

I have the following mssql query that I found on the net that is supposed to help me with a complex mysql query that I have been struggling with for a few days now.

SELECT
    inv.typeID AS typeID,
    inv.typeName AS typeName,
    invGroups.groupName AS groupName,
    inv.published AS published,
    inv.description AS description,
    rankVal.valueFloat AS rank,
    replace (( SELECT skills.attributeName AS [data()]
      FROM dgmTypeAttributes tattr  -- Link between skillbook and attributes
      INNER JOIN dgmAttributeTypes skills ON (skills.attributeID = tattr.valueInt)
      WHERE (tattr.typeID = inv.typeID)
        AND (tattr.attributeID IN (180, 181)) -- Primary and secondary attributes
      ORDER BY inv.typeID FOR xml path('')), ' ', ',') AS prisec,
    replace (( SELECT RTRIM(CAST(inv2.typeID AS varchar)) + ',' AS [data()]
      FROM (SELECT * FROM dgmTypeAttributes WHERE (attributeID in (182, 183, 184)) -- Pre-req skills 1, 2, and 3
        AND (typeID = inv.typeID)) tattr2 
      INNER JOIN invTypes inv2 ON (tattr2.valueInt = inv2.typeID)
      ORDER BY inv.typeID FOR xml path('')), ' ', ' ') AS prereq,
    replace (( SELECT RTRIM(CAST(tattr2.valueInt AS varchar)) + ',' AS [data()]
      FROM (SELECT * FROM dgmTypeAttributes WHERE (attributeID in (277, 278, 279)) AND (typeID = inv.typeID)) tattr2  -- Link between skillbook and attributes
      ORDER BY inv.typeID FOR xml path('')), ' ', ' ') AS prereqlvl
FROM invTypes inv
INNER JOIN invGroups ON (inv.groupID = invGroups.groupID)
INNER JOIN dgmTypeAttributes rankVal ON (inv.typeID = rankVal.typeID)
WHERE invGroups.categoryID = 16 -- Skillbooks category    
    AND rankVal.attributeID = 275 -- Skill rank attribute
    AND inv.published = 1
GROUP BY inv.typeID, inv.typeName, invGroups.groupName, inv.published, inv.description, rankVal.valueFloat
ORDER BY invGroups.groupName, inv.typeName

我对 mysql 很满意，但我对 mssql 一无所知.有人可以推荐一种转换此查询的好方法吗?我不希望有人为我转换它，因为那会要求太多，但是一些可以将我指向仪式方向的建议(除了学习 mssql lolz)会非常好.感谢您的时间和耐心.

I am so so with mysql but I know nothing of mssql. Can somebody recommend a good method of converting this query that is low or now cost? I do not expect somebody to convert it for me as that would be asking too much, but some suggestions that would point me in the rite direction (aside from learning mssql lolz) would be very nice. Thank you for your time and patience.

推荐答案

那些带有 FOR XML PATH('') 的子查询似乎是用来连接字符串的1.看看是否可以用 MySQL 中的 GROUP_CONCAT 替换它们.其他位似乎是标准 SQL.

Those subqueries with FOR XML PATH('') seem to be used to concatenate strings1. See if you can replace them with GROUP_CONCAT in MySQL. The other bits seem to be standard SQL.

这篇关于将 mssql 查询转换为 mysql 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！