我有一个表my_table,它有状态、月份、ID和sales列。
我的目标是将具有相同状态、月份、ID的不同行合并为一行,同时将这些选定行的sales列加到合并行中。
例如:
state month ID sales
-------------------------------
FL June 0001 12,000
FL June 0001 6,000
FL June 0001 3,000
FL July 0001 6,000
FL July 0001 4,000
TX January 0050 1,000
MI April 0032 5,000
MI April 0032 8,000
CA April 0032 2,000这是我应该得到的
state month ID sales
-------------------------------
FL June 0001 21,000
FL July 0001 10,000
TX January 0050 1,000
CA,MI April 0032 15,000发布于 2013-09-20 06:24:06
为此,您需要使用GROUP_CONCAT:
SELECT GROUP_CONCAT(DISTINCT State) AS State
, Month, ID, SUM(Sales)
FROM Table1
GROUP BY Month, ID;请参阅这个SQLFiddle
更新(用于Server)
对于Server,可以使用STUFF():
SELECT state =
STUFF((SELECT DISTINCT ' , ' + state
FROM Table1 b
WHERE b.ID = a.ID
AND b.month = a.month
FOR XML PATH('')), 1, 2, '')
,month, ID, SUM(sales) AS Sales
FROM Table1 a
GROUP BY month,ID;请参阅这个SQLFiddle
发布于 2013-09-20 06:26:26
请试试这个:
SELECT B.STATE, A.ID, SUM(A.SALES) AS SALES
FROM MYTABLE A
INNER JOIN
(SELECT DISTINCT
CASE WHEN A.state = B.STATE THEN A.STATE ELSE A.STATE+','+B.STATE END AS STATE,
ID
FROM MYTABLE A
INNER JOIN MYTABLE B ON A.ID = B.ID) B
ON A.STATE = B.STATE
GROUP BY B.state, A.month, A.ID如果它的查询需要纠正,请告诉我。
发布于 2013-09-20 06:02:41
实际上,您想要做的是分组,而不是合并行。在聚合不同的值时,用公共值对几行进行分组。
使用Group子句实现上述目标:
select state,month,ID sum(sales)
from my_table
group by
state, month, ID要阅读有关组的更多信息,请参见此处:groupby.asp
https://stackoverflow.com/questions/18909855
复制相似问题