！求一条SQL,立马结帖！
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
即:每个大厦的同一种图片类型只有一个图片.
[解决办法]
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 )
[解决办法]
select budID,distinct imgID,distinct imgtypeID,defaul from tabel order by imgID
[解决办法]
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
[解决办法]
--如果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)
[解决办法]
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)
[解决办法]
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)
[解决办法]
我来复制一条正确的
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)
[解决办法]
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
[解决办法]
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
131Y
142N
261Y
252N
[解决办法]
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 行）
[解决办法]
--借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
