求一道SQL题解法,头大了

大地主刘发财 2015-11-22 03:00:19
...全文
157 点赞 收藏 6
写回复
6 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
venjianX 2015-11-24
引用 5 楼 Modeala 的回复:
SELECT
	* INTO TN
FROM (SELECT
	T1.AID,
	T1.A,
	T1.B,
	SUM(T1.C1) AS C_C1,
	SUM(T1.D1) AS C_D1,
	SUM(T1.E1) AS C_E1,
	SUM(T1.F1) AS C_F1
FROM ((SELECT
	ID AS AID,
	A,
	B,
	0 AS C1,
	0 AS D1,
	0 AS E1,
	0 AS F1
FROM TT) UNION (SELECT
	AID,
	A,
	B,
	SUM(C) AS C1,
	SUM(D) AS D1,
	SUM(E) AS E1,
	SUM(F) AS F1
FROM TA
GROUP BY	AID,
			A,
			B))
AS T1
GROUP BY	T1.AID,
			T1.A,
			T1.B)
T
补充TT是主表,TA是副表1。生成的新表为TN。 思路主要是先对子表做sum运算,再做union联合查询,再使用select into 完成。
回复
venjianX 2015-11-24
SELECT
	* INTO TN
FROM (SELECT
	T1.AID,
	T1.A,
	T1.B,
	SUM(T1.C1) AS C_C1,
	SUM(T1.D1) AS C_D1,
	SUM(T1.E1) AS C_E1,
	SUM(T1.F1) AS C_F1
FROM ((SELECT
	ID AS AID,
	A,
	B,
	0 AS C1,
	0 AS D1,
	0 AS E1,
	0 AS F1
FROM TT) UNION (SELECT
	AID,
	A,
	B,
	SUM(C) AS C1,
	SUM(D) AS D1,
	SUM(E) AS E1,
	SUM(F) AS F1
FROM TA
GROUP BY	AID,
			A,
			B))
AS T1
GROUP BY	T1.AID,
			T1.A,
			T1.B)
T
回复
yooq_csdn 2015-11-23
去年好像见过这道题,楼主搜索一下
回复
上面说错 条件是 AID,A,B完全相同
回复
就是说形成一个新表 新表的行以主表为基础. 副表1和副表2的D,E,F列变成新表的 C_D1,C_E1,C_F1,C_D2,C_E2,C_F2列 其中的数据因为条件相同的有两行(副表的AID和A列),所以要将两行合并,D,E,F的值是汇总的,然后再写到新表中。 我研究了一下,先group by把两副表的数据提出来 然后update select.但效率低,人家说不行,要求是能应用到至少百万级的库中...我实在想不出还有什么办法了..
回复
shoppo0505 2015-11-22
没看懂题目,等大神。
回复
相关推荐
发帖
疑难问题
创建于2007-09-28

2.1w+

社区成员

MS-SQL Server 疑难问题
申请成为版主
帖子事件
创建了帖子
2015-11-22 03:00
社区公告
暂无公告