!求一条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

即:每个大厦的同一种图片类型只有一个图片.
...全文
591 17 打赏 收藏 转发到动态 举报
写回复
用AI写文章
17 条回复
切换为时间正序
请发表友善的回复…
发表回复
gh_xiong555 2007-06-01
  • 打赏
  • 举报
回复
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
fangzhouyu 2007-06-01
  • 打赏
  • 举报
回复
mark
postform999 2007-06-01
  • 打赏
  • 举报
回复
我来复制一条正确的
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)
paoluo 2007-06-01
  • 打赏
  • 举报
回复
樓上,復制也要復制一條正確的撒。
然爷 2007-06-01
  • 打赏
  • 举报
回复
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)
paoluo 2007-06-01
  • 打赏
  • 举报
回复
哦,對,排序有問題,應該加上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
  • 打赏
  • 举报
回复
小小的修改一下paoluo的语句

因为如果在budID imgTypeID 相同的情况下要选择"Y"
云中客 2007-06-01
  • 打赏
  • 举报
回复

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)
paoluo 2007-06-01
  • 打赏
  • 举报
回复
--如果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)

jiazheng 2007-06-01
  • 打赏
  • 举报
回复
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
sunhonglei2004 2007-06-01
  • 打赏
  • 举报
回复
select budID,distinct imgID,distinct imgtypeID,defaul from tabel order by imgID
$扫地僧$ 2007-06-01
  • 打赏
  • 举报
回复
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 )
HelloWorld_001 2007-06-01
  • 打赏
  • 举报
回复
可以接点分不?



有了答案为什么还不揭贴?
  • 打赏
  • 举报
回复
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)
鱼兄正解啊!
Andy-W 2007-06-01
  • 打赏
  • 举报
回复
--借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
fa_ge 2007-06-01
  • 打赏
  • 举报
回复
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 行)

lwl0606 2007-06-01
  • 打赏
  • 举报
回复
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

34,575

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧