select a.name name, (select b.fs from cs2 b where a.name=b.name and b.km='语文') 语文, (select b.fs from cs2 b where a.name=b.name and b.km='数学') 数学, (select b.fs from cs2 b where a.name=b.name and b.km='英语') 英语 from cs2 afs 分数 km 科目 name 姓名
谢谢2楼,结果有点出入,不过还是挺有用的,前面加个distinct就可以得到我想要的结果了
SELECT xm, MAX(DECODE(km,'语文',fs,0) yw, MAX(DECODE(km,'数学',fs,0) sx, MAX(DECODE(km,'英语',fs,0) yy FROM a GROUP BY xm
给你一个例子IF EXISTS(SELECT 1 FROM SYSOBJECTS WHERE ID=OBJECT_ID('tmpA') AND TYPE ='U') DROP TABLE tmpA GO CREATE TABLE tmpA ( ID INT PRIMARY KEY IDENTITY(1,1), DATE DATETIME NOT NULL, RESULT VARCHAR(10) NOT NULL ) GO INSERT INTO tmpA VALUES('2012-03-15','胜') INSERT INTO tmpA VALUES('2012-03-15','负') INSERT INTO tmpA VALUES('2012-03-15','负') INSERT INTO tmpA VALUES('2012-03-15','负')INSERT INTO tmpA VALUES('2012-08-30','负') INSERT INTO tmpA VALUES('2012-08-30','胜') INSERT INTO tmpA VALUES('2012-08-30','胜') INSERT INTO tmpA VALUES('2012-08-30','胜')SELECT * FROM tmpA SELECT DATE AS 日期 ,SUM(CASE WHEN RESULT='胜' THEN 1 ELSE 0 END) AS 胜 ,SUM(CASE WHEN RESULT='负' THEN 1 ELSE 0 END) AS 负 FROM tmpA GROUP BY DATE
(select b.fs from cs2 b where a.name=b.name and b.km='语文') 语文,
(select b.fs from cs2 b where a.name=b.name and b.km='数学') 数学,
(select b.fs from cs2 b where a.name=b.name and b.km='英语') 英语
from cs2 afs 分数 km 科目 name 姓名
谢谢2楼,结果有点出入,不过还是挺有用的,前面加个distinct就可以得到我想要的结果了
MAX(DECODE(km,'语文',fs,0) yw,
MAX(DECODE(km,'数学',fs,0) sx,
MAX(DECODE(km,'英语',fs,0) yy
FROM a
GROUP BY xm
DROP TABLE tmpA
GO
CREATE TABLE tmpA
(
ID INT PRIMARY KEY IDENTITY(1,1),
DATE DATETIME NOT NULL,
RESULT VARCHAR(10) NOT NULL
)
GO
INSERT INTO tmpA VALUES('2012-03-15','胜')
INSERT INTO tmpA VALUES('2012-03-15','负')
INSERT INTO tmpA VALUES('2012-03-15','负')
INSERT INTO tmpA VALUES('2012-03-15','负')INSERT INTO tmpA VALUES('2012-08-30','负')
INSERT INTO tmpA VALUES('2012-08-30','胜')
INSERT INTO tmpA VALUES('2012-08-30','胜')
INSERT INTO tmpA VALUES('2012-08-30','胜')SELECT * FROM tmpA
SELECT DATE AS 日期
,SUM(CASE WHEN RESULT='胜' THEN 1 ELSE 0 END) AS 胜
,SUM(CASE WHEN RESULT='负' THEN 1 ELSE 0 END) AS 负
FROM tmpA
GROUP BY DATE