首页 新闻 会员 周边

sql 查询

0
悬赏园豆：10 [已解决问题] 解决于 2012-09-24 14:53

我想查出code唯一的一个集合

比如：

create table test 
(
  [MediaCode] nvarchar(50)
 ,[MediaName] nvarchar(200)
 ,[SpotStartDate] datetime
,[SpotStartTime] nchar(6)
 
)
insert into test values('media1','mediaName1','2008-01-01 00:00:00.000','203609')
insert into test values('media1','mediaName2','2008-01-01 00:00:00.000','214016')
insert into test values('media2','mediaName2','2008-01-01 00:00:00.000','214017')
--需要的结果是
media1， mediaName2
media2， mediaName2
--子查询是这样的
SELECT TOP 1 C.[MediaCode],C.[MediaName] 
                FROM [MatrixDB_202_LoadData].dbo.test AS C 
                WHERE C.[MediaCode] IN 
                                    (SELECT D.[MediaCode] 
                                     FROM [MatrixDB_202_LoadData].dbo.test AS D
                                     WHERE C.[MediaName] <> D.[MediaName])
                ORDER BY C.SpotStartDate,C.SpotStartTime DESC

我不知道怎么把子查询筛出来的media1 和已经是唯一的media2连起来 ？

laura_lichanghong的主页 laura_lichanghong | 初学一级 | 园豆：116
提问于：2012-09-24 12:49
< >
分享
最佳答案
0

select distinct a.MediaCode ,
(select top 1 t.[MediaName] from test t where t.MediaCode=a.MediaCode order by [MediaName] desc)
from test a

收获园豆：10
gy_51Testing | 初学一级 |园豆：190 | 2012-09-24 13:45

gy_51Testing | 园豆：190 (初学一级) | 2012-09-24 13:47
其他回答(1)
0

DECLARE @test table
(
[MediaCode] nvarchar(50)
,[MediaName] nvarchar(200)
,[SpotStartDate] datetime
,[SpotStartTime] nchar(6)

)
insert into @test values('media1','mediaName1','2008-01-01 00:00:00.000','203609')
insert into @test values('media1','mediaName2','2008-01-01 00:00:00.000','214016')
insert into @test values('media2','mediaName2','2008-01-01 00:00:00.000','214017')
--需要的结果是
--media1， mediaName2
--media2， mediaName2
--子查询是这样的
SELECT C.[MediaCode],C.[MediaName]
FROM @test AS C
WHERE C.[MediaCode] IN
(SELECT D.[MediaCode]
FROM @test AS D
WHERE C.[MediaName] <> D.[MediaName])
ORDER BY C.SpotStartDate,C.SpotStartTime DESC

 

测试你的SQL是可以的

 

MediaCode MediaName
media1 mediaName2
media1 mediaName1

soulkiss | 园豆：215 (菜鸟二级) | 2012-09-24 13:00

可能我没有表达清楚。

insert into test values('media1','mediaName1','2008-01-01 00:00:00.000','203609')
insert into test values('media1','mediaName2','2008-01-01 00:00:00.000','214016')
insert into test values('media2','mediaName2','2008-01-01 00:00:00.000','172900')
insert into test values('media3','mediaName3','2008-01-01 00:00:00.000','175925')
insert into test values('media4','mediaName4','2008-01-01 00:00:00.000','171555')
insert into test values('media4','mediaName5','2008-01-01 00:00:00.000','172850')

--需要结果是
media1  mediaName2
media2  mediaName2
media3  mediaName3
media4  mediaName5

我用UNION 链接 也不对的。union 中不能有再order中出现的字段但是不在select 中的。而且我现在加不上media4这种

 

  SELECT DISTINCT(E.[MediaCode])
        ,E.[MediaName]
        ,E.SpotStartDate
        ,E.SpotStartTime
     FROM [MatrixDB_202_LoadData].dbo.test AS E
     WHERE E.[MediaCode] IS NOT NULL
     AND E.[MediaName] IS NOT NULL
     AND E.[MediaCode] NOT IN (SELECT F.[MediaCode] 
                                     FROM [MatrixDB_202_LoadData].dbo.test AS F
                                     WHERE E.[MediaName] <> F.[MediaName])                         
    UNION ALL
    SELECT TOP 1 C.[MediaCode]
                 ,C.[MediaName]
                 ,C.SpotStartDate
                 ,C.SpotStartTime 
                FROM [MatrixDB_202_LoadData].dbo.test AS C 
                WHERE C.[MediaCode] IN 
                                    (SELECT D.[MediaCode] 
                                     FROM [MatrixDB_202_LoadData].dbo.test AS D
                                     WHERE C.[MediaName] <> D.[MediaName])
支持(0) 反对(0) laura_lichanghong | 园豆：116 (初学一级) | 2012-09-24 13:13
清除回答草稿
   您需要登录以后才能回答，未注册用户请先注册。