呵呵,是呀,是该大方一点! CREATE TABLE #Table1( Idname int, Total int, Type varchar(20) ) INSERT INTO #Table1 SELECT 3,5,'发动机' UNION ALL SELECT 3,5,'发动机' UNION ALL SELECT 3,5,'电机' UNION ALL SELECT 4,5,'变速箱' UNION ALL SELECT 4,5,'变速箱'SELECT * FROM #Table1 SELECT Idname,SUM(A1) AS '发动机',SUM(A2) AS '电机',SUM(A3) AS '变速箱' FROM (SELECT AA.Idname, CASE AA.Type WHEN '发动机' THEN AA.Total END as A1, CASE AA.Type WHEN '电机' THEN AA.Total END as A2, CASE AA.Type WHEN '变速箱' THEN AA.Total END as A3 FROM ( SELECT Idname,Type ,sum(Total) AS Total FROM #Table1 GROUP BY Idname,Type) AA )BBGROUP BY Idname
CREATE TABLE #Table1(
Idname int,
Total int,
Type varchar(20)
)
INSERT INTO #Table1
SELECT 3,5,'发动机'
UNION ALL SELECT 3,5,'发动机'
UNION ALL SELECT 3,5,'电机'
UNION ALL SELECT 4,5,'变速箱'
UNION ALL SELECT 4,5,'变速箱'SELECT * FROM #Table1
SELECT Idname,SUM(A1) AS '发动机',SUM(A2) AS '电机',SUM(A3) AS '变速箱'
FROM
(SELECT AA.Idname,
CASE AA.Type WHEN '发动机' THEN AA.Total END as A1,
CASE AA.Type WHEN '电机' THEN AA.Total END as A2,
CASE AA.Type WHEN '变速箱' THEN AA.Total END as A3
FROM
(
SELECT Idname,Type ,sum(Total) AS Total
FROM #Table1
GROUP BY Idname,Type) AA
)BBGROUP BY Idname
在对类型进行一次group by 求出有多少类型
它可以挂接在vb,delphi,vc等開發軟件上,輸出報表.
當然它也可以單獨連接數據庫,進行報表輸出.