您的位置：程序门 -> ms-sql server -> 基础类

！求一条sql,立马结帖！

[收藏此页] [打印本页]选择字色：背景色：字体：[大][中][小]

！求一条sql,立马结帖！[已结贴,结贴人:zine_alone]

发表于：2007-06-01 09:58:34 楼主

1. budid为大厦id,该表中会出现重复
2. imgid为大厦对应的图片id，该表中也会重复（2个大厦同用一个图片的时候）.
3.imgtypeid 大厦图片类型id（重复)
4.default_yn 大厦图片的是否为默认（即.同一个大厦的同种类型的图片有多个时，优先取默认为y的，如果都是n,则取第一条).

budid imgid imgtypeid default_yn
1 2 1 n
1 3 1 y
1 4 2 n
2 5 2 n
2 6 1 y

期望的结果是:
budid imgid imgtypeid default_yn
1 3 1 y
1 4 2 n
2 5 2 n
2 6 1 y

即:每个大厦的同一种图片类型只有一个图片.

发表于：2007-06-01 10:07:561楼得分:30

create table test
（
budid int,
imgid int,
imgtypeid int,
default_yn varchar（2)
)

insert test select 1,2,1, 'n '
insert test select 1,3,1, 'y '
insert test select 1,4,2, 'n '
insert test select 2,5,2, 'n '
insert test select 2,6,1, 'y '

select t.*
from test t
where cast（t.budid as varchar) + cast（t.imgid as varchar) + cast（t.imgtypeid as varchar) in
（select top 1 cast（budid as varchar) + cast（imgid as varchar) + cast（imgtypeid as varchar) from test where t.budid=budid and t.imgtypeid=imgtypeid order by default_yn desc )

发表于：2007-06-01 10:11:492楼得分:2

select budid,distinct imgid,distinct imgtypeid,defaul from tabel order by imgid

发表于：2007-06-01 10:44:563楼得分:2

select t.* from test t inner join
（select budid,imgtypeid,max（default_yn) default_yn from test
group by budid,imgtypeid) v
on t.budid=v.budid and t.imgtypeid=v.imgtypeid and t.default_yn=v.default_yn
order by t.budid,t.imgtypeid

发表于：2007-06-01 10:50:354楼得分:20

--如果imgid不會重復的話，可以這麼寫
select * from tablename a
where imgid in （select top 1 imgid from tablename where budid = a.budid and imgtypeid = a.imgtypeid order by default_yn, imgid)

发表于：2007-06-01 10:59:125楼得分:20

select * from test a
where imgid in （select top 1 imgid from test where budid = a.budid and imgtypeid = a.imgtypeid order by default_yn desc, imgid)

发表于：2007-06-01 11:00:306楼得分:0

小小的修改一下paoluo的语句

因为如果在budid imgtypeid 相同的情况下要选择 "y "

发表于：2007-06-01 11:07:367楼得分:0

哦，對，排序有問題，應該加上desc。

--如果imgid不會重復的話，可以這麼寫
select * from tablename a
where imgid in （select top 1 imgid from tablename where budid = a.budid and imgtypeid = a.imgtypeid order by default_yn desc, imgid)

发表于：2007-06-01 11:21:468楼得分:2

select * from tablename a
where imgid in （selectt top 1 imgid from tablename where budid = a.budid and imgtypeid = a.imgtypeid order by default_yn, imgid)

发表于：2007-06-01 11:23:529楼得分:0

樓上，復制也要復制一條正確的撒。

发表于：2007-06-01 11:33:5110楼得分:2

我来复制一条正确的
select * from tablename a
where imgid in （select top 1 imgid from tablename where budid = a.budid and imgtypeid = a.imgtypeid order by default_yn desc, imgid)

发表于：2007-06-01 11:48:0511楼得分:0

mark

发表于：2007-06-01 12:18:2912楼得分:10

select *
from tabel a
where exsits（select * from table b where a.budid=b.budid and a.imgid=b.imgid and a.imgtypeid <> b.imgtypeid)
order by imgid

发表于：2007-06-01 13:05:4013楼得分:5

create table da （ budid nvarchar（10) , imgid nvarchar（10), imgtypeid nvarchar（10), default_yn nvarchar（10))
insert into da select 1 , 2 , 1 , 'n '
insert into da select 1 , 3 , 1 , 'y '
insert into da select 1 , 4 , 2 , 'n '
insert into da select 2 , 5 , 2 , 'n '
insert into da select 2 , 6 , 1 , 'y '

select a.*
from da a inner join （
select budid , imgtypeid ,min（case when default_yn= 'y ' then 0 else imgid end ) as imgid
from da
group by budid,imgtypeid) b
on a.budid=b.budid and a.imgtypeid=b.imgtypeid and （case when a.default_yn= 'y ' then 0 else a.imgid end)=b.imgid

--result
1 3 1 y
1 4 2 n
2 6 1 y
2 5 2 n

发表于：2007-06-01 13:31:1214楼得分:2

select * from test a where not exists（select 1 from test where a.budid=budid
and a.default_yn=default_yn and a.imgid <imgid )
budid imgid imgtypeid default_yn
----------- ----------- ----------- ----------
1 3 1 y
1 4 2 n
2 5 2 n
2 6 1 y

（所影响的行数为 4 行）

发表于：2007-06-01 13:36:5915楼得分:5

--借1楼的表数据：
create table test
（
budid int,
imgid int,
imgtypeid int,
default_yn varchar（2)
)
insert test select 1,2,1, 'n '
insert test select 1,3,1, 'y '
insert test select 1,4,2, 'n '
insert test select 2,5,2, 'n '
insert test select 2,6,1, 'y '

select * from test as a
where not exists（select 1 from test as b where b.budid=a.budid and b.imgtypeid=a.imgtypeid and b.default_yn> a.default_yn)
/*
说明：
b.default_yn> a.default_yn
在字母比较中，y> n ,根据“优先取默认为y的，如果都是n,则取第一条”

budid imgid imgtypeid default_yn
1 3 1 y
1 4 2 n
2 5 2 n
2 6 1 y
*/

drop table test

发表于：2007-06-01 13:55:5416楼得分:0

select * from tablename a
where imgid in （select top 1 imgid from tablename where budid = a.budid and imgtypeid = a.imgtypeid order by default_yn desc, imgid)
鱼兄正解啊！

发表于：2007-06-01 15:06:1717楼得分:0

可以接点分不?

有了答案为什么还不揭贴?