CREATE TABLE test (id nvarchar(50),subId nvarchar(50))
INSERT INTO test SELECT 'BDAE74F7','01'
INSERT INTO test SELECT 'BDAE74F7','01'INSERT INTO test SELECT 'BDAE74F8','01'
INSERT INTO test SELECT 'BDAE74F8','01'INSERT INTO test SELECT 'BDAE74F8','02'
INSERT INTO test SELECT 'BDAE74F8','02'想得到如下的结果,SQL语句应该怎么写?
id groupSubIdCount
BDAE74F7 1
BDAE74F8 2
INSERT INTO test SELECT 'BDAE74F7','01'
INSERT INTO test SELECT 'BDAE74F7','01'INSERT INTO test SELECT 'BDAE74F8','01'
INSERT INTO test SELECT 'BDAE74F8','01'INSERT INTO test SELECT 'BDAE74F8','02'
INSERT INTO test SELECT 'BDAE74F8','02'想得到如下的结果,SQL语句应该怎么写?
id groupSubIdCount
BDAE74F7 1
BDAE74F8 2
select id,COUNT(distinct subId) as groupSubIdCount
from test
group by id
CREATE TABLE test (id nvarchar(50),subId nvarchar(50))
INSERT INTO test SELECT 'BDAE74F7','01'
INSERT INTO test SELECT 'BDAE74F7','01'INSERT INTO test SELECT 'BDAE74F8','01'
INSERT INTO test SELECT 'BDAE74F8','01'INSERT INTO test SELECT 'BDAE74F8','02'
INSERT INTO test SELECT 'BDAE74F8','02'
/*
想得到如下的结果,SQL语句应该怎么写?
id groupSubIdCount
BDAE74F7 1
BDAE74F8 2
*/select id,COUNT(distinct subId) as groupSubIdCount from test group by iddrop table testid groupSubIdCount
-------------------------------------------------- ---------------
BDAE74F7 1
BDAE74F8 2(2 行受影响)