求救:SQL面试题
表g_applydetail(g_applyno, g_name, g_cardid)
g_applyno g_name g_cardid
001 张三 350124
002 李四 370103
003 王五 430105
004 张三 350124
找出同一个g_cardid出现记录个数大等于2的g_cardid及出现的次数?
[解决办法]
create table g_applydetail(g_applyno char(3), g_name nvarchar(10), g_cardid varchar(10))
insert g_applydetail select '001 ', '张三 ', '350124 '
union all select '002 ', '李四 ', '370103 '
union all select '003 ', '王五 ', '430105 '
union all select '004 ', '张三 ', '350124 '
select g_cardid, num=count(g_cardid)
from g_applydetail
group by g_cardid
having count(g_cardid)> 1
---result
g_cardid num
---------- -----------
350124 2
(1 row(s) affected)
[解决办法]
select g_cardid, count(g_cardid) as count
from g_applydetail
group by g_cardid
having count(g_cardid)> =2
