我正在使用下面的SQL查询来计算记录。 我想在获得计数后对重复的testID进行折扣。
例:
select COUNT(*) from PRODUCT where CLASS_TYPE = 'test'
以上查询给出 5 条记录
testID
1234-00-01
1234-00-01
1234-00-02
1234-00-02
1111222233
我想得到计数 = 5 - 重复 2 = 3
是否有SQL查询可用于找出重复记录,然后计算最终计数?
请使用以下查询,
select COUNT(distinct testID) from PRODUCT where CLASS_TYPE = 'test'
你想要count(distinct)
:
select count(distinct testID)
from product p
where p.class_type = 'test'
如果你想得到总数和重复,你可以试试这个SQL:
SELECT count(DISTINCT testID) as distinct_elements, count(*) as total_elements,
(count(*) - count(DISTINCT testID)) as duplicate_elements
FROM PRODUCT
WHERE CLASS_TYPE = 'test';
您可以使用distinct
:
select count(distinct col) as Unique_CNT, COUNT(*) as all_CNT
from PRODUCT
where CLASS_TYPE = 'test';
CREATE TABLE #Test
(
testID VARCHAR(100)
)
INSERT INTO #Test
(testID)
VALUES
('1234-00-01'),
('1234-00-01'),
('1234-00-02'),
('1234-00-02'),
('1111222233')
select COUNT(distinct testID) from #Test