背景; 银行一般都会出现一种情况, 一个用户开多个账户现在想用SQL查询出 一个银行系统里,一个用户有两个以上(含两个的)账户一共有多少个?比如
身份证id 开卡 时间
ID CARDTIME
101 20090101
102 20080101
102 20090201
103 20070101
103 20080808
103 20090202现在要 SELECT 出来的结果是 2 也就是 (102 、103)现在有一个参照SELECT ID COUNT(*)
FROM TABLE1
WHERE ID IN (
SELECT ID FROM TABLE1
GROUP BY ID
HAVING COUNT(ID)> 1
)结果:
101 1
102 2
103 3但是我想得到的结果 只是 “2”就是意思:一个用户有两个以上(含两个的)账户一共有2个
身份证id 开卡 时间
ID CARDTIME
101 20090101
102 20080101
102 20090201
103 20070101
103 20080808
103 20090202现在要 SELECT 出来的结果是 2 也就是 (102 、103)现在有一个参照SELECT ID COUNT(*)
FROM TABLE1
WHERE ID IN (
SELECT ID FROM TABLE1
GROUP BY ID
HAVING COUNT(ID)> 1
)结果:
101 1
102 2
103 3但是我想得到的结果 只是 “2”就是意思:一个用户有两个以上(含两个的)账户一共有2个
FROM TABLE1
WHERE ID IN (
SELECT ID FROM TABLE1
GROUP BY ID
HAVING COUNT(ID)> 1
)
WHERE ID IN (
SELECT ID FROM TABLE1
GROUP BY ID
HAVING COUNT(ID)> 1
) )
from t
group by id
having count(*) > 1
公司的DB2 我又不能随便 加个table,
所以在自己电脑上随便安装个数据库软件 试试 SQL语句的
希望高人现身啊
from (
SELECT ID FROM TABLE1
GROUP BY ID
HAVING COUNT(ID)> 1 )
FROM
(SELECT ID COUNT(*)
FROM TABLE1
WHERE ID IN
(SELECT ID
FROM TABLE1
GROUP BY ID
HAVING COUNT(ID)> 1))
---------- ----------
101 20090101
102 20080101
102 20090201
103 20070101
103 20080808
103 20090202已选择6行。SQL> select count(distinct id)
2 from
3 (select id from t_01 group by id having count(id)>1);COUNT(DISTINCTID)
-----------------
2
不过下面的语句在MYSQL里应该能运行吧
其实只要把你的SQL改一下就行了
SELECT COUNT(*)
FROM (
SELECT ID FROM TABLE1
GROUP BY ID
HAVING COUNT(ID)> 1
)
FROM (SELECT ID, COUNT(*) AS TOTAL FROM TABLE1 GROUP BY ID) A
WHERE A.TOTAL > 2
SELECT t.ID FROM TABLENAME t GROUP BY t.ID HAVING COUNT(*) > 1
)
( id number,
cardtime number);
insert into card values(101,20090101);
insert into card values(102,20080101);
insert into card values(102,20090301);
insert into card values(103,20090111);
insert into card values(103,20090121);
insert into card values(103,20090401);select id, count(id)
from card
where id in (select id from card group by id having count(id) > 1)
group by id;
where id in (select id from card group by id having count(1)>1);orselect * from card a
where exists(select 1 from card b where a.id=b.id group by b.id having count(1)>=2)
from (select id
from table_a
group by id
having count(id)>1) as a
with ur;