select * from HotelSearch where Fs_Province='2303' and Fi_HotelID not in ( select Fi_HotelID from HotelSearch where Fs_Province='2303' group by Fi_HotelID having count(1)>1)
select * from HotelSearch where Fs_Province='2303' group by Fi_HotelID,其它字段 having count(*)=1
select Fi_HotelID from (select * from HotelSearch where Fs_Province='2303') as tt group by Fi_HotelID having count(1)=1 select * from (select * from HotelSearch where Fs_Province='2303')tt where tt.Fi_HotelID in (select Fi_HotelID from (select * from HotelSearch where Fs_Province='2303') as tt group by Fi_HotelID having count(1)=1 )
对不起刚才发错了select * from (select * from HotelSearch where Fs_Province='2303')tt where tt.Fi_HotelID in (select Fi_HotelID from (select * from HotelSearch where Fs_Province='2303') as tt group by Fi_HotelID having count(1)=1 )
select distinct Fi_HotelID ,* from HotelSearch where Fs_Province='2303'
--创建临时表 SELECT * INTO dbo.#xx FROM HotelSearch WHERE 1=2--定义游标 DECLARE @Fi_HotelID nvarchar(20)DECLARE CURSOR xxxx AS SELECT DISTINCT Fi_HotelID FROM HotelSearch WHERE Fs_Province='2303'OPEN CURSOR xxxxFETCH next INTO @Fi_HotelID--对每个Fi_HotelID,只插入第一笔记录到临时表中 WHILE @@CURSOR=0 BEGIN INSERT INTO #xx SELECT top 1 * FROM HotelSearch WHERE Fs_Province='2303' AND Fi_HotelID=@Fi_HotelID
FETCH next INTO @Fi_HotelID END--把临时表的结果返回 SELECT * FROM #xx
and Fi_HotelID not in (
select Fi_HotelID from HotelSearch
where Fs_Province='2303'
group by Fi_HotelID having count(1)>1)
from HotelSearch
where Fs_Province='2303'
group by Fi_HotelID,其它字段
having count(*)=1
select * from (select * from HotelSearch where Fs_Province='2303')tt where
tt.Fi_HotelID in (select Fi_HotelID from (select * from HotelSearch where Fs_Province='2303') as tt group by Fi_HotelID having count(1)=1 )
tt.Fi_HotelID in (select Fi_HotelID from (select * from HotelSearch where Fs_Province='2303') as tt group by Fi_HotelID having count(1)=1 )
SELECT *
INTO dbo.#xx
FROM HotelSearch
WHERE 1=2--定义游标
DECLARE @Fi_HotelID nvarchar(20)DECLARE CURSOR xxxx
AS
SELECT DISTINCT Fi_HotelID
FROM HotelSearch
WHERE Fs_Province='2303'OPEN CURSOR xxxxFETCH next INTO @Fi_HotelID--对每个Fi_HotelID,只插入第一笔记录到临时表中
WHILE @@CURSOR=0
BEGIN
INSERT INTO #xx
SELECT top 1 *
FROM HotelSearch
WHERE Fs_Province='2303'
AND Fi_HotelID=@Fi_HotelID
FETCH next INTO @Fi_HotelID
END--把临时表的结果返回
SELECT * FROM #xx