超难度SQL问题

hushu02 2009-09-01 11:01:03

有表
file ID
---------------
a 1
b 1
c 2

怎么得到

id tfile1 file2
1 a b
2 c

或者有没有办法得到
id file
--------------
1 a
2 c
或者
id testfile
--------------
1 b
2 c
也就是一个Id只取一条记录

...全文

81 8 打赏收藏转发到动态举报

写回复

用AI写文章

8 条回复

切换为时间正序

请发表友善的回复…

发表回复

linguojin11 2009-09-01

打赏
举报

FFFF

hushu02 2009-09-01

打赏
举报

马上揭帖高手真他妈多

--小F-- 2009-09-01

打赏
举报

----------------------------------------------------------------

-- Author  :fredrickhu(小F，向高手学习)

-- Date    :2009-09-01 23:03:16

-- Verstion:

--      Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) 

--	Nov 24 2008 13:01:59 

--	Copyright (c) 1988-2005 Microsoft Corporation

--	Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)

--

----------------------------------------------------------------

--> 测试数据：[tb]

if object_id('[tb]') is not null drop table [tb]

go 

create table [tb]([file] varchar(1),[ID] int)

insert [tb]

select 'a',1 union all

select 'b',1 union all

select 'c',2

--------------开始查询--------------------------

select id,max([file]) as testfile from tb group by id



select id,[file] from tb t where not exists(select 1 from tb where id=t.id and [file]>t.[file])

----------------结果----------------------------

/*id          testfile

----------- --------

1           b

2           c



(2 行受影响) 

*/

xiaoliaoyun 2009-09-01

打赏
举报



create table #a([file] VARCHAR(10),[ID] INT)

insert #a select 'a',1   union all 

select 'b',1   union all 

select 'c',2

select * from #a 





DECLARE @s VARCHAR(8000)

SELECT @s = 'SELECT [ID]'

SELECT @s = @s + ',MAX(CASE WHEN sequence = ''' + RTRIM(sequence) + ''' THEN [file] END) AS ' + 'file' + RTRIM(sequence)

  FROM (SELECT DISTINCT sequence FROM (SELECT *,ROW_NUMBER()OVER(PARTITION BY [ID] ORDER BY GETDATE()) AS sequence FROM #a)X)Y

SELECT @s = @s + ' FROM (SELECT *,ROW_NUMBER()OVER(PARTITION BY [ID] ORDER BY GETDATE()) AS sequence FROM #a)X GROUP BY [ID]'

EXEC (@s)

htl258_Tony 2009-09-01

打赏
举报

select * from tb t where not exists(select 1 from tb where id=t.id and file>t.file)

--小F-- 2009-09-01

打赏
举报

----------------------------------------------------------------

-- Author  :fredrickhu(小F，向高手学习)

-- Date    :2009-09-01 23:03:16

-- Verstion:

--      Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) 

--	Nov 24 2008 13:01:59 

--	Copyright (c) 1988-2005 Microsoft Corporation

--	Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)

--

----------------------------------------------------------------

--> 测试数据：[tb]

if object_id('[tb]') is not null drop table [tb]

go 

create table [tb]([file] varchar(1),[ID] int)

insert [tb]

select 'a',1 union all

select 'b',1 union all

select 'c',2

--------------开始查询--------------------------

select id,max([file]) as testfile from tb group by id

----------------结果----------------------------

/*id          testfile

----------- --------

1           b

2           c



(2 行受影响) 

*/