56,679
社区成员
发帖
与我相关
我的任务
分享
USE tempdb
GO
IF OBJECT_ID('t') IS NOT NULL DROP TABLE t
GO
CREATE TABLE t(
id INT PRIMARY KEY,
q1 INT NOT NULL,
m1 DECIMAL(10,2) NOT NULL,
q2 INT,
m2 DECIMAL(10,2),
q3 INT,
m3 DECIMAL(10,2),
q4 INT,
m4 DECIMAL(10,2)
)
GO
SET NOCOUNT ON
INSERT INTO t
SELECT 1 ,10001,8.8,10002,3.8,10007,1.8,10003,1.3
UNION ALL SELECT 2 ,10005,1.3,10002,6.6,NULL,NULL,NULL,null
UNION ALL SELECT 3 ,10002,6.5,10004,3.2,10003,7.2,NULL,null
UNION ALL SELECT 4 ,10004,3.9,10005,5.4,NULL,NULL,NULL,null
UNION ALL SELECT 5 ,10007,2.9,NULL,NULL,10004,2.3,NULL,null
UNION ALL SELECT 6 ,10009,4.2,10001,3.7,NULL,NULL,NULL,NULL
UNION ALL SELECT 7 ,10004,6.7,NULL,NULL,NULL,NULL,NULL,NULL
UNION ALL SELECT 8 ,10002,3.8,NULL,NULL,NULL,NULL,NULL,NULL
---- 以上为测试数据 ------
SELECT
q
,SUM(m) AS m
FROM (
SELECT q1 AS q,m1 AS m FROM t
UNION ALL
SELECT q2,m2 FROM t
UNION ALL
SELECT q3,m3 FROM t
UNION ALL
SELECT q4,m4 FROM t
) AS tt
WHERE q IS NOT NULL AND m IS NOT NULL
GROUP BY q
ORDER BY m DESC
/*
q m
----------- -------
10002 20.70
10004 16.10
10001 12.50
10003 8.50
10005 6.70
10007 4.70
10009 4.20
*/