22,209
社区成员
发帖
与我相关
我的任务
分享
SELECT
T1.code,
T2.name AS name1,
T2.value AS value1,
T3.name AS name2,
T3.value AS value2
FROM
(
SELECT code,MIN(ID) AS ID1,CASE COUNT(code) WHEN 1 THEN NULL ELSE MAX(ID) END AS ID2 FROM T GROUP BY code
) T1
LEFT JOIN T T2 ON T1.ID1 = T2.ID
LEFT JOIN T T3 ON T1.ID2 = T3.ID
CREATE TABLE # (ID INT, code INT, NAME CHAR(1), VALUE VARCHAR(10))
INSERT INTO # VALUES
(1,2014000,'A','10'),
(2,2014000,'B','9'),
(3,2014001,'C','100'),
(4,2014002,'D','4'),
(5,2014002,'E','5'),
(6,2014003,'F','9'),
(7,2014003,'G','10')
SELECT
code,MIN(name) AS name1,MIN(CAST(REPLACE(value,' ','') AS INT)) AS value1,
CASE COUNT(ID) WHEN 1 THEN NULL ELSE MAX(name) END AS name2,
CASE COUNT(ID) WHEN 1 THEN NULL ELSE MAX(CAST(REPLACE(value,' ','') AS INT)) END AS value2
FROM
#
GROUP BY
code