SELECT virtualLotID, MAX((CASE ITEMVALUE3 WHEN -1 THEN value END)) as VALUE, MAX((CASE ITEMVALUE3 WHEN -1 THEN name END)) as name, MAX(WORKORDER) as WORKORDER, MIN(ITEMVALUE3) as ITEMVALUE, MAX((CASE ITEMVALUE3 WHEN 1 THEN value END)) as site1, MAX((CASE ITEMVALUE3 WHEN 2 THEN value END)) as site2, ... FROM t GROUP BY virtualLotID, SUBSTRING(name,1,3)
http://blog.csdn.net/acmain_chm/article/details/4283943 MySQL交叉表 在某些数据库中有交叉表,但在MySQL中却没有这个功能,但网上看到有不少朋友想找出一个解决方法,特发贴集思广义。http://topic.csdn.net/u/20090530/23/0b782674-4b0b-4cf5-bc1a-e8914aaee5ab.html?96198现整理解法如下:数据样本: create table tx( id int primary key, c1 c...
SELECT virtualLotID,
MAX((CASE ITEMVALUE3 WHEN -1 THEN value END)) as VALUE,
MAX((CASE ITEMVALUE3 WHEN -1 THEN name END)) as name,
MAX(WORKORDER) as WORKORDER,
MIN(ITEMVALUE3) as ITEMVALUE,
MAX((CASE ITEMVALUE3 WHEN 1 THEN value END)) as site1,
MAX((CASE ITEMVALUE3 WHEN 2 THEN value END)) as site2,
...
FROM t
GROUP BY virtualLotID, SUBSTRING(name,1,3)
MySQL交叉表
在某些数据库中有交叉表,但在MySQL中却没有这个功能,但网上看到有不少朋友想找出一个解决方法,特发贴集思广义。http://topic.csdn.net/u/20090530/23/0b782674-4b0b-4cf5-bc1a-e8914aaee5ab.html?96198现整理解法如下:数据样本: create table tx( id int primary key, c1 c...