--> 生成测试数据: @mobilefun DECLARE @mobilefun TABLE (mobileid INT,funid INT) INSERT INTO @mobilefun SELECT 6,2 UNION ALL SELECT 6,3 UNION ALL SELECT 7,2 UNION ALL SELECT 8,3--SQL查询如下:SELECT mobileid FROM @mobilefun WHERE funid=2 OR funid=3 GROUP BY mobileid HAVING COUNT(*)>=2/* mobileid ----------- 6(1 行受影响)*/
select * from (select * from mobilefun where funid=2) a, (select * from mobilefun where funid=3) b where a.mobileid=b.mobileid
谢谢,但是 funid=2 OR funid=3 是不定的,也有可能是 funid=2 OR funid=3 or funid=4 这样怎么写?
if object_id('[tb]') is not null drop table [tb] go create table [tb]([mobileid] int,[funid] int) insert [tb] select 6,2 union all select 6,3 union all select 7,2 union all select 8,3select * from tb t where exists(select 1 from tb where [mobileid]=t.[mobileid] and [funid]<>t.[funid]) and funid in(2,3) /* mobileid funid ----------- ----------- 6 2 6 3(2 行受影响) */
select mobileid from tb where funid in(2,3) group by mobileid having count(distinct funid)>=2
if object_id('[tb]') is not null drop table [tb] go create table [tb]([mobileid] int,[funid] int) insert [tb] select 6,2 union all select 6,3 union all select 6,4 union all select 7,2 union all select 8,3select * from tb t where exists(select 1 from tb where [mobileid]=t.[mobileid] and [funid]<>t.[funid]) and funid in(2,3,4) /* mobileid funid ----------- ----------- 6 2 6 3 6 4(3 行受影响) */
select mobileid from tb where funid in(2,3,4) --或者funid=2 OR funid=3 or funid=4 group by mobileid having count(distinct funid)>=2
会不会出现mobileid funid 6 2 6 2 这样的数据?
create table #MM ( mobileid int, funid int ) insert into #MM select 6,2 union all select 6,3 union all select 7,2 union all select 8,3select * from #MM M where exists(select * from #MM where mobileid=M.mobileid and funid<>M.funid)mobileid funid ----------- ----------- 6 2 6 3(2 行受影响)
create table #MM ( mobileid int, funid int ) insert into #MM select 6,2 union all select 6,3 union all select 7,2 union all select 8,3select * from #MM where mobileid in(select mobileid from #MM M where exists(select * from #MM where mobileid=M.mobileid and funid<>M.funid) and funid=2 or funid=3 group by mobileid having count(*)>1 ) mobileid funid ----------- ----------- 6 2 6 3(2 行受影响)
select a.* from (select * from mobilefun where funid=2) a, (select * from mobilefun where funid=3) b where a.mobileid=b.mobileid
最终解决方式CREATE PROC MobileFun_GetBySql@sql NVARCHAR(100), --条件 @num INT --参数个数AS ... @sql='SELECT mobileid FROM mobilefun WHERE ' + @sql + ' GROUP BY mobileid HAVING COUNT(*)>=' + @num ...GO
FROM mobilefun
WHERE funid=2 OR funid=3
GROUP BY mobileid
HAVING COUNT(*)>=2
-- Author: liangCK 小梁
---------------------------------
--> 生成测试数据: @mobilefun
DECLARE @mobilefun TABLE (mobileid INT,funid INT)
INSERT INTO @mobilefun
SELECT 6,2 UNION ALL
SELECT 6,3 UNION ALL
SELECT 7,2 UNION ALL
SELECT 8,3--SQL查询如下:SELECT mobileid
FROM @mobilefun
WHERE funid=2 OR funid=3
GROUP BY mobileid
HAVING COUNT(*)>=2/*
mobileid
-----------
6(1 行受影响)*/
select * from
(select * from mobilefun where funid=2) a,
(select * from mobilefun where funid=3) b
where a.mobileid=b.mobileid
funid=2 OR funid=3
是不定的,也有可能是
funid=2 OR funid=3 or funid=4 这样怎么写?
go
create table [tb]([mobileid] int,[funid] int)
insert [tb] select 6,2
union all select 6,3
union all select 7,2
union all select 8,3select *
from tb t
where exists(select 1 from tb where [mobileid]=t.[mobileid] and [funid]<>t.[funid])
and funid in(2,3)
/*
mobileid funid
----------- -----------
6 2
6 3(2 行受影响)
*/
mobileid
from
tb
where
funid in(2,3)
group by
mobileid
having count(distinct funid)>=2
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([mobileid] int,[funid] int)
insert [tb] select 6,2
union all select 6,3
union all select 6,4
union all select 7,2
union all select 8,3select *
from tb t
where exists(select 1 from tb where [mobileid]=t.[mobileid] and [funid]<>t.[funid])
and funid in(2,3,4)
/*
mobileid funid
----------- -----------
6 2
6 3
6 4(3 行受影响)
*/
select
mobileid
from
tb
where
funid in(2,3,4) --或者funid=2 OR funid=3 or funid=4
group by
mobileid
having
count(distinct funid)>=2
6 2
6 2 这样的数据?
(
mobileid int,
funid int
)
insert into #MM select 6,2
union all select 6,3
union all select 7,2
union all select 8,3select * from #MM M where exists(select * from #MM where mobileid=M.mobileid and funid<>M.funid)mobileid funid
----------- -----------
6 2
6 3(2 行受影响)
(
mobileid int,
funid int
)
insert into #MM select 6,2
union all select 6,3
union all select 7,2
union all select 8,3select * from #MM where mobileid in(select mobileid from #MM M where exists(select * from #MM where mobileid=M.mobileid and funid<>M.funid) and funid=2 or funid=3 group by mobileid having count(*)>1
)
mobileid funid
----------- -----------
6 2
6 3(2 行受影响)
select a.* from
(select * from mobilefun where funid=2) a,
(select * from mobilefun where funid=3) b
where a.mobileid=b.mobileid
@num INT --参数个数AS ...
@sql='SELECT mobileid FROM mobilefun WHERE ' + @sql + ' GROUP BY mobileid HAVING COUNT(*)>=' + @num
...GO