根据表中的字段查询表中只出现过一次或n次的记录
ID Name UserID
1 水果 999
1 蔬菜 999
1 零食 333
1 饮料 999
1 干货 555
1 海鲜 999
1 肉食 777
根据表中的字段查询表中只出现过一次或n次的记录
UserID出现1次的数据
UserID出现4次的数据
请问这个条件查询语句什么写? sql 
[解决办法]
@n你自己根据需要填
----------------------------------------------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-10-17 15:34:50
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
--Dec 28 2012 20:23:12
--Copyright (c) Microsoft Corporation
--Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据:[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([ID] int,[Name] varchar(4),[UserID] int)
insert [huang]
select 1,'水果',999 union all
select 1,'蔬菜',999 union all
select 1,'零食',333 union all
select 1,'饮料',999 union ALL
select 1,'干货',555 union all
select 1,'海鲜',999 union all
select 1,'肉食',777
--------------开始查询--------------------------
DECLARE @n INT
SET @n=1
SELECT *
FROM huang
WHERE userid IN (
SELECT userid
FROM (
select userid,ROW_NUMBER()OVER(PARTITION BY userid ORDER BY id)oid
from [huang])a
GROUP BY userid
HAVING MAX(oid)=@n
)
----------------结果----------------------------
/*
*/
create table js
(ID int, Name varchar(10), UserID varchar(10))
insert into js
select 1,'水果','999' union all
select 1,'蔬菜','999' union all
select 1,'零食','333' union all
select 1,'饮料','999' union all
select 1,'干货','555' union all
select 1,'海鲜','999' union all
select 1,'肉食','777'
-- UserID出现1次的数据
select a.ID,a.name,a.UserID
from js a
inner join
(select UserID from js
group by UserID having count(1)=1) b on a.UserID=b.UserID
/*
ID name UserID
----------- ---------- ----------
1 零食 333
1 干货 555
1 肉食 777
(3 row(s) affected)
*/
-- UserID出现4次的数据
select a.ID,a.name,a.UserID
from js a
inner join
(select UserID from js
group by UserID having count(1)=4) b on a.UserID=b.UserID
/*
ID name UserID
----------- ---------- ----------
1 海鲜 999
1 饮料 999
1 水果 999
1 蔬菜 999
(4 row(s) affected)
*/