求一查询语句

[livesoft]

CREATE TABLE #a
(
	col1 VARCHAR(100),
	col2 VARCHAR(100),
	col3 FLOAT
)

GO

INSERT INTO #a
SELECT 'A001','AAA',24.5 UNION ALL
SELECT 'A001','BBB',35 UNION ALL
SELECT 'A002','AAA',45.5 UNION ALL
SELECT 'A003','CCC',30 UNION ALL
SELECT 'A003','BBB',23 UNION ALL
SELECT 'A003','DDD',54 

GO

SELECT * FROM #a

GO

SELECT 'A001','AAA',24.5,'BBB',35,NULL,NULL,59.5 UNION ALL
SELECT 'A002','AAA',45.5,NULL,NULL,NULL,NULL,45.5 UNION ALL
SELECT 'A003','CCC',30,'BBB',23,'DDD',54,107

                                                                                    
---- ---- --------------------------------------- ---- ----------- ---- ----------- ---------------------------------------
A001 AAA  24.5                                    BBB  35          NULL NULL        59.5
A002 AAA  45.5                                    NULL NULL        NULL NULL        45.5
A003 CCC  30.0                                    BBB  23          DDD  54          107.0

(3 行受影响)

查询#a表,不使用游标能不能查询出最后那种结果?查询的最后一列是#a表相同col1的col3总和

[百年树人]

可以，先合计，再联合查询一下

[中国风]

CREATE TABLE #a
(
    col1 VARCHAR(100),
    col2 VARCHAR(100),
    col3 FLOAT
)

GO

INSERT INTO #a
SELECT 'A001','AAA',24.5 UNION ALL
SELECT 'A001','BBB',35 UNION ALL
SELECT 'A002','AAA',45.5 UNION ALL
SELECT 'A003','CCC',30 UNION ALL
SELECT 'A003','BBB',23 UNION ALL
SELECT 'A003','DDD',54 

GO


declare @s nvarchar(4000)
set @s='select col1'
select @s=@s+','+quotename(Col2)+'=max(case when Col2='+quotename(Col2,'''')+' then Col3 end)' from #a group by Col2 order by Col2

EXEC(@s+',sum(col3) as sumCol3 from #a group by col1')

/*
col1	AAA	BBB	CCC	DDD	sumCol3
A001	24.5	35	NULL	NULL	59.5
A002	45.5	NULL	NULL	NULL	45.5
A003	NULL	23	30	54	107
*/

[dearbinge]

查询最后一列
SELECT COL1,SUM(COL3) FROM #A GROUP BY COL1

[livesoft]

[Quote=引用 1 楼 josy 的回复:]
可以，先合计，再联合查询一下
[/Quote]
能不能给个代码？#a表的行是不定的。

[中国风]

[Quote=引用 4 楼 livesoft 的回复:]

引用 1 楼 josy 的回复:
可以，先合计，再联合查询一下

能不能给个代码？#a表的行是不定的。
[/Quote]
看看 2樓結果是否你想要的

[中国风]

declare @s nvarchar(4000)
set @s='select col1'
select @s=@s+','+quotename(Col2)+'=sum(case when Col2='+quotename(Col2,'''')+' then Col3 end)' from #a group by Col2 order by Col2

EXEC(@s+',sum(col3) as sumCol3 from #a group by col1')

2樓最col2相同時最大的，如果是Col2相同時合計，max改了sum

[livesoft]

[Quote=引用 6 楼 roy_88 的回复:]
SQL code
declare @s nvarchar(4000)
set @s='select col1'
select @s=@s+','+quotename(Col2)+'=sum(case when Col2='+quotename(Col2,'''')+' then Col3 end)' from #a group by Col2 order by Col2

EXEC(@s……
[/Quote]

不是我想要的，我不是把col2的值换成列，我想要的是假如col1相同，那么查询出来的如果是有多少行col1相同的，就把col2和col3放在后面，假如col1值为'A001'有10笔数据，那么查出来的列就有col1+10*(col2,col3)+统计列，一共有22列。

[htl258_Tony]

CREATE TABLE #a
(
    col1 VARCHAR(100),
    col2 VARCHAR(100),
    col3 FLOAT
)

GO

INSERT INTO #a
SELECT 'A001','AAA',24.5 UNION ALL
SELECT 'A001','BBB',35 UNION ALL
SELECT 'A002','AAA',45.5 UNION ALL
SELECT 'A003','CCC',30 UNION ALL
SELECT 'A003','BBB',23 UNION ALL
SELECT 'A003','DDD',54 

GO

SELECT * FROM #a

GO

DECLARE @s VARCHAR(MAX)
SELECT @s=ISNULL(@s+',','Select col1,')
		 +' MAX(CASE rn WHEN '+LTRIM(rn)+' THEN col2 END) ['+LTRIM(rn)+'a]'
		 +',SUM(CASE rn WHEN '+LTRIM(rn)+' THEN col3 END) ['+LTRIM(rn)+'b]'
FROM (SELECT DISTINCT rn=ROW_NUMBER()OVER(PARTITION BY col1 ORDER BY GETDATE()) FROM #a) t
SET @s=@s+',SUM(col3) sumall FROM (
	SELECT rn=ROW_NUMBER()OVER(PARTITION BY col1 ORDER BY GETDATE()),*
	FROM #a) AS T GROUP BY col1'
EXEC(@s)

/*
col1	1a	1b	2a	2b	3a	3b	sumall
A001	AAA	24.5	BBB	35	NULL	NULL	59.5
A002	AAA	45.5	NULL	NULL	NULL	NULL	45.5
A003	CCC	30	BBB	23	DDD	54	107
                                                                                           CCC                                                                                                  30                     BBB                                                                                                  23                     DDD                                                                                                  54                     107
(3 行受影响)
*/

[livesoft]

[Quote=引用 8 楼 htl258 的回复:]
SQL code
CREATE TABLE #a
(
col1 VARCHAR(100),
col2 VARCHAR(100),
col3 FLOAT
)

GO

INSERT INTO #a
SELECT 'A001','AAA',24.5 UNION ALL
SELECT 'A001','BBB',35 UNION ALL
SELECT 'A……
[/Quote]

谢谢，就是想要这种结果，慢慢研究一下你代码。

[hlf1989]

CREATE TABLE #a
(
    col1 VARCHAR(100),
    col2 VARCHAR(100),
    col3 FLOAT
)
GO

INSERT INTO #a
SELECT 'A001','AAA',24.5 UNION ALL
SELECT 'A001','BBB',35 UNION ALL
SELECT 'A002','AAA',45.5 UNION ALL
SELECT 'A003','CCC',30 UNION ALL
SELECT 'A003','BBB',23 UNION ALL
SELECT 'A003','DDD',54 
GO
select row_number=row_number() over (partition by col1 order by col2),* into #b from #a order by col1,col2

declare @s nvarchar(4000)
set @s='select col1'
select @s=@s+','+quotename('col1'+ltrim(row_number))+'=max(case when row_number='+ltrim(row_number)+' then Col2 end) '
			+','+quotename('col2'+ltrim(row_number))+'=max(case when row_number='+ltrim(row_number)+' then Col3 end) '
	         
from #b group by row_number order by row_number
print @s
EXEC(@s+',sum(col3) as sumCol3 from #b group by col1')