SQL检查列中的所有值
我在Oracle DB中具有下表。SQL检查列中的所有值
ID VALUE
-----------
1 1
1 2
1 3
2 1
2 2
3 1
3 2
3 3
4 1
如何选择的ID里面有所有3个值(1,2,3)
试试这个:
SELECT ID
FROM TABLENAME T
WHERE EXISTS (SELECT *
FROM TABLENAME T1
WHERE T1.ID = T.ID AND T1.VALUE = '1')
AND EXISTS (SELECT *
FROM TABLENAME T2
WHERE T1.ID = T.ID AND T2.VALUE = '2')
AND EXISTS (SELECT *
FROM TABLENAME T3
WHERE T1.ID = T.ID AND T2.VALUE = '3')
或
SELECT ID
FROM TABLENAME T
WHERE (SELECT COUNT(*)
FROM (SELECT VALUE
FROM TABLENAME T1
WHERE T1.ID = T.ID
GROUP BY VALUE)) = 3;
,其中3号的值可以通过
SELECT COUNT(*)
FROM TABLENAME T1
GROUP BY VALUE
所以这将是通用:
SELECT ID
FROM TABLENAME T
WHERE (SELECT COUNT(*)
FROM (SELECT VALUE
FROM TABLENAME T1
WHERE T1.ID = T.ID
GROUP BY VALUE)) = (SELECT COUNT(*)
FROM TABLENAME T2
GROUP BY VALUE)
有效,但过于复杂 - 答案可以在单个“SELECT”语句中完成。此外,您的别名需要更正 – 2012-07-27 03:51:00
仍然过于复杂 - 您不需要子查询。提示:'HAVING' – 2012-07-27 03:53:48
我试过这个..但是我得到所有行,即使它没有所有的值1,2和3 – user1556549 2012-07-27 03:55:46
下面是一个选项... HAVING子句中每个表达式计数被发现等于1,2或3,如果任何值的数量这些计数小于1,那么ID将不会被返回。
http://sqlfiddle.com/#!4/00fdc/8
SELECT ID
FROM myTable
GROUP BY ID
HAVING
SUM(DECODE(VALUE, 1, 1, 0)) > 0 AND
SUM(DECODE(VALUE, 2, 1, 0)) > 0 AND
SUM(DECODE(VALUE, 3, 1, 0)) > 0
EDIT - 规定值1,2或3个:
SELECT ID
FROM myTable
GROUP BY ID
HAVING
SUM(DECODE(VALUE, 1, 1, 0)) > 0 AND
(
SUM(DECODE(VALUE, 2, 1, 0)) > 0 OR
SUM(DECODE(VALUE, 3, 1, 0)) > 0
)
方式过于复杂 – 2012-07-27 03:56:16
那么为什么不向我们展示它是如何完成的? – 2012-07-27 03:57:54
查看我的提示hmmftg – 2012-07-27 03:58:26
选择ID作为'v1',
总和(当值= 2然后1其他0结束的情况)作为'v2',
总和(当值= 3然后1其他0结束的情况)作为'v3'
由ID ORAC组)作为最终
其中,V1> 0且V2> 0和v3> 0
有了这个选项,你会得到更多的比的ID,到你的应用程序选择您想要的列:
SELECT ID,
sum(CASE WHEN VALUE = 1 THEN 1 ELSE 0 END) AS ONE,
sum(CASE WHEN VALUE = 2 THEN 1 ELSE 0 END) AS TWO,
sum(CASE WHEN VALUE = 3 THEN 1 ELSE 0 END) AS THREE
FROM MYTABLE
GROUP BY ID
HAVING ONE >= 1 AND TWO >= 1 AND THREE >= 1;
或者,如果你的情况是特定的(只值1,2,3是可能的,而且没有重复值是允许的),那么你可以尝试以下操作之一:
SELECT ID,
count(VALUE) AS VALUECOUNT
FROM MYTABLE
GROUP BY ID
HAVING VALUECOUNT = 3;
我会以ca重新开始之前,如果您稍后想要添加其他值,则可能会产生副作用。但是,如果您目前的案例符合上述限制条件,仍然值得提出建议。
,当然,如果你不喜欢这些取中间数的想法,我围内的另一选择
SELECT ID FROM (
...
)
最简单的方式给了查询一般是这样的
SQL> ed
Wrote file afiedt.buf
1 with x as (
2 select 1 id, 1 val from dual union all
3 select 1 id, 2 val from dual union all
4 select 1 id, 3 val from dual union all
5 select 2 id, 1 val from dual union all
6 select 2 id, 2 val from dual union all
7 select 3 id, 1 val from dual union all
8 select 3 id, 2 val from dual union all
9 select 3 id, 3 val from dual union all
10 select 4 id, 1 val from dual
11 )
12 select id
13 from x
14 where val in (1,2,3)
15 group by id
16* having count(distinct val) = 3
SQL>/
ID
----------
1
3
WHERE
子句标识您感兴趣的值。HAVING
子句告诉您需要存在多少这些值。例如,如果您想要所有具有3个值中的至少2个的行,则会更改HAVING
子句以查找COUNT
为2。
如果一个特定的val
保证每id
最多发生一次,则可以消除HAVING
子句中的distinct
。
非常好的建议 – 2012-07-27 05:08:14
值字段是否包含1,2,3以外的任何内容? – hmmftg 2012-07-27 04:16:52
标题是** SQL检查列**或** SQL中的所有值以检查列**中的特定值? – hmmftg 2012-07-27 04:18:27