请问一条SQL语句,怎么分组筛选
请教一条SQL语句,如何分组筛选?
现有一张表如下:
ID Num
1 1
1 0
2 0
2 0
3 0
3 0
现在问题是,上面ID分别2条为一组,我想筛选出当同一组ID中,Num的值 同时都等于0的那个ID,
即上面结果最张需要输出来
ID Num
2 0
2 0
3 0
3 0
请问SQL语句应该如何写呢?所有数据是动态产生,所以不能写死条件的,谢啦!
------最佳解决方案--------------------
------其他解决方案--------------------
------其他解决方案--------------------
select * from tb where id in(select id from tb where num=0 group by id having count(1)>1)
------其他解决方案--------------------
select * from tb group by id,num having count(1)>1
------其他解决方案--------------------
select * from tb where num=0 group by id,num having count(1)>1
------其他解决方案--------------------
------其他解决方案--------------------
太阳哦,一下子那么多人回复了
------其他解决方案--------------------
现有一张表如下:
ID Num
1 1
1 0
2 0
2 0
3 0
3 0
现在问题是,上面ID分别2条为一组,我想筛选出当同一组ID中,Num的值 同时都等于0的那个ID,
即上面结果最张需要输出来
ID Num
2 0
2 0
3 0
3 0
请问SQL语句应该如何写呢?所有数据是动态产生,所以不能写死条件的,谢啦!
------最佳解决方案--------------------
--> 测试数据:#tb
IF OBJECT_ID('TEMPDB.DBO.#tb') IS NOT NULL DROP TABLE #tb
GO
CREATE TABLE #tb([ID] INT,[Num] INT)
INSERT #tb
SELECT 1,1 UNION ALL
SELECT 1,0 UNION ALL
SELECT 2,0 UNION ALL
SELECT 2,0 UNION ALL
SELECT 3,0 UNION ALL
SELECT 3,0
--------------开始查询--------------------------
SELECT * FROM #tb AS t WHERE NOT EXISTS(SELECT 1 FROM #tb WHERE [ID]=t.[ID] AND [Num]=1)
----------------结果----------------------------
/*
*
ID Num
2 0
2 0
3 0
3 0
*
*/
------其他解决方案--------------------
select *
from TB
where ID in (
select ID
from TB
where num=0
group by ID
having COUNT(1)>1)
------其他解决方案--------------------
select * from tb where id in(select id from tb where num=0 group by id having count(1)>1)
------其他解决方案--------------------
select * from tb group by id,num having count(1)>1
------其他解决方案--------------------
select * from tb where num=0 group by id,num having count(1)>1
------其他解决方案--------------------
--CREATE TABLE #tb (ID INT, Num INT )
--INSERT INTO #tb
--SELECT 1 ,1
--UNION ALL
--SELECT 1, 0
--UNION ALL
--SELECT 2, 0
--UNION ALL
--SELECT 2, 0
--UNION ALL
--SELECT 3, 0
--UNION ALL
--SELECT 3, 0
SELECT * FROM #tb
WHERE id IN (
SELECT id
FROM #tb
GROUP BY id
HAVING SUM(num)=0)
/*
ID Num
----------- -----------
2 0
2 0
3 0
3 0
*/
------其他解决方案--------------------
太阳哦,一下子那么多人回复了
------其他解决方案--------------------