34,576
社区成员
发帖
与我相关
我的任务
分享有一个sql,思考之后不知道怎么写.来请教一下各位.
我目前能想到的是
SELECT *
FROM t
WHERE a IN (
SELECT a FROM t GROUP BY a HAVING COUNT(b) = 2
)
AND b IN ( 21, 22 );
-- 但是这样也能把2查出来. 我只想把 1查出来.实在想不到要怎么写这个sql了. 要是配合代码一个个 b 查知道怎么查,但是不确定b的个数.
...全文
请发表友善的回复…
发表回复
吉普赛的歌 2018-11-01
- 打赏
- 举报
USE tempdb
GO
IF OBJECT_ID('t') IS NOT NULL DROP TABLE t
GO
CREATE TABLE t(a INT,b INT)
GO
INSERT INTO t VALUES(1,21)
INSERT INTO t VALUES(1,22)
INSERT INTO t VALUES(2,21)
INSERT INTO t VALUES(2,31)
INSERT INTO t VALUES(3,21)
INSERT INTO t VALUES(3,22)
INSERT INTO t VALUES(3,23)
INSERT INTO t VALUES(3,24)
INSERT INTO t VALUES(3,25)
;WITH cte AS (
SELECT 21 AS c
UNION
SELECT 22
),cte2 AS (
SELECT a,(SELECT ','+LTRIM(b) FROM t AS tt WHERE tt.a=t.a FOR XML PATH(''))+',' AS s,COUNT(1) AS cnt FROM t
GROUP BY a
)
SELECT * FROM cte2 WHERE EXISTS(
SELECT * FROM cte WHERE cte2.s LIKE '%,'+ltrim(cte.c)+',%'
)
AND NOT EXISTS (
SELECT * FROM cte WHERE cte2.s NOT LIKE '%,'+ltrim(cte.c)+',%'
)
AND cte2.cnt=(SELECT COUNT(1) FROM cte)
二月十六 版主 2018-11-01
- 打赏
- 举报
对对,是这种情况,我还准备改写一下,,谢谢,谢谢,真的太谢谢了.[/quote]
记得结贴啊
k. 2018-11-01
- 打赏
- 举报
对对,是这种情况,我还准备改写一下,,谢谢,谢谢,真的太谢谢了.
k. 2018-11-01
- 打赏
- 举报
谢谢,谢谢,明白了.
二月十六 版主 2018-11-01
- 打赏
- 举报
或者这样已知的数
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([a] int,[b] int)
Insert #T
select 1,21 union all
select 1,22 union all
select 2,21 union all
select 2,31 union all
select 3,21 union all
select 3,22 union all
select 3,23 union all
select 3,24 union all
select 3,25
Go
--测试数据结束
SELECT
a
FROM
(
SELECT
a,
COUNT(DISTINCT b) AS bcount
FROM
#T
WHERE
b IN (
21, 22, 23, 24, 25 --如果数是已知的
)
GROUP BY
a
) t
WHERE
t.bcount = 5 --已知的5个数
AND t.bcount =
(
SELECT
COUNT(DISTINCT b)
FROM
#T
WHERE
a = t.a
);二月十六 版主 2018-11-01
- 打赏
- 举报
上边的语句21，22是个字符串穿进去的，不知道你实际是什么样的数据
二月十六 版主 2018-11-01
- 打赏
- 举报
函数：
ALTER FUNCTION dbo.F_Split
(
@SplitString nvarchar(max), --源字符串
@Separator nvarchar(10)=' ' --分隔符号，默认为空格
)
RETURNS @SplitStringsTable TABLE --输出的数据表
(
[id] int identity(1,1),
[value] nvarchar(max)
)
AS
BEGIN
DECLARE @CurrentIndex int;
DECLARE @NextIndex int;
DECLARE @ReturnText nvarchar(max);
SELECT @CurrentIndex=1;
WHILE(@CurrentIndex<=len(@SplitString))
BEGIN
SELECT @NextIndex=charindex(@Separator,@SplitString,@CurrentIndex);
IF(@NextIndex=0 OR @NextIndex IS NULL)
SELECT @NextIndex=len(@SplitString)+1;
SELECT @ReturnText=substring(@SplitString,@CurrentIndex,@NextIndex-@CurrentIndex);
INSERT INTO @SplitStringsTable([value]) VALUES(@ReturnText);
SELECT @CurrentIndex=@NextIndex+1;
END
RETURN;
END
GO
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([a] int,[b] int)
Insert #T
select 1,21 union all
select 1,22 union all
select 2,21 union all
select 2,31 union all
select 3,21 union all
select 3,22 union all
select 3,23 union all
select 3,24 union all
select 3,25
Go
--测试数据结束
DECLARE @str NVARCHAR(200)='21,22,23,24,25'
SELECT a FROM (
SELECT a,COUNT(DISTINCT t.value) AS bcount from #T JOIN (SELECT * FROM dbo.F_Split(@str,','))t ON b=t.value GROUP BY a)t
WHERE t.bcount=(SELECT COUNT(1) FROM dbo.F_Split(@str,',')) AND t.bcount=(SELECT COUNT(DISTINCT b) FROM #T WHERE a=t.a)
k. 2018-11-01
- 打赏
- 举报
没有 a组的值 b不重复
21,21 只能查到 1
21,22,23查不到任何值
21,22,23,24,25查到的是3
二月十六 版主 2018-11-01
- 打赏
- 举报
21，22，23得不到b是因为b除了这三个数之外，还有别的值吗？如果1有两个21，一个22，那么通过21，22能查到1吗？
